tbl_leavebalancetransactions

-1 rows


Description

Module: HRMS Leave Management
Purpose: Serves as a complete audit ledger for all leave balance changes including allocations, utilizations, adjustments, and system operations.
Data: Stores every transaction that affects employee leave balances with before/after snapshots and detailed context.
Process Usage:
- Provides granular tracking of all leave balance movements for audit and reconciliation.
- Supports balance reconstruction at any point in time using transaction history.
- Enables detailed reporting on leave utilization patterns and policy enforcement.
Key Points:
- Acts as an immutable ledger following accounting principles (credit/debit entries).
- Captures complete context including policy references and business reasons.
Business Impact: Essential for financial auditing, compliance reporting, dispute resolution, and system transparency.

Columns

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

Primary key
Format: BigSerial (auto-increment)
Used as: Unique identifier for each transaction record.
leavecodeid int8 19 √ null
tbl_leavecodemaster.leavecodeid fk_tbl_leavecodetobalancetransaction R

FK: public.tbl_leavecodemaster.leavecodeid
Meaning: Type of leave being transacted (PL, CL, SL, etc.).
Usage: Identifies which specific leave balance is modified.
entityid int8 19 √ null

Reference to the source entity that triggered this transaction
Format: Long
References: Varies by transactionType
LEAVE: tbl_leaveapplicationheader.id
ALLOTMENT/LAPSE/ENCASH: tbl_leavecodepolicyconfig.id
MANUAL: User session or import batch
ATTENDANCE_PENALTY: tbl_dailyattendance.id
Usage: Enables tracing transactions back to their source documents.
leavecode varchar 100 √ null

Leave type name for reporting and display
Format: String
Examples: “PRF. LEAVE”, “Causal Leave”, “Sick leave”
Usage: Human-readable identifier for reports and audit trails.
employeeid varchar 500 √ null
tbl_employee.employeeid Implied Constraint R

FK: public.tbl_employee.employeeid
Meaning: Employee whose leave balance is affected by this transaction.
Usage: Links transaction to specific employee record.
transactiontype varchar 500 √ null

Category of transaction indicating the business operation
Format: String
Values: “ALLOTMENT”, “LEAVE”, “LAPSE”, “MANUAL”, “ATTENDANCE_PENALTY”, “ENCASH”, “ENCASH_TENURE”, “LAPSE_TENURE”
ALLOTMENT: Monthly/periodic leave allocations from policies
LEAVE: Leave utilization from approved applications
LAPSE: Year-end balance expirations
MANUAL: HR-administered adjustments
Usage: Classifies transactions for reporting and business intelligence.
credit numeric 0 √ null

Amount added to leave balance (positive movement)
Format: Float
Unit: Days
Usage: Represents leave allocations, manual additions, or balance restorations.
Examples: 21.0 (monthly allocation), 2.0 (manual adjustment), 0.5 (partial day credit)
debit numeric 0 √ null

Amount deducted from leave balance (negative movement)
Format: Float
Unit: Days
Usage: Represents leave utilization, lapses, penalties, or manual deductions.
Examples: 16.0 (year-end lapse), 1.0 (leave application), 0.5 (penalty deduction)
remarks varchar 2000 √ null

Business context and reason for the transaction
Format: String
Examples: “Added due to leave”, “LAPSE”, “ALLOTMENT”, “Manual balance adjustment”
Usage: Provides human-readable explanation for audit and troubleshooting purposes.
month int8 19 √ null

Calendar month for transaction period context
Format: Integer (1-12)
Usage: Identifies the specific month when transaction occurred or applies to.
year int8 19 √ null

Calendar year for transaction period context
Format: Integer (4-digit year)
Usage: Groups transactions by year for reporting and period-based calculations.
status varchar 100 √ null

Current lifecycle status of the transaction
Format: Enum
Values: “DELETED” (typically null for active transactions)
DELETED: Transaction has been voided/reversed
NULL: Transaction is active and affects current balances
Usage: Supports transaction correction and audit trail maintenance.
createdby varchar 50 √ null
createddate timestamp 29,6 √ null
modifiedby varchar 50 √ null
modifieddate timestamp 29,6 √ null
before_balance numeric 0 √ null

Leave balance BEFORE this transaction was applied
Format: Float
Unit: Days
Audit Purpose: Enables balance reconstruction and validates transaction integrity.
Calculation: after_balance = before_balance + credit - debit
after_balance numeric 0 √ null

Leave balance AFTER this transaction was applied
Format: Float
Unit: Days
Audit Purpose: Provides snapshot of new balance state after transaction processing.
Validation: Used to verify transaction arithmetic and system consistency

Indexes

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

Relationships

Close relationships within degrees of separation