tbl_leavecodepolicyconfig

5 rows


Description

Module: HRMS Leave Management
Purpose: Central configuration table that defines comprehensive leave policies and rules for different leave types.
Data: Stores complete policy configurations including allotment rules, application restrictions, carry-forward settings, and special leave provisions.
Process Usage:
- Serves as the master configuration for leave entitlement calculations and validations.
- Used by leave balance engines, application validators, and policy enforcement systems.
Key Points:
- Supports multiple leave types (Paid, Unpaid, Maternity, Paternity, Compensatory Off, Anniversary, Optional Holiday).
- Configures complex business rules including probation restrictions, sandwich rules, and encashment policies.
Business Impact: Critical for accurate leave accruals, compliance with labor laws, and consistent policy application across the organization.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
id bigserial 19 √ nextval('tbl_leavecodepolicyconfig_id_seq'::regclass)
tbl_leavecodepolicyconfighistory.leavecodepolicyid fk_tbl_leavecodepolicyconfighistory_leavecodepolicyid R
tbl_leavecodepolicycounton.leavecodepolicyid fk_tbl_leavecodepolicycounton_leavecodepolicyid R
tbl_leavecodepolicyemployeemapping.leavecodepolicyid fk_tbl_leavecodepolicyemployeemapping_leavecodepolicyid R
tbl_leavecodepolicymonthlyconfig.leavecodepolicyid fk_tbl_leavecodepolicymonthlyconfig_leavecodepolicyid R

Primary key
Format: BigSerial (auto-increment)
Used as: Unique identifier for each leave policy configuration.
status varchar 100 null

Policy status indicator
Values: ACTIVE, INACTIVE
Usage: Determines whether the policy is currently applicable for leave calculations and allocations.
applicablecriteria text 2147483647 √ null

Target employee criteria for policy application
Format: JSON configuration
Logic: Defines which employees qualify for this policy based on department, location, grade, or custom rules.
isfirsttimeallocated bool 1 null

Safety lock for allocation criteria modifications
Type: Boolean
Purpose: Prevents accidental changes to allocation criteria that could invalidate existing employee mappings or historical allocations.
leavecodepolicyname varchar 100 null

User-defined name for the leave policy
Max Length: 100 characters
Constraint: Unique combination with leavecodeid
Usage: Human-readable identifier for policy selection and reporting.
description varchar 500 √ null

Detailed description of the policy purpose and rules
Max Length: 500 characters
Usage: Provides context and business rationale for the policy configuration.
leavecodeid int8 19 null
tbl_leavecodemaster.leavecodeid fk_tbl_leavecodepolicyconfig_leavecodeid R

FK: public.tbl_leavecodemaster.leavecodeid
Meaning: References the leave type (e.g., Sick Leave, Casual Leave, Maternity Leave)
Dependency: Determines which leave category this policy governs.
leavecalendar varchar 100 √ null

Leave year calculation basis
Values: CALENDAR (Jan-Dec), FINANCIAL (Apr-Mar)
Impact: Affects year-end processing, carry-forward calculations, and balance resets.
effectivefromdate date 13 null

Policy activation date
Format: Date
Usage: From when this policy becomes effective for applicable employees.
yearendclosingconfig varchar 100 √ null

Year-end processing mode
Values: AUTO, MANUAL
AUTO: Automated processing via scheduled jobs
MANUAL: Requires HR/admin intervention for year-end closure.
allotfrom varchar 100 √ null

Leave entitlement start point
Values: FROM_DOJ, AFTER_CONFIRMATION
Business Logic: Determines when employees start accruing leave balances.
roundoffmonthlyconfig varchar 100 √ null

Rounding method for monthly allocations
Values: NEAREST, MINIMUM, MAXIMUM
NEAREST: 0.25-0.75 → 0.50, >0.75 → 1.00
MINIMUM: ≥0.50 → 0.50
MAXIMUM: >0-0.50 → 0.50, >0.50 → 1.00
allotmentfrequency varchar 100 √ null

Leave allocation frequency
Values: MONTHLY, QUARTERLY, HALF_YEARLY, YEARLY, ONE_TIME
Usage: Determines how often leave balances are credited to employees.
allotmenttime varchar 100 √ null

Timing of leave allocation within cycle
Values: START_CYCLE, END_CYCLE
Impact: Affects when employees can access their allocated leaves.
allotleavecountbasedon varchar 100 √ null

Basis for leave calculation
Values: ATTENDANCE, NON_ATTENDANCE
ATTENDANCE: Prorated based on actual working days
NON_ATTENDANCE: Fixed allocation regardless of attendance.
roundoffleavecount varchar 100 √ null
allotmenttype varchar 100 √ null

Core allocation methodology
Values: FIXED, PRO_RATA
FIXED: Predetermined allocation amounts
PRO_RATA: Calculated based on service duration or other factors.
allotmentfixedtype varchar 100 √ null

Fixed allocation pattern
Values: FIXED, CUSTOMIZED
FIXED: Same amount throughout
CUSTOMIZED: Decreasing amounts across periods (comma-separated values).
allotmentfixedcount varchar 100 √ null

Fixed allocation values
Format: Comma-separated float values
Usage: For CUSTOMIZED type, provides different amounts for each period in sequence.
allotmentproratatype varchar 100 √ null

Pro-rata calculation method
Values: FIXED, CUSTOMIZED
CUSTOMIZED: Allows different rates for different months/periods.
allotmentproratacount varchar 100 √ null

Pro-rata allocation rates
Format: Comma-separated float values
Mapping: Values mapped to specific months/quarters based on leave calendar and frequency.
probationrestriction varchar 100 √ null

Probation period handling
Values: SAME_AS_GENERAL, RESTRICTION
RESTRICTION: Applies different rules during probation period.
allotprobationtype varchar 100 √ null

Probation allocation method
Values: PERCENTAGE_OF_GENERAL, CUSTOMIZED
PERCENTAGE_OF_GENERAL: Applies percentage to regular allocation
CUSTOMIZED: Specific probation allocation rules.
allotprobationcount varchar 100 √ null
roundoffallotprobationcount varchar 100 √ null
parentaleligibilitydays int4 10 √ null

Minimum service required for parental leave
Unit: Days
Usage: Eligibility threshold for maternity/paternity leave availing.
parentalclaimtime int4 10 √ null
parentalleavesfordelivery int4 10 √ null

Leave days allocated for childbirth
Unit: Days
Compliance: Configured as per organizational policy and statutory requirements.
parentalleavesforadoption int4 10 √ null
parentalleavesformiscarriage int4 10 √ null
parentalsplittime int4 10 √ null
parentalgapbtwclaimtime int4 10 √ null
parentalvalidity int4 10 √ null

Validity period for parental leave
Unit: Months
Usage: Timeframe within which parental leave must be availed after eligibility.
allowfrom varchar 100 √ null

When employees can start applying for leaves
Values: FROM_DOJ, AFTER_CONFIRMATION
Integration: Works with probation restrictions for comprehensive access control.
allowapplyingafterdays int4 10 √ null

Advance notice requirement
Unit: Days
Usage: Minimum days before leave date when application must be submitted.
displaybeforeeligibletoapply bool 1 √ null
leaveapplicationsubmittedrestriction varchar 100 √ null

Application submission timeline restriction
Values: BEFORE, AFTER
BEFORE: Must apply before certain days
AFTER: Can apply after certain days from leave date.
leaveapplicationsubmitteddays int4 10 √ null
maxleaveinprobation float8 17,17 √ null
roundoffmaxleaveinprobation varchar 100 √ null
monthlyrestrictiondays float8 17,17 √ null
noticeperiodleave float8 17,17 √ null

Leave encashment during notice period
Type: Float (NULL if disabled)
Usage: Specifies leave days encashable during employee separation.
docupload varchar 100 √ null

Document upload requirement
Values: MANDATORY, OPTIONAL
MANDATORY: Forces document upload for leave approval
Usage: Typically for medical or proof-based leaves.
docuploadreqdays float8 17,17 √ null
advanceleave float8 17,17 √ null
applyleavebefore int4 10 √ null
applyleaveafter int4 10 √ null
minleavepertransaction float8 17,17 √ null

Minimum leave that can be applied at once
Unit: Days (divisible by 0.5)
Validation: Enforced during leave application submission.
maxleavepertransaction float8 17,17 √ null

Maximum leave per single application
Unit: Days (divisible by 0.5)
Purpose: Prevents bulk leave applications beyond policy limits.
mindurationbtwinstances int4 10 √ null
maxinstancesperyear int4 10 √ null

Maximum number of leave applications per year
Type: Integer
Usage: Controls frequency of leave instances regardless of duration.
maxdayspermonth float8 17,17 √ null
holidayleaveholiday bool 1 √ null
leaveholidayleave bool 1 √ null
weekoffleaveweekoff bool 1 √ null
leaveweekoffleave bool 1 √ null
holidayleaveweekoffviceversa bool 1 √ null
carryoverenable bool 1 √ null

Enables year-end leave carry forward
Type: Boolean
Impact: Determines if unused leaves transfer to next year.
carryovercycleconfig varchar 100 √ null
carryoveryearlyconfig varchar 100 √ null
carryovervalue float8 17,17 √ null

Maximum leaves that can be carried forward
Unit: Days or Percentage
Calculation: Based on carryovercalculationunit setting.
carryovercalculationunit varchar 100 √ null
carryoverroundoff varchar 100 √ null
remainingleaveaftercarryover varchar 100 √ null
encashvalueofcarryover float8 17,17 √ null

Encashment value for carried-over leaves
Unit: Fixed amount or Percentage
Usage: Monetary value when encashing unused carried leaves.
encashunitofcarryover varchar 100 √ null
encashroundoffofcarryover varchar 100 √ null
encashprocessofcarryover varchar 100 √ null
encashpaymentmonthofcarryover varchar 100 √ null
encashformulaofcarryover varchar 100 √ null

Salary calculation formula for encashment
Values: BASIC_26, BASIC_30, GROSS_26, CTC_26, etc.
Determines: How daily salary is calculated for leave encashment.
carryovertenurelimit float8 17,17 √ null
remainingleaveaftertenurelimit varchar 100 √ null
encashvalueoftenure float8 17,17 √ null
encashunitoftenure varchar 100 √ null
encashroundoffoftenure varchar 100 √ null
encashprocessoftenure varchar 100 √ null
encashpaymentmonthoftenure varchar 100 √ null
encashformulaoftenure varchar 100 √ null
carryoverexpiry varchar 100 √ null
encashableonseparation bool 1 √ null
encashunitofseparation varchar 100 √ null
encashvalueofseparation float8 17,17 √ null
encashroundoffofseparation varchar 100 √ null
coffhalfdayhours float8 17,17 √ null

Hours required for half-day compensatory off
Type: Float
Usage: Converts extra working hours to leave entitlement.
cofffulldayhours float8 17,17 √ null

Hours required for full-day compensatory off
Type: Float
Business Rule: Typically 8+ hours for full day entitlement.
coffconfig varchar 100 √ null

Comp-off utilization method
Values: LEAVE, PAYMENT
LEAVE: Converted to leave balance
PAYMENT: Paid as overtime.
cofflimitperiod varchar 100 √ null
cofflimitdays float8 17,17 √ null
coffexpiry int4 10 √ null
createdby varchar 50 null
createddate timestamp 29,6 null
modifiedby varchar 50 null
modifieddate timestamp 29,6 null
allow_next_calendar_request bool 1 √ null

Cross-year leave application permission
Type: Boolean
Usage: Allows applying leaves for next calendar/financial year in advance.
leave_application_restriction_json text 2147483647 √ null

Detailed application restriction rules
Format: JSON configuration
Contains: Complex rules for different scenarios and day types.
sandwich_type_1_applicable_for varchar 10 √ 'BOTH'::character varying
sandwich_type_1_applicable bool 1 √ false

Enables Sandwich Type 1 detection
Type: Boolean
Pattern: Leave between two off-days (Holiday/Weekoff)
Purpose: Prevents leave abuse around weekends/holidays.
sandwich_type_1_continue bool 1 √ true
sandwich_type_1_minimum_side_1 float8 17,17 √ 0.5

Minimum leave required on first side
Type: Double (default: 0.5)
Usage: Controls partial day restrictions in sandwich scenarios.
sandwich_type_1_minimum_side_2 float8 17,17 √ 0.5
sandwich_type_1_minimum_off_day_count float8 17,17 √ 1

Minimum off-days required for sandwich pattern
Type: Double (default: 1)
Validation: Ensures genuine sandwich pattern detection.
sandwich_type_1_minimum_leave_count float8 17,17 √ 1
sandwich_type_2_applicable_for varchar 10 √ 'HOLIDAY'::character varying
sandwich_type_2_applicable bool 1 √ false
sandwich_type_2_continue bool 1 √ true
sandwich_type_2_minimum_side_1 float8 17,17 √ 0.5
sandwich_type_2_minimum_side_2 float8 17,17 √ 0.5
sandwich_type_2_minimum_off_day_count float8 17,17 √ 1
sandwich_type_2_minimum_leave_count float8 17,17 √ 1
sandwich_type_3_applicable_for varchar 10 √ 'BOTH'::character varying
sandwich_type_3_applicable bool 1 √ false
sandwich_type_3_minimum_off_day_count float8 17,17 √ 2
sandwich_type_3_minimum_leave_count float8 17,17 √ 1
sandwich_type_4_applicable_for varchar 10 √ 'HOLIDAY'::character varying
sandwich_type_4_applicable bool 1 √ false
sandwich_type_4_minimum_off_day_count float8 17,17 √ 2
sandwich_type_4_minimum_leave_count float8 17,17 √ 1
parental_eligibility_days_type varchar 10 √ null

Eligibility days calculation basis
Values: WORKING, CALENDAR
WORKING: Only working days count
CALENDAR: All calendar days count.

Indexes

Constraint Name Type Sort Column(s)
pk_tbl_leavecodepolicyconfig_id Primary key Asc id
ux_tbl_leavecodepolicyconfig_leavecodepolicyname_leavecodeid Must be unique Asc/Asc leavecodepolicyname + leavecodeid

Relationships

Close relationships within degrees of separation