analytical_ledger

NEW

In short

General ledger with analytical tags. This table includes all the bookkeeping entries of the company, with the analytical tags associated with each entry.

This table is suited for incremental ingestion with an ETL tool. For standard data analysis, use schema pennylane.

The general ledger includes all the bookkeeping entries of the company, at the most granular level.

🔗 See general_ledger for more details.

Pennylane users, both accountants and their clients, can add analytical tags to their bookkeeping entries and/or invoices and bank transactions. When a bookkeeping entry or a document is tagged, the tags are inherited by the corresponding ledger event, and become available in the analytical ledger.

Multiple tags can be associated with a single ledger event, with varying weights. In such situations, the same ledger event will appear several times in the analytical ledger, once for each tag associated with it.

By design, this table is one of the heaviest in the Pennylane data model, so make sure your queries are optimized before using it in production.

Available Columns

company_id

bigint

Unique ID of the company on Pennylane, referring to companies.id.

company_name

character varying

Company's display name on Pennylane.

id

bigint

Unique ID of the ledger event on Pennylane, referring to general_ledger.id.

date

date

Date of the ledger event, in YYYY-MM-DD format.

lettering

bigint

In case the ledger event is part of a lettered group of events, lettering is a unique ID for the group.
Examples: 350 312 715, 344 963 571

label

character varying

Label of the bookkeeping entry if any, defaulting to the label of the document attached to the entry, for example an invoice or a bank transaction.
Examples: "Facture XYZ - F-2023-06-345", "URSSAF d'Ile de France (123456789)"

debit

numeric

Bookkeeping entry amount in case of debits.

credit

numeric

Bookkeeping entry amount in case of credits.

plan_item_number

character varying

A plan_item describes an entry of the company's accounting plan.
The plan_item_number is the standard account number identifying the plan item. Note: multiple accounts with different VAT rates can hold the same number.
Examples: "411008", "445710", "512001"

plan_item_label

character varying

Label of the account.
Examples: "Capital", "TVA déductible sur autres biens et services", "Achats de marchandises"

journal_code

character varying

Short code identifying the journal the bookkeeping entry belongs to.
Examples: "VT", "HA", "OD", "BQ"

journal_label

character varying

Label of the journal the bookkeeping entry belongs to.
Examples: "Journal des ventes", "Journal des achats", "Journal de trésorerie"

document_id

bigint

ID of the document (invoice, bank transaction, addendum, etc) associated with the bookkeeping entry.

document_label

character varying

Label of the document (invoice, bank transaction, addendum, etc) associated with the bookkeeping entry.
Examples: "Facture XYZ - F-2023-06-345", "Virement émis ABCD"

invoice_number

character varying

Invoice number, in case the document attached to the bookkeeping entry is an invoice.
Examples: "F-2023-06-345", "F-2023-06-346"

invoice_link

character varying

Link to the invoice on the Pennylane app, in case the document attached to the bookkeeping entry is an invoice.

fec_pieceref

character varying

NEW

Unique identifier of each bookkeeping entry. It is used to track and reference each transaction in Pennylane, facilitating the traceability and audit of accounting operations. Unlike the ID field, the FEC_PIECEREF is part of FEC (Fichiers des Écritures Comptables).
Examples: "KIHQXJIQDS", "LQLSBKFFLT"

document_created_at

timestamp without time zone

Creation date of the document associated with the bookkeeping entry.

document_updated_at

timestamp without time zone

Last update date of the document associated with the bookkeeping entry.

thirdparty_id

bigint

A thirdparty represents a supplier or a customer of the company managed on Pennylane. Third parties can be both legal entities or individuals. The thirdparty_id is the unique identifier of the third party on Pennylane, and refers to suppliers.id or customers.id depending on the type of third party.

If the bookkeeping entry is associated with a third party, thirdparty_id is its ID.
Otherwise, thirdparty_id is inherited from the document associated with the bookkeeping entry in case of 4xx (invoices) and 5xx (bank transactions) accounts.

thirdparty_plan_item_number

character varying

Third party's 401/411 account number in the accounting plan.
Examples: "401100024", "411NORAUTO"

thirdparty_plan_item_label

character varying

Third party's account label in the accounting plan.
Examples: "John Doe", "Norauto"

tag_group

character varying

Analytical tags are organized in groups. This field is the name of the group the tag belongs to.
Examples: "Types de dépenses / revenus", "Équipe", "Projet"

tag_label

character varying

Label of the analytical tag.
Examples: "Déplacements", "Juridique", "Projet X"

analytical_code

character varying

Analytical Code of the analytical tag.
Examples: "AB001", "SALESPARIS"

tag_weight

numeric

Each analytical tag comes with a weight between 0 and 1.
When an entry is associated with multiple tags inside the same tag group, the sum of the weights of all tags is 1.

deleted_at

timestamp without time zone

Date and time when the ledger event was deleted.
If the ledger event is not deleted, this field is null.

synchronised_at

timestamp without time zone

Date and time when the ledger event was last synchronised with Pennylane's application.