انتقل إلى المحتوى الرئيسي

Temporal Item Pricing

Overview​

Temporal Item Pricing is a date-effective pricing system that tracks every price change for every item over time, creating an immutable historical record. Unlike traditional systems that overwrite old prices, temporal pricing maintains complete price history with effective dates, enabling time-travel queries, future planning, and regulatory compliance.

This document explains how item prices work within costing versions, focusing on the business value and practical usage.

Core Concepts​

1. What is an Item Price?​

An Item Price (also called Item Cost) is a single price record for a specific item in a specific costing version. It consists of:

  • Item: What product/material this price applies to (e.g., "A0001 - Steel Bracket")
  • Unit Cost: The cost per unit (e.g., $12.50)
  • Unit of Measure: What unit the cost applies to (e.g., "EACH", "KG", "BOX")
  • Price Type: How the price was determined (Cost, Purchase, Sales, Calculated)
  • From Date: When this price becomes effective
  • Site: Optional - price can be site-specific or default (applies to all sites)
  • Activation Status: Whether this price is active or pending approval

2. Date-Effective Pricing Explained​

Traditional pricing systems overwrite old prices when you update them:

❌ TRADITIONAL APPROACH (Overwrites History)
Update Item A0001 price from $10.00 to $12.50
Result: Only $12.50 exists, $10.00 is lost forever

Temporal pricing preserves all prices with effective dates:

✅ TEMPORAL APPROACH (Preserves History)
Item A0001 Price Records:
├─ $10.00, FromDate: 2024-01-01 [Activated]
├─ $12.50, FromDate: 2024-02-15 [Activated]
└─ $14.00, FromDate: 2024-04-01 [Pending]

Query "What was the cost on February 10?" → $10.00
Query "What is the cost on February 20?" → $12.50
Query "What will the cost be on April 5?" → $14.00 (once activated)

3. How Price Selection Works​

When the system needs to find the cost of an item on a specific date, it follows these rules:

Step 1: Filter by Activation Status

  • Only consider prices where IsActivated = true

Step 2: Filter by Effective Date

  • Only consider prices where FromDate <= QueryDate

Step 3: Apply Site Specificity

  • If querying for a specific site, prefer site-specific prices
  • Fall back to default (non-site) prices if no site-specific price found

Step 4: Select Most Recent

  • Return the price with the most recent FromDate

Example:

Item: A0001, Query Date: February 20, 2024, Site: WAREHOUSE-01

Available Prices:
├─ $10.00, FromDate: 2024-01-01, Site: null [Activated]           ✅ Eligible
├─ $11.50, FromDate: 2024-01-01, Site: WAREHOUSE-01 [Activated]   ✅ Eligible (site-specific)
├─ $12.50, FromDate: 2024-02-15, Site: null [Activated]           ✅ Eligible (most recent)
└─ $14.00, FromDate: 2024-04-01, Site: null [Pending]             ❌ Not activated yet

Result: $11.50 (site-specific price takes precedence)

If querying without site filter:
Result: $12.50 (most recent default price)

Price Types​

Prices can come from different sources, tracked via PriceType:

Standard Cost (PriceType.Cost)​

  • Source: Manually entered or imported standard costs
  • Purpose: Official cost for inventory valuation and COGS
  • Example: "Our standard cost for steel brackets is $12.50"

Purchase Price (PriceType.Purchase)​

  • Source: Imported from purchase orders or supplier quotes
  • Purpose: Tracking actual purchase costs
  • Example: "Last purchase from Supplier ABC was $11.80"

Sales Price (PriceType.Sales)​

  • Source: Sales price lists or customer agreements
  • Purpose: Tracking target selling prices
  • Example: "List price for customers is $18.00"

Calculated Cost (PriceType.CalculatedCost)​

  • Source: BOM cost roll-ups and manufacturing calculations
  • Purpose: Bottom-up cost calculation from components
  • Example: "Based on BOM, calculated cost is $13.42"
    • Includes detailed cost allocations (Material: $8.00, Labor: $3.00, Overhead: $2.42)

Price Activation Workflow​

Why Activation Matters​

Prices go through a two-state lifecycle:

┌─────────────────┐     Activate      ┌──────────────────┐
│ Pending (Draft) ├──────────────────►│ Activated (Live) │
└─────────────────┘                   └──────────────────┘
        ▲                                       │
        │                                       │
        └───────────── Deactivate ──────────────┘
                     (for corrections)

Pending Status (IsActivated = false)​

Meaning: Price has been entered but not yet approved for use

Behavior:

  • Not visible in cost calculations
  • Can be modified or deleted
  • Used for staging and review

Use Cases:

  • Future price negotiations still in progress
  • Bulk imports awaiting approval
  • Prices pending manager review

Example:

You're negotiating with a supplier for Q2 prices:
- Enter tentative prices as Pending
- Run cost analysis using current prices
- Once negotiations finalize, activate the new prices

Activated Status (IsActivated = true)​

Meaning: Price is approved and available for cost calculations

Behavior:

  • Used in all cost queries and calculations
  • Immutable - cannot be edited
  • Deactivation available for corrections

Use Cases:

  • Current operational costs
  • Historical cost records
  • Approved future prices

Example:

On February 1st, you activate future prices with FromDate = March 1st:
- From February 1-28: System uses old prices
- From March 1 onward: System automatically uses new prices
- ActivatedDate = Feb 1 (when you approved it)
- FromDate = Mar 1 (when it becomes effective)

Activation Timestamp​

Every activated price records two important dates:

  • FromDate: When the price becomes effective for calculations
  • ActivatedDate: When someone approved the price

Example Scenario:

January 15, 2024: Cost analyst enters new prices with FromDate = February 1, 2024
  Status: Pending
  FromDate: February 1, 2024
  ActivatedDate: null

January 20, 2024: Manager reviews and activates the prices
  Status: Activated
  FromDate: February 1, 2024          ← Price effective date
  ActivatedDate: January 20, 2024     ← When it was approved

February 1, 2024: System automatically starts using the new prices
  No action needed - the FromDate controls when it takes effect

Site-Specific Pricing​

Prices can be:

  • Default (Site = null): Applies to all sites/warehouses
  • Site-Specific: Applies only to a specific warehouse/location

Business Use Cases​

Scenario 1: Regional Cost Differences

Item: A0001 - Steel Bracket

Default Price:      $12.00 (applies to all sites)
WAREHOUSE-WEST:     $13.50 (higher due to shipping costs)
WAREHOUSE-EAST:     $11.00 (closer to supplier)

Query for WAREHOUSE-WEST: Returns $13.50
Query for WAREHOUSE-SOUTH: Returns $12.00 (uses default)

Scenario 2: Special Supplier Agreements

Item: B0002 - Plastic Housing

Default Price:      $8.00 (standard supplier)
PLANT-TEXAS:        $7.20 (local supplier agreement)

Texas plant benefits from lower costs while other sites use standard pricing

Price Resolution Logic​

When site is specified:

  1. Look for site-specific price
  2. If found → use it
  3. If not found → fall back to default price

When site is NOT specified:

  • Only return default (non-site) prices

Common Business Workflows​

Workflow 1: Annual Standard Cost Update​

Objective: Update all item costs for new fiscal year

Steps:

  1. Create new costing version: "FY2025-STD"
  2. Bulk import prices from supplier catalog (Status: Pending, FromDate: Jan 1, 2025)
  3. Cost team reviews prices over 2-3 weeks
  4. Activate costing version
  5. Activate all approved item prices
  6. On January 1st, system automatically uses new prices

Result: Smooth transition with no manual date switching

Workflow 2: Mid-Year Price Increase​

Objective: Supplier raises prices effective March 1st

Steps:

  1. Open current costing version: "FY2024-STD"
  2. Add new prices for affected items (FromDate: March 1, 2024, Status: Pending)
  3. Run cost impact analysis while prices are pending
  4. Present analysis to management
  5. Once approved, activate the new prices
  6. On March 1st, system automatically applies increases

Result: Forward planning with impact visibility

Workflow 3: Correcting a Price Error​

Objective: Fix incorrect price entered last month

Problem: Entered $10.00 instead of $12.00 on February 1st

Solution (Immutable Approach):

❌ Cannot do: Edit the $10.00 price (immutable for audit)

✅ Correct approach:
1. Deactivate the incorrect price ($10.00)
2. Add new price record ($12.00, FromDate: Feb 1, 2024)
3. Activate the new price
4. Both records remain in history for audit trail

Why Not Just Edit?

  • Transactions may have already used the old price
  • Financial audits require immutable cost history
  • Regulatory compliance demands complete audit trail

Workflow 4: Future Price Planning​

Objective: Set up quarterly price updates in advance

Steps:

January 15, 2024: Enter all quarterly prices

Q1 Prices (FromDate: Jan 1):   Already activated and in use
Q2 Prices (FromDate: Apr 1):   Pending (not activated yet)
Q3 Prices (FromDate: Jul 1):   Pending
Q4 Prices (FromDate: Oct 1):   Pending

March 25, 2024: Review and activate Q2 prices

April 1, 2024: Q2 prices automatically take effect

June 20, 2024: Review and activate Q3 prices

July 1, 2024: Q3 prices automatically take effect

Result: Entire year's pricing managed in advance

Querying and Reporting​

Query Type 1: Current Active Price​

Question: "What is the current cost of item A0001?"

Query: GetItemPrice(
  ItemId: A0001,
  AsOfDate: today,
  IsActivated: true
)

Result: $12.50 (most recent activated price)

Query Type 2: Historical Price Lookup​

Question: "What was the cost of item A0001 on February 10, 2024?"

Query: GetItemPrice(
  ItemId: A0001,
  AsOfDate: 2024-02-10,
  IsActivated: true
)

Result: $10.00 (price that was effective on that date)

Use Case: Analyzing past transaction costs, audit investigations

Query Type 3: Pending Prices Report​

Question: "What prices are awaiting approval in this version?"

Query: GetItemPricesForVersion(
  VersionId: FY2024-STD,
  IsActivated: false
)

Result: List of all pending prices for manager review

Use Case: Approval workflows, review queues

Query Type 4: Complete Price History​

Question: "Show me all price changes for item A0001"

Query: GetItemPriceHistory(
  ItemId: A0001
)

Result:
├─ $8.00,  FromDate: 2023-01-01, Activated: 2022-12-15 [Historical]
├─ $10.00, FromDate: 2024-01-01, Activated: 2023-12-20 [Historical]
├─ $12.50, FromDate: 2024-02-15, Activated: 2024-02-10 [Current]
└─ $14.00, FromDate: 2024-04-01, Activated: 2024-03-25 [Future]

Use Case: Trend analysis, cost volatility reporting

Cost Allocations (for Calculated Costs)​

When a price comes from BOM calculations (PriceType.CalculatedCost), it includes detailed cost breakdowns:

Cost Groups​

Costs are allocated across different categories:

  • Material: Raw material costs
  • Labor: Direct labor costs
  • Overhead: Factory overhead, utilities, depreciation
  • Subcontracting: External processing costs

Example: Manufactured Item​

Item: WIDGET-500 (Manufactured Assembly)
Total Unit Cost: $45.80

Cost Allocations:
├─ Material:        $28.00  (61.1%)
├─ Labor:           $10.00  (21.8%)
├─ Overhead:        $6.00   (13.1%)
└─ Subcontracting:  $1.80   (3.9%)

FromDate: 2024-02-01
PriceType: CalculatedCost
IsActivated: true

Business Value:

  • Understand cost drivers
  • Identify opportunities for cost reduction
  • Compare material vs. labor content
  • Track overhead absorption rates

Integration Points​

With Inventory Module​

  • Inventory Valuation: Uses activated prices from standard costing version
  • Inventory Transactions: Record moving average costs in price history
  • Stock Adjustments: Reference historical prices for variance analysis

With Production/Manufacturing​

  • BOM Calculations: Roll up component costs to create calculated prices
  • Production Orders: Use prices from specified costing version for job costing
  • Cost Variance Analysis: Compare actual costs vs. standard prices

With Accounts Receivable​

  • COGS Calculation: When posting customer invoices, system queries item price using posting date
  • Profitability Analysis: Sales price vs. standard cost from costing version

With Procurement​

  • Purchase Price Variance: Compare actual purchase price vs. standard cost
  • Supplier Price Updates: Import supplier catalog prices as new price records

Benefits of Temporal Pricing​

1. Complete Audit Trail​

  • ✅ Every price change is permanently recorded
  • ✅ Know exactly what cost was used for any past transaction
  • ✅ Meets regulatory requirements for financial record-keeping
  • ✅ Immutable history prevents tampering

2. Time-Travel Capabilities​

  • ✅ Query costs as of any past date
  • ✅ Rerun financial reports using historical prices
  • ✅ Analyze cost trends over time
  • ✅ Support audit investigations with accurate historical data

3. Future Planning​

  • ✅ Enter prices months in advance with future FromDates
  • ✅ Prices automatically take effect on scheduled date
  • ✅ No manual intervention needed for planned increases
  • ✅ Build entire year's pricing schedule upfront

4. Approval Workflows​

  • ✅ Prices can be entered as "pending" for review
  • ✅ Management approves prices before activation
  • ✅ Bulk imports don't accidentally go live
  • ✅ Staged rollout of price changes

5. Correction Without Deletion​

  • ✅ Deactivate incorrect prices
  • ✅ Add corrected prices
  • ✅ Original error remains visible for audit
  • ✅ No data loss, complete transparency

6. Cost Analysis​

  • ✅ Track price volatility over time
  • ✅ Identify cost trends and patterns
  • ✅ Compare prices across different periods
  • ✅ Detailed cost group allocations for manufactured items

Best Practices​

DO:​

✅ Always use FromDate to represent price effective date ✅ Leave prices as Pending until confirmed ✅ Activate prices before their FromDate to allow advance planning ✅ Use site-specific pricing only when necessary (keep defaults simple) ✅ Include detailed cost allocations for calculated costs ✅ Deactivate incorrect prices rather than deleting them

DON'T:​

❌ Don't try to edit activated prices (immutable) ❌ Don't delete price records (audit trail) ❌ Don't backdate FromDate after activation (confusing) ❌ Don't activate prices without review (approval workflow) ❌ Don't use past FromDates for corrections (use actual error date)

Summary​

Temporal Item Pricing provides:

  • ✅ Complete, immutable price history for every item
  • ✅ Date-effective pricing with automatic transitions
  • ✅ Dual activation model (version-level and price-level)
  • ✅ Site-specific pricing support
  • ✅ Approval workflows with pending/activated states
  • ✅ Time-travel queries for historical analysis
  • ✅ Future price planning with scheduled effective dates
  • ✅ Detailed cost allocations for manufactured items
  • ✅ Audit compliance and regulatory readiness