fct_contract_price_history


Description

The new price and price change information whenever the price of a contract is adjusted. The current_price of a contract can be built with condition valid_from_date >= CURRENT_DATE < valid_to_date

Columns

Column Type Size Nulls Auto Default Children Parents Comments
contract_price_history_id int8 19 null

The primary key of this table. System column calculated by the source System.
contract_id int8 19 null
fct_contract.contract_id fct_contract_price_history_contract_id_fkey R

The contract the price change is about.
customer_id int8 19 null
dim_customer.customer_id fct_contract_price_history_customer_id_fkey R

The customer the contract and price change belong to.
organization_unit_id int8 19 null
dim_organization_unit.organization_unit_id fct_contract_price_history_organization_unit_id_fkey R

The studio the customer and price change belong to.
price numeric 23,2 null

The absolute new price charged for the contract (in frequency of the related dim_contract_payment_frequency). This price combine values paid by customer and optional part covered by company.
valid_from_date date 13 null
dim_date.date fct_contract_price_history_valid_from_date_fkey R

The date from when the price becomes valid, inclusive.
valid_to_date date 13 null
dim_date.date fct_contract_price_history_valid_to_date_fkey R

The date until which the price is valid for, exclusive. Price changes with no expiration date will be set to valid_to_date=2199-01-01.
is_final bool 1 null

Whether the price is the final price of the day, the customer will be charged for. Only relevant if there are multiple price changes on the same day.
order_index int4 10 null

The order at which price changes are applied, starting from 0. If there are multiple price changes per day, the order of application can be determined by this column.
price_change numeric 23,2 √ null

The percentage or absolute change of the previous price compared to this one. Or NULL if the price was statically set to a new value.Depending on charge_adjustment_type.
charge_adjustment_type varchar 256 null

The way how a new price is calculated using the defined price change. One of:

  • BASIC_AMOUNT: The price is set to a new absolute value, price_change=NULL.
  • RELATIVE: The price is set to the previous price multiplied by the price_change as a percentage.
  • ABSOLUTE: The price is set to the previous price plus the price_change.
declaration_type varchar 256 null

Using what rules a price change was declared. One of:

  • INITIAL: Not a price change but the original price of a contract.
  • DYNAMIC_ADJUSTMENT: A price changing rule set in the System came into effect.
  • EXPIRED: The expiration of a dynamic adjustment restoring the previous price.
  • EXTENSION_TERM: The term of the contract was extended with a new price.
  • AGE_BASED_ADJUSTMENT: The customer reached an age falling into a new price group.
dynamic_adjustment_origin varchar 256 null

The origin of the dynamic adjustment that caused the price change. One of:

  • INITIAL: Not an adjustment but the original contract price.
  • IMPORTED: Price Adjustment was imported from another system.
  • UNKNOWN: Not a dynamic adjustment.
  • BULK_OPERATION: The price change was part of a bulk operation set by a user.
  • MANUAL: The price change was individually set by a user.
  • DISCOUNT_VOUCHER: The price change was caused by a discount voucher. Either to a customer or to a company. (Legacy for values before 2024-09-20).
  • B2B_DISCOUNT_VOUCHER: The price change was caused by a discount voucher given to a company.
  • B2C_DISCOUNT_VOUCHER: The price change was caused by a discount voucher given to a customer.
  • RATE: The price change was executed on rate-level.
  • CONTRACT_OFFER: The price change was part of a contract offer.
customer_price numeric 19,2 null

The part of the price that customer will be charged for ( in frequency of the related dim_contract_payment_frequency).There could be another part of the price, that would be paid by company that client is part of.
customer_price_change numeric 19,2 √ null

The percentage or absolute change of the previous customer_price compared to this one. Or NULL if the price was statically set to a new value)
company_price numeric 19,2 null

The part of the price that company connected to customer will be charged for ( in frequency of the related dim_contract_payment_frequency). There could be another part of the price, that would be paid by the customer
company_price_change numeric 19,2 √ null

The percentage or absolute change of the previous company_price compared to this one. Or NULL if the price was statically set to a new value)
last_updated timestamptz 35 √ null

System column. UTC Timestamp at which entry was calculated (lags behind source system).

Relationships

Close relationships within degrees of separation