Price Entities
Overview
The Price collection consists of all of the different types of prices that are generated or imported into the kalibrate pricing system.
Price Data Model
The following data model shows the Price entity and it's relational links. For the full model, see Data Model.
Competitor Price
| Name | Description |
|---|---|
| Entity Description | Details related to a customer's competitor's prices |
| Source Schema | dbo |
| Source Table | PNCompetitorPrice |
| Destination Collection(Endpoint) | Price |
| Entity Type | Price |
| Entity Variant | Competitor |
Competitor Price Attribute Details
| Field | Reference Entity | Field Description | Format | Max Bytes |
|---|---|---|---|---|
| CheckedForPG | Evaluated to determine if price generation occurred | bit | 1 | |
| Product | Product | unique identifier assigned to a product of a specific competitor (usually a long string of numbers) | uniqueidentifier | 16 |
| PriceID | Unique identifier assigned to a price for a product of a specific competitor (usually a number) | int | 4 | |
| StatusID | PriceStatus | unique identifier for status of specific competitor price mapped to own site | int | 4 |
| DataChannelID | DataChannel | Unique identifier assigned to a specific data channels to transport data to and from Kalibrate | int | 4 |
| EffectiveTime | Competitor Price effective time | datetime | 8 | |
| Origin | The source identified in the market share report(ex. PNWeb, Kmobile, OPIS) | nvarchar | 100 | |
| OriginalEffectiveTime | Pre price ammendament effective time | datetime | 8 | |
| Site | Site | Unique identifier for an Own Site location (used in mapping; usually long string of numbers) | uniqueidentifier | 16 |
| Price | Competitor Price Value | money | 8 | |
| Source | The source identified in the market share report | source | 40 | |
| User | Username of the user that entered a competitor price survey | nvarchar | 100 |
Cost Price
| Name | Description |
|---|---|
| Entity Description | Details related to cost prices |
| Source Schema | dbo |
| Source Table | PNCostPrice |
| Destination Collection(Endpoint) | Price |
| Entity Type | Price |
| Entity Variant | Cost |
Cost Price Attribute Details
| Field | Reference Entity | Field Description | Format | Max Bytes |
|---|---|---|---|---|
| CheckedForPG | Evaluated to determine if price generation occurred | bit | 1 | |
| Product | Product | Unique identifier for an Own Site product (used in mapping; usually long string of numbers) | uniqueidentifier | 16 |
| PriceID | Unique identifier specific to cost price assigned to a product | int | 4 | |
| EffectiveTime | Cost Price Effective Time | datetime | 8 | |
| Price | Value assigned to either own site price competitor price cost type cost value dealer price DTA price | money | 8 | |
| Source | The source identified in the market share report. | source | 40 |
Dealer Price
| Name | Description |
|---|---|
| Entity Description | Details related to dealer prices |
| Source Schema | dbo |
| Source Table | PNDealerPrice |
| Destination Collection(Endpoint) | Price |
| Entity Type | Price |
| Entity Variant | DTA |
Dealer Price Attribute Details
| Field | Reference Entity | Field Description | Format | Max Bytes |
|---|---|---|---|---|
| DealerPriceID | unique identifier specifying price for a dealer site received from the Dealer Trade Area | int | 4 | |
| DealerSiteRelation | DealerSiteRelation | Unique Identifier for the Own Site/Dealer Site Relation | uniqueidentifier | 16 |
| ProductID | Product | Unique identifier for an Own Site product (used in mapping; usually a number) | int | 4 |
| DealerMargin | margin share achieved for the dealer by the DTW price. | decimal | 9 | |
| TVAAdjustment | Value is used in dealer pricing to adjust a dealer trade area level price at individual dealer sites. | decimal | 9 |
Dealer Trade Area Price
| Name | Description |
|---|---|
| Entity Description | Details related to Dealer Trade Area prices |
| Source Schema | dbo |
| Source Table | PNDTAPrice |
| Destination Collection(Endpoint) | Price |
| Entity Type | Price |
| Entity Variant | DTA |
Dealer Trade Area Price Attribute Details
| Field | Reference Entity | Field Description | Format | Max Bytes |
|---|---|---|---|---|
| DTAPriceID | Unique identifier for a price value assigned to a specific Dealer Trade Area (DTA) | int | 4 | |
| OwnProductID | Product | Unique identifier for an Own Site product (used in mapping; usually a number) | int | 4 |
| BaseCostPrice | The product cost configured by a formula for each product in a Dealer trade area. | costprice | 9 | |
| AdditionalCostPrice | Value assigned to the cost that is added to the Net DTW in order to make the Gross DTW | costprice | 9 | |
| CompanyMargin | margin share achieved for the company by the DTW price. | decimal | 9 | |
| DealerMargin | margin share achieved for the dealer by the DTW price. | decimal | 9 | |
| GrossDTWPrice | Calculated as the NET DTW plus any additional costs such as local taxes or delivery costs. | money | 8 | |
| NetDTWPrice | The price at which an independent dealer buys fuel from your company in order to sell on to the public. Calculated from the product cost (the base cost) and the margin that you want to make (the company margin). | money | 8 | |
| NotionalStreetPrice | A benchmark retail price used in dealer pricing to represent the typical street price in a dealer trade area. | money | 8 |
Notional Street Price
| Name | Description |
|---|---|
| Entity Description | Details related to Notional Street prices |
| Source Schema | dbo |
| Source Table | PNNotionalStreetPrice |
| Destination Collection(Endpoint) | Price |
| Entity Type | Price |
| Entity Variant | NotionalStreet |
Notional Street Price Attribute Details
| Field | Reference Entity | Field Description | Format | Max Bytes |
|---|---|---|---|---|
| NotionalStreetPriceID | Unique identifier assigned to a specific Notional Street Price calculated for the representative dealer trade area. | int | 4 | |
| OwnProductUID | Product | Unique identifier for an Own Site product (used in mapping; usually long string of numbers) | uniqueidentifier | 16 |
| Price | Value assigned to either own site price competitor price cost type cost value dealer price DTA price | money | 8 | |
| EffectiveTime | Notinal Street Price Effective Time | datetime | 8 |
OPIS Competitor Price
| Name | Description |
|---|---|
| Entity Description | Details related to a customer's competitor's prices source via OPIS |
| Source Schema | dbo |
| Source Table | PNOPISCompetitorPrice |
| Destination Collection(Endpoint) | Price |
| Entity Type | Price |
| Entity Variant | OPISCompetitor |
OPIS Competitor Price Attribute Details
| Field | Reference Entity | Field Description | Format | Max Bytes |
|---|---|---|---|---|
| Product | Product | unique identifier assigned to a product of a specific competitor (usually a long string of numbers) | uniqueidentifier | 16 |
| CopiedToHistory | Model item moved to History (such as cost price delivery volume plan data etc) | bit | 1 | |
| PriceID | Unique identifier for OPIS competitor price; used in mapping. | int | 4 | |
| EffectiveTime | Effective Time of OPIS Competitor Price | datetime | 8 | |
| Site | Site | Unique identifier for an Own Site location (used in mapping; usually long string of numbers) | uniqueidentifier | 16 |
| Price | Value assigned to either own site price competitor price cost type cost value dealer price DTA price | money | 8 | |
| Source | The source identified in the market share report. | source | 40 |
OPIS Own Price
| Name | Description |
|---|---|
| Entity Description | Details related to a customer's own prices via OPIS feed |
| Source Schema | dbo |
| Source Table | PNOpisOwnPrice |
| Destination Collection(Endpoint) | Price |
| Entity Type | Price |
| Entity Variant | OPISOwn |
OPIS Own Price Attribute Details
| Field | Reference Entity | Field Description | Format | Max Bytes |
|---|---|---|---|---|
| Product | Product | Unique identifier for an Own Site product (used in mapping; usually long string of numbers) | uniqueidentifier | 16 |
| CopiedToHistory | Model item moved to History (such as cost price delivery volume plan data etc) | bit | 1 | |
| PriceID | Unique identifier for OPIS own price for specific own site | int | 4 | |
| EffectiveTime | Time OPIS Own Price is Effective | datetime | 8 | |
| Price | Value assigned to either own site price competitor price cost type cost value dealer price DTA price | money | 8 | |
| Source | The source identified in the market share report. | source | 40 |
Own Price
| Name | Description |
|---|---|
| Entity Description | Details related to a customer's own prices |
| Source Schema | dbo |
| Source Table | PNOwnPrice |
| Destination Collection(Endpoint) | Price |
| Entity Type | Price |
| Entity Variant | Own |
Own Price Attribute Details
| Field | Reference Entity | Field Description | Format | Max Bytes |
|---|---|---|---|---|
| Product | Product | Unique identifier for an Own Site product (used in mapping; usually long string of numbers) | uniqueidentifier | 16 |
| PriceID | Unique identifier for an Own Site price (used in mapping; usually a number) | int | 4 | |
| EffectiveTime | Time price was effective | datetime | 8 | |
| Origin | Source of price (CSV,XML or Survey Username) | nvarchar | 100 | |
| OriginalEffectiveTime | Pre-Price Ammendment effective time | datetime | 8 | |
| Price | Value assigned to either own site price competitor price cost type cost value dealer price DTA price | money | 8 | |
| Source | The source identified in the market share report(ex. PNWeb, Kmobile, OPIS) | source | 40 |
Price Differentials
| Name | Description |
|---|---|
| Entity Description | Details related to proposed prices |
| Source Schema | dbo |
| Source Table | PNPriceDifferential |
| Destination Collection(Endpoint) | Price |
| Entity Type | Pricedifferential |
| Entity Variant | Common |
Price Differentials Attribute Details
| Field | Reference Entity | Field Description | Format | Max Bytes |
|---|---|---|---|---|
| OwnProduct | OwnproductUID | Unique identifier for the Own Proudct to which a price differential is applied | uniqueidentifier | 16 |
| diffOwnProduct | OwnproductUID | Unique Identifier for the Own Product used as the differential contraining product | unique identifier | 16 |
| diffCompetitorProduct | CompetitorproductUID | Unique Identifier for the Competitor Product used as the differential contraining product | unique identifier | 16 |
| undercut | Minimum differential value used in rule | money | 8 | |
| overrcut | Maximum differential value used in rule | money | 8 | |
| active | True or False value inidcating rule active/inactive status | bit | 1 | |
| type | Boolvalue indicating type of differential rule. Valid options are 1 and 2 | tinyint | 1 |
Proposed Price
| Name | Description |
|---|---|
| Entity Description | Details related to proposed prices |
| Source Schema | dbo |
| Source Table | PNProposedPrice |
| Destination Collection(Endpoint) | Price |
| Entity Type | Price |
| Entity Variant | Proposed |
Proposed Price Attribute Details
| Field | Reference Entity | Field Description | Format | Max Bytes |
|---|---|---|---|---|
| AutoImplementFail | Auto Implementation rule failure based on product product group pricing settings. Moved to SFR. | bit | 1 | |
| Battled | Refers to when two sets of prices for the same product group become ready for export and are compared based on a set of rules where one is exported and one is cancelled | bit | 1 | |
| OwnProductUID | Product | Unique identifier for an Own Site product (used in mapping; usually long string of numbers) | uniqueidentifier | 16 |
| DealerRetailPrice | Retail price set by dealer (not supplied by or delivered by client) | money | 8 | |
| EffectiveTime | Effective time of proposed price | datetime | 8 | |
| ExportedTime | Time at which the latest price was sent to the site | datetime | 8 | |
| ExportID | Unique identifier for price export for product product group or site | int | 8 | |
| ExportTime | Time the price was sent to user | datetime | 8 | |
| ExportTimeDelayed | amount of time added to initial export time | bit | 1 | |
| InitialPrice | Inital Proposed Price | money | 8 | |
| PostProcessedTime | Time price was posted | datetime | 8 | |
| Price | Value assigned to either own site price competitor price cost type cost value dealer price DTA price | money | 8 | |
| PriceRequest | PriceRequest | Unique identifier for Price Request assigned to product product group or site (sql-long string of numbers) | uniqueidentifier | 16 |
| RuleUID | Rule | Unique identifier for pricing rule assigned to product product group or site (sql-long string of numbers) | uniqueidentifier | 16 |
| SequenceID | Sequential indicator | int | 4 | |
| Status.Amended | Price request price export status ; price was adjusted | bit | 1 | |
| Status.Applied | Price request price export status ; price was applied | bit | 1 | |
| Status.ExportApproved | Price request price export status ; price export was approved | tinyint | 1 | |
| Status.Exported | Price request price export status ; price was exported | bit | 1 | |
| Status.GPCReview | Price request price export status ; GPC price was sent to SFR for Review | bit | 1 | |
| Status.ReadyToExport | Price request price export status ; price is waiting for export | bit | 1 | |
| Status.Rejected | Price request price export status ; price was rejected | bit | 1 | |
| Status.SFR | Price request price export status ; price was sent to SFR | bit | 1 | |
| Status.Superseded | Price request price export status ; price was overwritten | bit | 1 |
SOE Price
| Name | Description |
|---|---|
| Entity Description | Details related to SOE prices |
| Source Schema | dbo |
| Source Table | PNSOEPrice |
| Destination Collection(Endpoint) | Price |
| Entity Type | Price |
| Entity Variant | SOE |
SOE Price Attribute Details
| Field | Reference Entity | Field Description | Format | Max Bytes |
|---|---|---|---|---|
| EffectiveTime | SOE Price Effective Time | datetime | 8 | |
| NotionalStreetPriceID | Unique identifier assigned to a specific Notional Street Price calculated for the representative dealer trade area. | int | 4 | |
| Product | Product | Unique identifier for an Own Site product (used in mapping; usually long string of numbers) | uniqueidentifier | 16 |
| Price | Value assigned to either own site price competitor price cost type cost value dealer price DTA price | money | 8 |