Position_Supervisor

Purpose

The Position.Supervisor table manages reporting relationships between employees and their supervisors. It tracks direct and indirect reporting relationships, supervisor assignments, and organizational hierarchy. This table is essential for organizational structure reporting, management chain analysis, and workflow routing.

Table Structure

Field Name	Data Type	Nullable	Description	Source Table	Source Field	Transformation Notes
SupervisorID	INT	NO	Primary key, unique identifier	SUPERVISOR_RELATIONS	SUPERVISOR_ID	Direct mapping
EmployeeID	INT	NO	Foreign key to Core.Employee	SUPERVISOR_RELATIONS	EMPL_ID	Direct mapping
SupervisorEmployeeID	INT	NO	Foreign key to Core.Employee	SUPERVISOR_RELATIONS	SUPERVISOR_EMPL_ID	Direct mapping
PositionID	INT	NO	Foreign key to Position.Position	SUPERVISOR_RELATIONS	POSITION_ID	Direct mapping
SupervisorPositionID	INT	NO	Foreign key to Position.Position	SUPERVISOR_RELATIONS	SUPERVISOR_POS_ID	Direct mapping
RelationshipType	VARCHAR(20)	NO	Type of supervisory relationship	SUPERVISOR_RELATIONS	RELATIONSHIP_TYPE	Mapped from codes
IsDirect	BIT	NO	Direct supervisor indicator	SUPERVISOR_RELATIONS	IS_DIRECT	Derived
IsInterim	BIT	NO	Interim supervisor indicator	SUPERVISOR_RELATIONS	IS_INTERIM	Direct mapping
EffectiveDate	DATE	NO	Relationship effective date	SUPERVISOR_RELATIONS	EFFECTIVE_DATE	Direct mapping
EndDate	DATE	YES	Relationship end date	SUPERVISOR_RELATIONS	END_DATE	Direct mapping
IsActive	BIT	NO	Active status indicator	SUPERVISOR_RELATIONS	IS_ACTIVE	Derived
CreatedDate	DATETIME	NO	Record creation date	N/A	N/A	System generated
ModifiedDate	DATETIME	NO	Last modification date	N/A	N/A	System generated

Source System Mapping

Primary Source: SUPERVISOR_RELATIONS Table

Contains supervisor relationship information
Updated when reporting relationships change
Key fields mapped directly to Position.Supervisor

Secondary Sources:

POSITIONS Table
- Position information
- Used for position validation
- Updated when positions change
EMPLOYEES Table
- Employee information
- Used for employee validation
- Updated when employee status changes

Data Transformation Rules

Relationship Type Mapping

Source Code -> Target Type
'DIRECT' -> 'Direct'
'MATRIX' -> 'Matrix'
'DOTTED' -> 'Dotted Line'
'FUNCTIONAL' -> 'Functional'

Direct Supervisor Derivation

IsDirect = CASE
  WHEN RelationshipType = 'Direct' THEN 1
  ELSE 0
END

Active Status Derivation

IsActive = CASE
  WHEN EndDate IS NULL OR EndDate > GETDATE() THEN 1
  ELSE 0
END

Data Quality Rules

Required Fields
- SupervisorID
- EmployeeID
- SupervisorEmployeeID
- PositionID
- SupervisorPositionID
- RelationshipType
- IsDirect
- EffectiveDate
- IsActive
Validation Rules
- EmployeeID must be valid
- SupervisorEmployeeID must be valid
- PositionID must be valid
- SupervisorPositionID must be valid
- EffectiveDate must be valid
- EndDate must be after EffectiveDate if present
Business Rules
- Only one direct supervisor per employee
- Supervisor must be an active employee
- Employee cannot be their own supervisor
- Supervisor must have an active position
- Relationship dates must align with position dates

Usage Notes

Reporting Considerations
- Use IsDirect for organizational charts
- Consider RelationshipType for complex reporting structures
- Track supervisor changes over time
- Monitor interim assignments
Performance Considerations
- Indexed on SupervisorID, EmployeeID, and SupervisorEmployeeID
- Consider filtering on IsActive for current relationships
- Use CreatedDate/ModifiedDate for incremental loads
Integration Points
- Links to Core.Employee via EmployeeID and SupervisorEmployeeID
- Links to Position.Position via PositionID and SupervisorPositionID
- Used in organizational hierarchy views
- Source for management chain analysis

Maintenance

Refresh Schedule
- Daily updates for supervisor changes
- Full refresh monthly
- Emergency updates for corrections
Archive Strategy
- Historical relationships maintained in table
- Use EndDate for historical filtering
- Supervisor history tracked for compliance
Monitoring
- Track supervisor changes by employee
- Monitor direct supervisor assignments
- Alert on validation rule violations
- Track interim assignments

Position.Supervisor Table Documentation