tbl_employeepromotion

-1 rows


Description

Module: HRMS Employee Lifecycle & Compensation
Purpose: Records the details of every promotion, vertical movement, or band/level change approved for an employee.
Data: Stores the effective date, new position details, and associated metadata for each promotion event.
Process Usage:
- Used to update the employee’s active designation, pay cadre, band, and level in the core employee master table.
- Provides a historical log for career progression and salary review/audit processes.
Key Points:
- Essential for tracking employee growth, managing compensation changes, and calculating tenure in position.
- Includes a flag to confirm if the promotion has been fully implemented across all dependent systems.
Business Impact: Ensures accurate reflection of organizational hierarchy and compensation structure for each employee.

Columns

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

Primary key
Format: Integer (auto-increment)
Used as: Unique identifier for a single promotion record.
employeeid varchar 50 √ null

FK: public.tbl_employee.employeeid
Meaning: The ID of the employee who received the promotion.
Format: Integer/Varchar.
designationid int8 19 √ null

FK: Reference to a designation master table
Meaning: The new designation ID granted to the employee upon promotion.
paycadreid int8 19 √ null

FK: Reference to a pay cadre master table
Meaning: The new pay cadre ID assigned to the employee upon promotion.
promotiondate timestamp 29,6 √ null

Meaning: The official effective date of the promotion.
Format: Date.
description varchar 500 √ null

Detailed remarks or justification for the promotion.
Format: Text.
createdby varchar 50 √ null

FK: public.tbl_userlogin.id or public.tbl_employee.employeeid
Meaning: The ID of the user who created this promotion record.
createdon timestamp 29,6 √ null

Legacy timestamp of when the promotion record was initially created.
Format: Timestamp.
modifiedby varchar 50 √ null

FK: public.tbl_userlogin.id or public.tbl_employee.employeeid
Meaning: The ID of the last user who modified the promotion record.
modifiedon timestamp 29,6 √ null

Legacy timestamp of the last modification to the promotion record.
Format: Timestamp.
com1 varchar 50 √ null

Custom field 1 (General Purpose) - Reserved for specific, non-standard business data.
Format: Varchar/Text.
com2 varchar 50 √ null

Custom field 2 (General Purpose).
Format: Varchar/Text.
com3 varchar 50 √ null

Custom field 3 (General Purpose).
Format: Varchar/Text.
com4 varchar 50 √ null

Custom field 4 (General Purpose).
Format: Varchar/Text.
com5 numeric 0 √ null

Custom field 5 (General Purpose).
Format: Varchar/Text.
com6 numeric 0 √ null

Custom field 6 (General Purpose).
Format: Varchar/Text.
com7 int8 19 √ null

Custom field 7 (General Purpose).
Format: Varchar/Text.
com8 int8 19 √ null

Custom field 8 (General Purpose).
Format: Varchar/Text.
com9 bool 1 √ null

Custom field 9 (General Purpose).
Format: Varchar/Text.
com10 bool 1 √ null

Custom field 10 (General Purpose).
Format: Varchar/Text.
com11 timestamp 29,6 √ null

Custom field 11 (General Purpose).
Format: Varchar/Text.
com12 timestamp 29,6 √ null

Custom field 12 (General Purpose).
Format: Varchar/Text.
bandid int4 10 √ null

FK: Reference to a band master table
Meaning: The new job band ID assigned to the employee.
levelid int4 10 √ null

FK: Reference to a level master table
Meaning: The new job level ID assigned to the employee.
status varchar 50 √ null

The current lifecycle status of the promotion request (e.g., Pending, Approved, Rejected, Implemented).
Format: Varchar/Text.
isimported bool 1 √ false

Flag indicating if this promotion record was imported from an external system.
Format: Boolean.
is_promotion_effected bool 1 √ null

Flag indicating if the promotion has been fully processed and its changes (salary, designation, etc.) have taken effect in dependent systems.
Format: Boolean.
past_structure_ids text 2147483647 √ null

A JSON array or Text list of the old organizational unit IDs (e.g., department, designation, band) prior to this promotion event.
Format: JSONB/Text.

Indexes

Constraint Name Type Sort Column(s)
tbl_employeepromotion_pk_tbl_employeepromotion Primary key Asc employeepromotionid

Relationships

Close relationships within degrees of separation