Position_Position

Purpose

The Position.Position table stores comprehensive information about employee positions, including job details, department assignments, compensation information, and position status. This table is essential for organizational structure management, position tracking, and compensation analysis.

Table Structure

Field Name	Data Type	Nullable	Description	Source Table	Source Field	Transformation Notes
PositionID	INT	NO	Primary key, unique identifier	POSITIONS	POSITION_ID	Direct mapping
EmployeeID	INT	NO	Foreign key to Core.Employee	POSITIONS	EMPL_ID	Direct mapping
JobCode	VARCHAR(20)	NO	Job classification code	POSITIONS	JOB_CODE	Direct mapping
PositionTitle	VARCHAR(100)	NO	Full position title	POSITIONS	POSITION_TITLE	Direct mapping
ShortTitle	VARCHAR(50)	YES	Abbreviated position title	POSITIONS	SHORT_TITLE	Direct mapping
DepartmentCode	VARCHAR(20)	NO	Department assignment	POSITIONS	DEPT_CODE	Direct mapping
DepartmentName	VARCHAR(100)	NO	Department name	DEPARTMENTS	DEPT_NAME	Direct mapping
DivisionCode	VARCHAR(20)	YES	Division assignment	POSITIONS	DIV_CODE	Direct mapping
DivisionName	VARCHAR(100)	YES	Division name	DIVISIONS	DIV_NAME	Direct mapping
LocationCode	VARCHAR(20)	NO	Work location code	POSITIONS	LOC_CODE	Direct mapping
LocationName	VARCHAR(100)	NO	Work location name	LOCATIONS	LOC_NAME	Direct mapping
PositionType	VARCHAR(20)	NO	Type of position	POSITIONS	POS_TYPE	Mapped from codes
FLSAStatus	VARCHAR(10)	NO	FLSA classification	POSITIONS	FLSA_STATUS	Mapped from codes
ExemptStatus	BIT	NO	Exempt status indicator	POSITIONS	EXEMPT_FLAG	Derived
PayGrade	VARCHAR(10)	YES	Pay grade level	POSITIONS	PAY_GRADE	Direct mapping
BaseSalary	DECIMAL(12,2)	YES	Base salary amount	POSITIONS	BASE_SALARY	Direct mapping
EffectiveDate	DATE	NO	Position effective date	POSITIONS	EFFECTIVE_DATE	Direct mapping
EndDate	DATE	YES	Position end date	POSITIONS	END_DATE	Direct mapping
IsActive	BIT	NO	Active status indicator	POSITIONS	IS_ACTIVE	Derived
ServiceYears	INT	YES	Years of service	POSITIONS	SERVICE_YEARS	Calculated
AffiliatedYears	INT	YES	Years of affiliation	POSITIONS	AFFILIATED_YEARS	Calculated
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: POSITIONS Table

Contains position information
Updated when position details change
Key fields mapped directly to Position.Position

Secondary Sources:

DEPARTMENTS Table
- Department information
- Used for department validation
- Updated when departments change
DIVISIONS Table
- Division information
- Used for division validation
- Updated when divisions change
LOCATIONS Table
- Location information
- Used for location validation
- Updated when locations change

Data Transformation Rules

Position Type Mapping

Source Code -> Target Type
'FT' -> 'Full-Time'
'PT' -> 'Part-Time'
'TEMP' -> 'Temporary'
'CONTRACT' -> 'Contract'

FLSA Status Mapping

Source Code -> Target Status
'EX' -> 'Exempt'
'NE' -> 'Non-Exempt'
'SE' -> 'Special Exempt'

Exempt Status Derivation

ExemptStatus = CASE
  WHEN FLSAStatus = 'Exempt' THEN 1
  ELSE 0
END

Service Years Calculation

ServiceYears = DATEDIFF(YEAR, EffectiveDate, GETDATE())

Active Status Derivation

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

Data Quality Rules

Required Fields
- PositionID
- EmployeeID
- JobCode
- PositionTitle
- DepartmentCode
- DepartmentName
- LocationCode
- LocationName
- PositionType
- FLSAStatus
- ExemptStatus
- EffectiveDate
- IsActive
Validation Rules
- JobCode must be valid
- DepartmentCode must be valid
- LocationCode must be valid
- EffectiveDate must be valid
- EndDate must be after EffectiveDate if present
- BaseSalary must be non-negative if present
Business Rules
- Only one active position per employee
- Position must have valid department
- Position must have valid location
- FLSA status must match exempt status
- Service years must be non-negative

Usage Notes

Reporting Considerations
- Use PositionType for position analysis
- Consider FLSAStatus for compliance reporting
- Track position changes over time
- Monitor department assignments
Performance Considerations
- Indexed on PositionID, EmployeeID, and DepartmentCode
- Consider filtering on IsActive for current positions
- Use CreatedDate/ModifiedDate for incremental loads
Integration Points
- Links to Core.Employee via EmployeeID
- Links to Position.Supervisor via PositionID
- Used in organizational structure views
- Source for position management

Maintenance

Refresh Schedule
- Daily updates for position changes
- Full refresh monthly
- Emergency updates for corrections
Archive Strategy
- Historical positions maintained in table
- Use EndDate for historical filtering
- Position history tracked for compliance
Monitoring
- Track position changes by employee
- Monitor department assignments
- Alert on validation rule violations
- Track service year calculations

Position.Position Table Documentation