Profit & Loss

Facts

Definitions

Column	Description
date	Date in YYYY-MM-DD format, reference to Dates.
gl_account_id	Which GL Account (links to GL Accounts).
value_type_id	Type of value measured (links to Value Types, e.g., actuals, budget).
amount	Fact registered in a currency unit.

Sample & Implementation

A list of PnL' GL Accounts
date	gl_account_id	value_type_id	amount
2025-01-01	1010	2	15000000
2025-02-01	1010	2	15194000
2025-03-01	1010	2	15448294.23
2025-04-01	1010	2	15702878.21
2025-05-01	1010	2	15957747.59
2025-06-01	1010	2	16212898.14
2025-07-01	1010	2	16468325.76
2025-08-01	1010	2	16718990.28
2025-09-01	1010	2	16969918.37
2025-10-01	1010	2	17221106.42

CREATE TABLE IF NOT EXISTS pnl (
    date DATE NOT NULL,
    gl_account_id INTEGER NOT NULL,
    value_type_id INTEGER NOT NULL,
    amount INTEGER NOT NULL,
    FOREIGN KEY (date) REFERENCES dates(date),
    FOREIGN KEY (gl_account_id) REFERENCES gl_accounts(id),
    FOREIGN KEY (value_type_id) REFERENCES value_types(id),
    PRIMARY KEY (date, gl_account_id, value_type_id)
)

Data Format

Field

Type

The primary key is formed by constrainst on dates, GL accounts, and value types:
PRIMARY KEY (date, gl_account_id, value_type_id)

Facts

Definitions

Sample & Implementation

Data Format

Dependencies

On this page