tbl_expense_policy

-1 rows


Description

Module: HRMS Expense Management
Purpose: Defines comprehensive expense policy rules with configurable limits, frequency controls, and eligibility criteria for different expense types across the organization.
Data: Each row represents a complete expense policy configuration with multi-level limits, submission rules, and calculation methods for specific expense categories.
Process Usage:
- Configures expense eligibility rules and limits for different employee groups.
- Defines submission deadlines, payment cycles, and approval workflows.
- Sets frequency and amount limits across multiple time periods (daily, monthly, quarterly, etc.).
Key Points:
- Multi-dimensional limit system with time-based constraints.
- Flexible eligibility criteria using JSON-based rule engine.
- Integrated payment calculation and variation handling.
Business Impact: Ensures consistent expense policy enforcement, prevents policy violations, and automates expense validation and limit checking across the organization.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
id bigserial 19 √ nextval('tbl_expense_policy_id_seq'::regclass)

Primary key
Format: Long (auto-increment)
Used as: Unique identifier for each expense policy.
expense_type_id int8 19 √ null

FK: public.tbl_expense_type.id
Purpose: Links this policy to a specific expense category or type.
Usage: Determines which expense category this policy applies to (Travel, Meals, Accommodation, etc.).
effective_date date 13 √ null

Format: Date
Purpose: Date when the policy becomes active and enforceable.
Usage: Policy version control - allows future-dated policy implementations.
Business Logic: Policies only apply to expenses dated on or after effective_date.
payment_date varchar 100 √ null

Format: String
Purpose: Defines when payments are processed for approved expenses.
Examples: “END_OF_MONTH”, “15TH_AND_LAST”, “WEEKLY”, “IMMEDIATE”
Usage: Automated payment scheduling and employee expectation setting.
next_payment_cycle varchar 100 √ null

Format: String
Purpose: Indicates the next scheduled payment cycle for this policy.
Usage: Payment forecasting and cash flow management.
Examples: “2024-08-31”, “2024-09-15”
claim_submission_days_limit int8 19 √ null

Format: Long (Days)
Purpose: Maximum number of days after expense date when claim can be submitted.
Usage: Enforces timely expense reporting and accounting period compliance.
Business Logic: Claims submitted beyond this limit are automatically rejected or require exception approval.
weightage int8 19 √ null

Format: Long
Purpose: Priority or importance weight for policy evaluation in rule conflicts.
Usage: When multiple policies apply, higher weightage policies take precedence.
Business Logic: Used in policy conflict resolution and composite rule evaluation.
applicable_criteria text 2147483647 √ null

Format: String (JSON Rule Engine Syntax)
Purpose: Defines which employees are eligible for this policy based on organizational attributes.
JSON Structure Examples:
- Department-based: {"department": ["SALES", "MARKETING"]}
- Grade-based: {"grade": {"gte": 5}}
- Location-based: {"location": ["MUMBAI", "DELHI"]}
- Composite rules with AND/OR logic
Usage: Dynamic policy assignment based on employee master data.
calculate_payment int8 19 √ null

Format: Long
Purpose: Determines how payment amounts are calculated for this policy.
Values:
- 1: Fixed amount per instance
- 2: Variable amount based on actuals
- 3: Slab-based calculation
- 4: Percentage-based calculation
Usage: Defines the payment calculation methodology.
is_fixed int8 19 √ null

Format: Long (0/1)
Purpose: Indicates if the expense amount is fixed or variable.
Values:
- 1: Fixed amount (use “amount” field)
- 0: Variable amount (based on actual submission)
Usage: Determines whether to use predefined amount or submitted amount.
amount float8 17,17 √ null

Format: Double
Purpose: Fixed amount for expenses when is_fixed = 1.
Usage: Standard reimbursement amount for fixed-amount policies.
Examples: 500.00 (fixed daily meal allowance), 2000.00 (fixed travel allowance)
allow_to_exceed_limit int8 19 √ null

Format: Long (0/1)
Purpose: Controls whether expenses can exceed defined limits with approval.
Values:
- 1: Allow exceed with justification and approval
- 0: Strict enforcement - no exceedance allowed
Usage: Policy flexibility control for exceptional circumstances.
max_number_of_instance_in_day int8 19 √ null

Format: Long
Purpose: Maximum number of expense claims allowed per day under this policy.
Usage: Prevents abuse and controls claim frequency.
Examples: 1 (one meal per day), 2 (two taxi rides per day)
max_number_of_instance_in_month int8 19 √ null

Format: Long
Purpose: Maximum number of expense claims allowed per calendar month.
max_number_of_instance_in_quarter int8 19 √ null

Format: Long
Purpose: Maximum number of expense claims allowed per financial quarter.
max_number_of_instance_in_half_year int8 19 √ null

Format: Long
Purpose: Maximum number of expense claims allowed per half-year period.
max_number_of_instance_in_year int8 19 √ null

Format: Long
Purpose: Maximum number of expense claims allowed per calendar year.
max_amount_in_day float8 17,17 √ null

Format: Double
Purpose: Maximum total amount allowed per day across all instances.
Usage: Daily spending cap enforcement.
Examples: 1000.00 (max ₹1000 per day for meals)
max_amount_in_month float8 17,17 √ null

Format: Double
Purpose: Maximum total amount allowed per calendar month.
max_amount_in_quarter float8 17,17 √ null

Format: Double
Purpose: Maximum total amount allowed per financial quarter.
max_amount_in_half_year float8 17,17 √ null

Format: Double
Purpose: Maximum total amount allowed per half-year period.
max_amount_in_year float8 17,17 √ null

Format: Double
Purpose: Maximum total amount allowed per calendar year.
createdby varchar 100 √ null
modifiedby varchar 100 √ null
createddate timestamp 29,6 √ null
modifieddate timestamp 29,6 √ null
variation_type varchar 100 √ null

Format: String
Purpose: Defines how amount variations are handled for this policy.
Examples: “FIXED”, “VARIABLE”, “SLAB_BASED”, “TIERED”, “LOCATION_BASED”
Usage: Complex amount calculation scenarios.
amount_fields varchar 1000 √ null

Format: String (JSON)
Purpose: Additional amount fields for complex calculation scenarios.
JSON Structure: Field definitions for multi-component amount calculations.
Usage: Advanced expense calculations with multiple components.
amount_field_limit text 2147483647 √ null

Format: String (JSON)
Purpose: Limit configurations for individual amount fields.
Usage: Granular control over specific expense components.
amount_field_limit_day text 2147483647 √ null

Format: String (JSON)
Purpose: Daily limits for individual amount fields.
Usage: Component-level daily spending controls.
amount_field_limit_month text 2147483647 √ null

Format: String (JSON)
Purpose: Monthly limits for individual amount fields.
amount_field_limit_quarter text 2147483647 √ null

Format: String (JSON)
Purpose: Quarterly limits for individual amount fields.
amount_field_limit_half_year text 2147483647 √ null

Format: String (JSON)
Purpose: Half-yearly limits for individual amount fields.
amount_field_limit_year text 2147483647 √ null

Format: String (JSON)
Purpose: Yearly limits for individual amount fields.
max_number_of_attachments int8 19 √ 1

Format: Long
Purpose: Maximum number of supporting documents required/allowed per claim.
Usage: Controls documentation requirements and storage management.
Examples: 1 (single receipt), 3 (multiple supporting documents)
policy_name varchar 70 √ null

Format: String
Purpose: Human-readable name identifying the expense policy.
Examples: “Domestic Travel Policy”, “Client Entertainment Policy”, “Office Supplies Policy”
Usage: Displayed in policy selection interfaces and reports.

Relationships

Close relationships within degrees of separation