Search

Data structure

Database Export Structure and Overview

When users initiate an export by clicking the [Export Now] button or through scheduled exports, the system generates or updates various tables and files based on the configured export filters and YAML settings. This document outlines the tables used in exports, detailing how each table contributes to data management for CapEx requests, cash flow, and capital budget data.

CapEx Request Data Export

The CapEx Request Data Export section covers tables that store information about capital expenditure (CapEx) requests, including essential request metadata, properties, approval steps, and lifecycle states. Tables in this section only export data if the request meets the current request state filter and properties that are defined in the YAML settings. Key tables include capex_management_requests, property_info, and cm_request_states, each of which provides a specific aspect of the request data, from basic identifiers to properties and approval steps.

Table: `capex_management_requests`

The capex_management_requests table stores details about each CapEx request, including both static and dynamic properties. Static system properties use descriptive names (e.g., totalRequestedAmount), while dynamic properties are identified by a property prefix and ID (e.g., property456). Mapping of dynamic property IDs to names is available in the property_info or property_names_transposedtable. Codified values are shown as codes; their code-to-value mappings can be found in property_info_codes.

Column	Description	Example Data
`requestId`	Unique identifier for each CapEx request in Capex Management	12345
`requestOriginId`	It also acts as a unique identifier for each CapEx request Capex Management	54321
`name`	Name or title of the CapEx request.	“New Equipment”
`properties`	Holds various properties based on request type: CAPEX or GLOBAL. The number of columns depends on the configured properties for export defined in the YAML configuration file.	“totalRequestedAmount” or “property456”

Table: `property_info`

This table provides metadata for each property, including its type and display name.

Column	Description	Example Data
`propertyId`	Unique identifier for each property.	456
`name`	Internal name used in `capex_management_requests`. For system properties it is the system name of the property e.g., `totalRequestedAmount` while dynamically created propertiesare identified by a property prefix and ID (e.g., `property456`)	“totalRequestedAmount” or “property456”
`caption`	Display name for the property in Weissr.	“Requested Amount”, “Department”
`type`	Data type of the property (e.g., Boolean, Date, Money).	“Money”, ”Code”

Table: `property_names_transposed`

The property_names_transposed table contains a separate column for each propertyId found in property_info, with the column name as the propertyId (e.g., property456) and the cell data as the caption (display name) for that property. This structure allows each property’s display name to be easily accessed across multiple properties within a single row.

Column	Description	Example Data
`property456`	Display name for property with ID 456	“Project Budget”
`property789`	Display name for property with ID 789	“Asset Value”
`property123`	Display name for property with ID 123	“Depreciation Rate”

Explanation: In property_names_transposed, each propertyId from property_info becomes a separate column with the caption as the cell data. This layout provides an easily accessible reference for each property’s display name across multiple properties.

Table: `cm_routes`

Contains data on approval routes configured within the system.

Column	Description	Example Data
`id`	Unique identifier for each route.	102
`name`	Display name for the route in Weissr.	“Smaller Investments < 20 kEUR”

Table: `cm_approval_steps`

Contains data on individual approval steps within each route.

Column	Description	Example Data
`id`	Unique identifier for each approval step.	203
`name`	Display name for the approval step in Weissr.	“Step 1 Approval”

Table: `cm_request_states`

The cm_request_states table contains two columns, id and name, for the predefined states that CapEx requests can be in, representing each phase of the request lifecycle. Only the following states are available in this table:

ID `id`	Name `name`	Description
0	Undefined	State is not yet specified or assigned.
1	Draft	Initial state where the request is created.
2	Under Review	Request is under review and awaiting decision.
3	Decision	Request is in the decision-making phase.
4	Monitoring	Request is approved and being actively monitored.
5	Follow Up	Request is in post-completion review.
6	Completed	Request has been fully completed.
7	Rejected	Request has been declined.

Note: Only these states are visible in cm_request_states, ensuring consistency in the request lifecycle tracking.

Table: `decision_information`

The decision_information table logs key decision data for each CapEx request, such as assigned decision-makers, planned dates, and actual decision dates.

Column	Description	Example Data
`requestOriginId`	Foreign key linking to the `requestOriginId` in the `capex_management_requests` table.	54321
`routeId`	Foreign key linking to the `cm_routes` table.	102
`approvalStepId`	Foreign key linking to `cm_approval_steps`.	203
`statusName`	Status for the step the decision-making process.	“In Progress”
`assignedDecisionMakerId`	ID of the assigned decision-maker, linking to the user’s `id` in the `users` table.	789
`actualDecisionMakerId`	ID of the actual decision-maker, linking to the user’s `id` in the `users` table.	456
`plannedDate`	The planned date for the decision-making step.	“2023-12-01”
`decisionDate`	Actual date the decision was made.	“2023-12-15”

Table: `cm_models`

Stores investment model information relevant to CapEx requests.

Column	Description	Example Data
`id`	Unique identifier for each investment model.	401
`name`	Display name for the investment model.	“Financial Model”

Table: `cm_nodes`

Stores node information, representing organizational units the CapEx request are located in.

Column	Description	Example Data
`id`	Unique identifier for each node.	501
`name`	Display name for the node.	“Manufacturing”

Table: `currency`

Stores currency data for financial calculations.

Column	Description	Example Data
`id`	Unique identifier for each currency.	1
`name`	Currency code, matching the `currencyCode` in the `exchange_rates` table.	“USD”

Table: `exchange_rates`

This table contains monthly exchange rate data for various currencies.

Column	Description	Example Data
`currencyCode`	The currency code (e.g., "USD"), linking to the `name` column in the `currency` table.	“USD”
`year`	Year for the exchange rate.	2024
`monthIndex`	Index of the month in the year (1 for January, 12 for December).	1
`value`	Exchange rate value for the given month related to the group currency.	1.13

Table: `users`

The users table stores user information within the system, including details from external identity management (IdM) sources.

Column	Description	Example Data
`id`	Unique identifier for each user.	789
`name`	Full name of the user.	“John Doe”
`username`	System username for the user.	“jdoe@example.com”
`email`	Email address of the user.	“jdoe@example.com”
`externalIdmUsername`	Username from an external IdM system. If not used it will be NULL.	“jdoe_ext”
`externalIdmLocation`	Location in the external IdM system. If not used it will be NULL.	“HQ”
`externalIdmCompany`	Company affiliation in the external IdM system. If not used it will be NULL.	“Weissr Inc.”

Table: `cm_capex`

The cm_capex table contains expenditure data for capital projects, storing planned, actual, and forecasted expenditure amounts based on the approval state of each request. Data is exported to this table only if selected in the export filters.

Column	Description	Example Data
`requestId`	Foreign key linking to `requestId` in the `capex_management_request` table, establishing a relationship to the relevant project.	12345
`siteNodeId`	Foreign key linking to `nodeId` in the `cm_node` table. Identifier for the node (production unit) associated with the asset or expenditure.	678
`assetId`	Unique identifier for the asset related to the expenditure.	4567
`assetOriginId`	Origin reference for the asset, linking it to original asset records.	8901
`subIndustry`	Industry subcategory for the asset. Removed in version 5.1.	Manufacturing
`assetCategory`	Asset category type, allowing grouping and filtering by asset categorization.	Equipment
`assetName`	Name of the expenditure as entered in the project’s expenditure tab.	Conveyor Belt
`state`	Status of the expenditure data, which can be `PRE-DECISION` (planned) or `POST-DECISION` (actual/forecasted), depending on the request approval state.	POST-DECISION
`assetStartYear`	Year the asset begins its operational use or depreciation schedule.	2024
`taxDepreciation`	Depreciation period or useful life of the asset, represented as an integer indicating the number of years over which the asset is depreciated.	10
`fiscalYear`	Fiscal year for the data entry; differs from the calendar year if custom fiscal years are configured.	FY2024
`calendarYear`	Standard calendar year for the expenditure entry.	2024
`month`	Month of the expenditure within the fiscal or calendar year.	April
`monthNumber`	Month number within the calendar year (1 for January, 12 for December).	4
`planned`	Planned expenditure amount (only filled if `state` = `PRE-DECISION`).	10000
`actual`	Represents the actual expenditure amount for a project. (`state`= `POST-DECISION`). Once a project reaches the `POST-DECISION` state, any expenditures from the beginning of the project up to and including the current month (e.g., up to November 11, 2024, if that is the forecast start date) will be recorded as actuals.	6500
`forecast`	Represents the forecasted expenditure amount for a project (`state`= `POST-DECISION`). This value includes all anticipated outlays for future months based on the project’s forecast start date. If no forecast start date is set for a project, all projected outlays will be included as forecasts.	3500

Note: Only one of planned, actual, or forecast is populated per row, depending on the expenditure phase and is always exported in the local currency for the project

Table: `cm_capex_commitments`

New from version 5.1. The cm_capex_commitments table captures commitment data associated with each expenditure line. It exports alongside cm_capex when enabled in the export settings. Only expenditure lines with recorded commitments are included in the export; lines without commitments or those in the pre-decision phase are excluded. Commitments data is only exported if the customer utilizes the commitment import feature, which must be enabled by Weissr Support in the YAML file.

Column	Description	Example Data
`expenditureId`	Foreign key linking to `assetId` in the `cm_capex` table, establishing a relationship to the relevant asset expenditure.	4567
`requestOriginId`	Foreign key linking to `requestOriginId` in the `capex_management_request` table, allowing tracking of each commitment’s source request.	12345
`currencyCode`	Currency code for the commitment amount, ensuring accurate conversion if needed.	USD
`commitmentsSum`	Total sum of commitments for the specified expenditure line.	12000

Relationship Notes:
expenditureId in cm_capex_commitments corresponds to assetId in cm_capex, linking commitments to specific assets.
requestOriginId links to requestOriginId in the capex_management_request table, allowing tracking of each commitment’s source request.

Cash flow data export

The Cash Flow Data Export section focuses on capturing projected and actual cash flows for CapEx requests. Based on the request state filter and selected rows data from CapEx requests is filtered and stored in the cm_cashflow_data and cm_cashflow_meta tables. These tables hold detailed cash flow entries, allowing users to track the financial impact of requests at various stages.

Table: `cm_cashflow_data`

Column	Description	Example Data
`requestId`	Foreign key linking to `requestId` in the `capex_management_request` table, establishing a relationship to the relevant project.	12345
`is_pre_decision`	Flag (1 or 0) indicating if the data is pre-decision (1) or post-decision (0).	1
`cash_flow_meta_id`	Foreign key linking to `id` in `cm_cashflow_meta`, identifying the cash flow metadata associated with the entry.	101
`year`	Year for the cash flow data entry.	2024
`value`	Cash flow value for the specified year.	500000

Table: `cm_cashflow_meta`

Column	Description	Example Data
`id`	Unique identifier for each cash flow metadata entry.	101
`row_name`	Name of the row associated with the cash flow entry.	“Operating Cash Flow”
`data_type_code`	Code representing the type of data in the cash flow row.	“CASH_OP”

Budget Data Export

The Budget Data Export section documents tables related to budget management, including budget creation, frames, requests, and property definitions. This data provides a comprehensive view of budgets by including approved budget periods, request data, and historical exchange rates. Budget data exports are based on budget filter selection.

Table: `budget_data`

Column	Description	Example Data
`id`	Unique identifier for each budget entry.	202
`date_created`	Date the budget was created.	“2023-01-15”
`date_modified`	Date the budget was last modified.	“2023-06-10”
`start_year`	Start year of the budget period.	2024
`end_year`	End year of the budget period.	2026
`status`	Current status of the budget (approved, preliminary, not set).	“approved”
`backup_source_id`	Identifier if the budget is a backup; filled only if exported as a backup.	301
`backup_name`	Name of the budget backup, filled only if exported as a backup.	“2024 Q1 Backup”

Table: `budget_frames`

This table provides the budgeted miscellaneous or contingency amount for each organizational unit in capital budgets.

Column	Description	Example Data
`node_id`	Identifier for the node associated with the budget.	501
`budget_id`	Foreign key linking to the `id` in the `budget_data` table.	202
`amount`	Budgeted miscellaneous or contingency amount for the specific node and budget.	120000
`currency_code`	Currency code for the budgeted miscellaneous or contingency amount.	“USD”
`focus`	Indicates the focus of the budget entry (spend or decision).	“spend”

Table: `capital_budgeting_requests`

This table stores details about budget requests, with both static and dynamic properties available.

Column	Description	Example Data
`requestId`	Unique identifier for each budget request.	23456
`requestOriginId`	Foreign key linking to the `requestOriginId` in the `capex_management_requests` table.	54321
`name`	Name or title of the budget request.	“New Equipment”
`budget_id`	Foreign key linking to the `id` in the `budget_data` table.	202
`properties`	Includes properties based on request type: Budget or Both (filterable in the YAML configuration file).	“includeToBudget”

Notes:
Static Properties: Displayed as property names, e.g., includeToBudget.
Dynamic Properties: Named as property[ID], e.g., property456. Mappings for dynamic properties can be found in budget_property_names_transposed.
Codified Properties: Displayed as codes; code-to-value mappings are in budget_property_info_codes.

Table: `budget_property_info`

The budget_property_info table contains metadata for properties in budget requests.

Column	Description	Example Data
`propertyId`	Unique identifier for each property.	456
`name`	Internal name used in `capital_budgeting_requests`.	“projectBudget”
`caption`	Display name in Weissr.	“Project Budget”
`type`	Data type of the property (e.g., Boolean, Date, Money).	“Money”

Table: `budget_property_names_transposed`

Each column in this table represents a propertyId from budget_property_info, with each cell containing the caption (display name) for that property.

Column	Description	Example Data
`property456`	Display name for the property with ID 456	“Project Budget”
`property789`	Display name for the property with ID 789	“Asset Value”

Table: `budget_property_info_codes`

The budget_property_info_codes table provides code-to-value mappings for codified properties.

Column	Description	Example Data
`propertyId`	Identifier for each property.	456
`code`	Codified value of the property.	“EQUIP”
`value`	Actual value that corresponds to the code.	“Equipment”

Table: `budget_outlay_data`

The budget_outlay_data table stores budgeted outlays for the expenditures in each budget request.

Column	Description	Example Data
`budget_request_id`	Foreign key linking to the `id` in the `capital_budgeting_requests` table.	23456
`expense_type`	Type of expense (e.g., CapEx, OpEx).	“CapEx”
`fiscal_year`	Fiscal year of the budgeted outlay.	FY2024
`calendar_year`	Calendar year of the budgeted outlay	2024
`month_index`	Month index in the calendar year (1 for January, 12 for December).	1
`amount`	Bbudgeted amount for the specified period.	5000
`currency_code`	Currency code for the amount.	“USD”

Table: `budget_historical_rates`

This table provides monthly historical exchange rates for budgeting purposes.

Column	Description	Example Data
`budget_id`	Foreign key linking to the `id` in the `budget_data` table.	202
`currency_code`	Code of the currency for the exchange rate.	“USD”
`year`	Year for the exchange rate.	2024
`month_index`	Month index in the year (1 for January, 12 for December).	1
`value`	Exchange rate value for the specified currency and month related to the group currency.	1.13

Data structure

Database Export Structure and Overview

CapEx Request Data Export

Table: capex_management_requests

Table: property_info

Table: property_names_transposed

Table: cm_routes

Table: cm_approval_steps

Table: cm_request_states

Table: decision_information

Table: cm_models

Table: cm_nodes

Table: currency

Table: exchange_rates

Table: users

Table: cm_capex

Table: cm_capex_commitments