tbl_employeestructure

7 rows


Description

**Module:**Employee Master Data / Organization Structure
Purpose: Stores the detailed organizational and structural data for an employee, effective from a specific date.
Data: Each row defines an employee’s designation, department, branch, reporting lines, and pay grade at a point in time.
Process Usage:
- A new historical record (row) is created for any organizational change (e.g., promotion, transfer, change of reporting manager).
- Used to retrieve the current and historical reporting/structural details of an employee.
Key Points:
- History Tracking: The erunning column manages the effective-dating: only one record per employee should have erunning = true (the current structure).
- Highly critical for payroll, approval workflows, and reporting structures.
Business Impact: Defines the employee’s place in the company, impacts security, and drives complex business logic.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
estructureid bigserial 19 √ nextval('tbl_employeestructure_estructureid_seq'::regclass)
tbl_teamtransfermembers.fromposition fk_tbl_teamtransfermembers_fromposition R
tbl_transferrequisitionmaster.fromposition fk_tbl_transferrequisitionmaster_fromposition R

Primary key
Format: Long (auto-increment)
Used as: Unique identifier for a single historical structural record.
employeeid varchar 500 null
tbl_employee.employeeid fk_employee R

FK: public.tbl_employee.employeeid
Meaning: The identifier of the employee whose structure is being defined.
stateid int4 10 √ null

FK: public.tbl_state.id (or similar)
Meaning: The state/region ID associated with the employee’s work location or jurisdiction.
branchtype int4 10 √ null

The type of branch (e.g., Head Office, Regional Office, Site Office).
branchid int8 19 √ null
tbl_branchmaster.branchid Implied Constraint R

FK: public.tbl_branch.id (or similar)
Meaning: The primary physical Branch/Location where the employee is based.
categoryid int8 19 √ null
tbl_categorymaster.categoryid Implied Constraint R

FK: public.tbl_category.id (or similar)
Meaning: Employee Job Category (e.g., Executive, Staff, Contract).
subcategoryid int8 19 √ null
tbl_subcategorymaster.subcategoryid Implied Constraint R

FK: public.tbl_subcategory.id (or similar)
Meaning: Sub-category within the primary Job Category.
departmentid int8 19 √ null

FK: public.tbl_department.id
Meaning: Primary Department ID (likely the legacy field).
designationid int8 19 √ null

FK: public.tbl_designation.id
Meaning: The Job Title/Designation held by the employee.
paycaderid int4 10 √ null

FK: public.tbl_paycader.id (or similar)
Meaning: The Grade or Band/Level used for compensation purposes.
startdate timestamp 29,6 √ null

Java Field: effectiveDate
Meaning: The date from which this structural record becomes active.
Usage: Determines which structural definition is current or applies for a given historical period.
erunning bool 1 √ true

Boolean flag indicating if this is the currently active organizational structure for the employee.
Note: The application uses @Where(clause = "erunning = true") to filter for the current record.
reportingperson varchar 50 √ null

FK: public.tbl_employee.employeeid
Meaning: The ID of the employee’s Primary Reporting Manager (M1).
reportingperson1 varchar 50 √ null

FK: public.tbl_employee.employeeid
Meaning: The ID of the employee’s Secondary Reporting Manager (M2) or dotted-line manager.
subdeptid int8 19 √ null

FK: public.tbl_subdepartment.id
Meaning: Primary Sub-Department ID (likely the legacy field).
entityid int4 10 √ null

FK: public.tbl_entity.id (or similar)
Meaning: The Legal Entity or Company under which the employee is hired.
sbuid int4 10 √ null
tbl_companymaster.companyid fk_employeestru_sbu R

FK: public.tbl_sbu.id (or similar)
Meaning: The Strategic Business Unit (SBU) the employee belongs to.
newdepartmentid int4 10 √ null

FK: public.tbl_department.id
Meaning: The current or updated Department ID (used in newer implementations).
newsubdepartmentid int4 10 √ null

FK: public.tbl_subdepartment.id
Meaning: The current or updated Sub-Department ID 1.
newsubdepartment1id int4 10 √ null

FK: public.tbl_subdepartment.id
Meaning: The current or updated Sub-Department ID 2 (Multi-level hierarchy).
newsubdepartment2id int4 10 √ null

FK: public.tbl_subdepartment.id
Meaning: The current or updated Sub-Department ID 3.
newsubdepartment3id int4 10 √ null

FK: public.tbl_subdepartment.id
Meaning: The current or updated Sub-Department ID 4.
newsubdepartment4id int4 10 √ null

FK: public.tbl_subdepartment.id
Meaning: The current or updated Sub-Department ID 5.
bendid int4 10 √ null

FK: public.tbl_bend.id (or similar)
Meaning: Employee compensation Band ID.
levelid int4 10 √ null

FK: public.tbl_level.id (or similar)
Meaning: Employee organizational Level ID.
modifiedon timestamp 29,6 √ null

Timestamp of when this specific structural record was last modified.
modifiedby varchar 50 √ null
updatedreason varchar 200 √ null

Text description detailing the reason for the change that created this new structural record (e.g., “Annual Promotion”, “Departmental Transfer”).
createddate timestamp 29,6 √ null
createdby varchar 200 √ null
modifieddate timestamp 29,6 √ null
eventlabel varchar 200 √ null

Label/source of the event that triggered the structural change.
Usage: Helps track if the change came from a Promotion screen, Transfer screen, or API update.
compliancebranchid int8 19 √ null

FK: public.tbl_branch.id (or similar)
Meaning: A secondary branch ID, specifically used for compliance or legal purposes.

Indexes

Constraint Name Type Sort Column(s)
tbl_employeestructure_pk_tbl_employeestructure Primary key Asc estructureid
tbl_employeestructure_idx_employee_tbl_employeestructure Performance Asc employeeid

Relationships

Close relationships within degrees of separation