Employee Item_Values Expense

by

Overview

The Employee Item Values Expense view provides the transactional value layer for employee expense and mileage items within the Common Data Model (CDM).

It captures the key numeric measures associated with employee claims and presents them in a standardised long-form item structure, enabling flexible analysis, auditability, and controlled reuse across reporting and planning use cases.

This view sits alongside its companion item views:

  • employee.item_codes_expense → classification and item descriptors
  • employee.item_dates_expense → accounting and lifecycle dates
  • employee.item_values_expense → numeric amounts and quantities

Together, these views provide a consistent and extensible model for employee expense activity.

Purpose

  • Provide a single item-level value view for employee expenses and mileage
  • Standardise monetary and quantity-based claim measures into the CDM item pattern
  • Support reporting on:
    • expense values
    • mileage quantities
    • gross and VAT-related amounts
    • unit rates
    • accounting dates
  • Enable controlled linkage between employee identity and individual expense transactions

Key Features

1. Item-Level Transaction Structure

Each row represents a single value attribute for a single expense item, using:

  • OBJECT_SEQ → Employee object (COMPANY_ID, EMP_NO)
  • ITEM_KEY → Unique expense item key (EXPENSE_ID, SEQ_NO_KEY, EXPENSE_TYPE)
  • ITEM_TYPE → Expense or Mileage
  • ATTRIBUTE → Value type (for example GROSS_PAY_AMOUNT or QUANTITY)
  • CODE_VALUE → Primary numeric value
  • VALUE_METADATA → Supporting structured context

This supports a repeatable, governed item model across employee transactional data.

2. Covers Both Expense and Mileage Claims

The view combines multiple source patterns into one unified output:

  • Expense items
    • standard employee expense claim lines
    • represented primarily through GROSS_PAY_AMOUNT
  • Mileage items
    • mileage captured within expense detail rows
    • dedicated mileage detail rows
    • includes mileage-linked cases such as Taxi

This allows mileage and expense activity to be analysed using a common item structure.

3. Long-Form Value Modelling

Rather than storing multiple value columns in a wide table, values are represented as rows, including measures such as:

  • GROSS_PAY_AMOUNT
  • QUANTITY

This keeps the design:

  • extensible
  • easier to union across sources
  • aligned with the CDM principle of separating values from codes and dates

4. Rich Metadata for Each Value

Each item value carries additional context in VALUE_METADATA, including combinations of:

  • AMOUNT
  • GROSS_VALUE
  • CURRENCY
  • UNIT_PRICE
  • VAT
  • ACCOUNT_DATE

This allows the primary numeric value to remain simple while preserving supporting detail needed for interpretation and reporting.

5. Employee-Centred Item Linking

The view links each expense item back to the employee object using:

  • COMPANY_ID
  • EMP_NO

This ensures expense activity can be analysed alongside other employee-focused domains such as:

  • employment
  • assignment
  • absence
  • workforce reporting

Associated Views

Employee Item Codes Expense

This companion view should provide the descriptive and classification layer for each expense item.

Typical use includes:

  • expense type
  • claim category
  • mileage vs expense classification
  • status or approval codes
  • other item descriptors needed for grouping and filtering

Purpose:

  • explain what the item is
  • classify the item for slicing and reporting
  • avoid embedding descriptive codes into the values layer

Employee Item Dates Expense

This companion view should provide the temporal layer for each expense item.

Typical use includes:

  • account date
  • expense submission date
  • transaction date
  • approval or posting dates, where required

Purpose:

  • support time-based analysis
  • separate date logic from values and codes
  • align expense items to financial and operational periods

Design Principles

1. Separation of Concerns

The expense item model follows the standard CDM pattern:

  • values → numeric measures
  • codes → classifications and labels
  • dates → temporal context

This prevents one view becoming overloaded with mixed semantics.

2. Employee as Operational Owner

The expense item is attached to the employee workforce object, not to the broader person identity domain.

This is appropriate because the data describes:

  • employee claim activity
  • workforce-linked operational transactions
  • reimbursable expense and mileage behaviour

rather than restricted personal identity.

3. Extensible by Attribute

New measures can be added as new ATTRIBUTE rows without redesigning the structure.

This makes the model resilient to:

  • source changes
  • additional value types
  • evolving reporting requirements

4. Safe Reporting Structure

The design provides transactional detail without requiring consumers to join directly to raw source tables.

This improves:

  • usability
  • audit consistency
  • reporting governance

Usage Guidance

Use this view when:

  • analysing employee claim values
  • aggregating expense or mileage totals
  • assessing reimbursement activity
  • linking employee operational spend to reporting periods or categories

Recommended pattern:

  • join to employee.core_details via OBJECT_SEQ
  • join to associated item code and date views via OBJECT_SEQ and ITEM_KEY
  • keep interpretation of item meaning in the codes layer
  • keep time analysis in the dates layer

Do not:

  • treat VALUE_METADATA as a replacement for proper item codes or dates
  • flatten all logic into this one view
  • expand ITEM_KEY unnecessarily for routine reporting

What This View Is Not

  • Not a header-level expense register
  • Not a full expense workflow model
  • Not the descriptive classification layer
  • Not the date history layer

It is the numeric fact layer for employee expense items.

Summary

employee.item_values_expense provides the governed transactional value layer for employee expenses and mileage, using a standardised item-based CDM structure.

Together with its associated codes and dates views, it supports a clear model in which:

  • the employee object identifies who the claim belongs to
  • the item key identifies the specific claim line
  • the codes layer explains what the item is
  • the dates layer explains when it occurred
  • the values layer shows the measurable quantity or amount

That makes it a strong and scalable foundation for employee expense reporting.

Leave a Comment