tbl_user_trip_detail

-1 rows


Description

Module: HRMS Employee Tracking
Purpose: Stores summarized trip information for employees, capturing travel periods, distances, and expense claim status for automated expense processing.
Data: Each row represents a completed trip or travel period with calculated distance and expense generation status, serving as the bridge between tracking data and expense claims.
Process Usage:
- Aggregates raw tracking data into meaningful trip segments with calculated distances.
- Tracks expense claim generation status for automated travel reimbursement.
- Provides trip-level context for travel pattern analysis and expense validation.
Key Points:<br-**Trip-level aggregation of tracking data with distance calculations.
- Integration point between location tracking and expense management systems.
- Automated expense claim generation tracking and status management.
Business Impact: Enables automated travel expense processing, provides trip-level analytics for travel patterns, and ensures accurate distance-based reimbursement calculations.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
id serial 10 √ nextval('tbl_user_trip_detail_id_seq'::regclass)

Primary key
Format: Long (auto-increment)
Used as: Unique identifier for each trip record.
employee_id varchar 500 null

FK: public.tbl_employee.employeeid
Purpose: Identifies the employee associated with this trip.
Dependency: Links trip data to employee master records for expense processing and reporting.
daily_tracker_id int4 10 √ null

FK: public.tbl_daily_tracker.id
Purpose: Links this trip record to the daily tracking summary for consolidated reporting.
Usage: Enables roll-up of multiple trips into daily travel summaries and attendance context.
date date 13 null

Format: Date
Purpose: The calendar date on which the trip occurred.
Usage: Primary grouping key for daily trip analysis and expense period categorization.
Business Logic: Used for daily travel pattern analysis and expense period validation.
intime timestamp 29,6 null

Format: Timestamp
Purpose: Exact date and time when the trip began (departure time).
Collection: Derived from tracking data when employee starts moving from a location.
Usage: Start point for trip duration calculation and expense period determination.
outtime timestamp 29,6 null

Format: Timestamp
Purpose: Exact date and time when the trip ended (arrival time).
Collection: Derived from tracking data when employee arrives at destination.
Usage: End point for trip duration calculation and expense period determination.
totalkm numeric 0 null

Format: Double (Kilometers)
Purpose: Calculated total distance traveled during the trip in kilometers.
Calculation Method:
- Sum of distances between consecutive tracking points
- Route-based distance calculation using mapping APIs
- Straight-line distance for short trips with validation
Business Use: Primary metric for distance-based expense calculations and travel reimbursement.
createdby varchar 50 √ null
createddate timestamp 29,6 √ null
modifiedby varchar 50 √ null
modifieddate timestamp 29,6 √ null
expensetracking varchar 50 √ null

Format: String (JSON or structured data)
Purpose: Additional expense-related data and tracking context for the trip.
Content Examples:
- Vehicle type used for the trip
- Trip purpose or project code
- Route information and waypoints
- Additional expense categories or notes
- Validation flags and approval context
Integration: Provides contextual data for expense claim generation and validation.
expensecreated bool 1 √ null

Flag Type: Boolean
True: An expense claim has been automatically generated from this trip
False: No expense claim has been created yet (pending processing)
Workflow States:
- FALSE: Trip recorded, expense pending generation
- TRUE: Expense claim created, linked to this trip
Business Logic: Prevents duplicate expense claims for the same trip.

Indexes

Constraint Name Type Sort Column(s)
tbl_user_trip_detail_pk_tbl_user_trip_detail Primary key Asc id

Relationships

Close relationships within degrees of separation