Let me give you a comprehensive solution covering all three critical areas:
1. HDL File Structure for Assignments:
Your HDL file must follow this structure for assignment records:
METADATA|Assignment|PersonNumber|EffectiveStartDate|EffectiveEndDate|AssignmentNumber|AssignmentStatus
MERGE|Assignment|EMP001|2025-01-01|4712-12-31|E001-A1|ACTIVE_PROCESS
Key fields for assignment status:
- AssignmentStatus: Must use Oracle standard lookup values
- AssignmentStatusTypeCode: Usually derived automatically
- EffectiveStartDate: When status becomes effective
- ActionCode: For status changes (use action codes like HIRE, RETURN, SUSPEND)
2. Lookup Value Mapping Strategy:
Create a comprehensive mapping table that handles all your legacy status codes:
CREATE TABLE assignment_status_mapping (
legacy_code VARCHAR2(10),
oracle_lookup_value VARCHAR2(30),
requires_leave_check VARCHAR2(1),
description VARCHAR2(200)
);
INSERT INTO assignment_status_mapping VALUES
('ACT', 'ACTIVE_PROCESS', 'Y', 'Active on payroll'),
('SUS', 'SUSPENDED', 'N', 'Suspended from work'),
('TRM', 'INACTIVE', 'N', 'Terminated'),
('LOA', 'ACTIVE_NO_PROCESS', 'N', 'Leave of absence');
Use this mapping in your HDL extraction query:
SELECT
e.employee_number,
CASE
WHEN e.status = 'ACT' AND e.on_leave = 'Y'
THEN 'ACTIVE_NO_PROCESS'
WHEN e.status = 'ACT' AND e.on_leave = 'N'
THEN 'ACTIVE_PROCESS'
ELSE m.oracle_lookup_value
END as assignment_status
FROM legacy_employees e
JOIN assignment_status_mapping m
ON e.status = m.legacy_code
3. Assignment Status Configuration Requirements:
Before loading HDL data, verify these Oracle configurations:
a) Review Available Lookup Values:
Setup and Maintenance > Manage Common Lookups > ASG_STATUS
Standard Oracle values you’ll use:
- ACTIVE_PROCESS: Employee active and on payroll
- ACTIVE_NO_PROCESS: Employee active but not on payroll (unpaid leave)
- SUSPENDED: Temporarily suspended (may or may not be paid)
- INACTIVE: Terminated or separated
b) Understand Payroll Processing Impact:
- ACTIVE_PROCESS: Included in payroll runs automatically
- ACTIVE_NO_PROCESS: Excluded from payroll runs
- SUSPENDED: Depends on suspend reason and configuration
- INACTIVE: Always excluded from payroll
c) Configure Action Reasons (if needed):
Setup and Maintenance > Manage Action Reasons
Link action reasons to status changes for audit trail
Critical Validation Steps:
-
Test Lookup Values:
Before full migration, validate all your mapped values exist:
- Run a distinct query on your transformed status values
- Compare against ASG_STATUS lookup in Oracle
- Ensure no unmapped codes remain
-
Handle Status History:
If migrating historical status changes, include multiple HDL records per assignment:
- One record per status change with appropriate EffectiveStartDate
- Use EffectiveEndDate to close previous status periods
- Ensure dates don’t overlap
-
Validate Conditional Logic:
Test your conditional mapping for edge cases:
- Active employees on paid leave (ACTIVE_PROCESS)
- Active employees on unpaid leave (ACTIVE_NO_PROCESS)
- Suspended employees returning to work (ACTIVE_PROCESS with action code)
Common Lookup Types That Need Mapping:
Beyond assignment status, watch for these lookup types in payroll HDL:
- ASSIGNMENT_CATEGORY: Regular, Contingent Worker, etc.
- EMP_CAT: Employee Category codes
- PER_TYPE: Person type (Employee, Contingent Worker, Pending Worker)
- WORKER_TYPE: Worker type classification
- PAY_RATE_TYPE: Salary, Hourly, etc.
HDL Load Best Practices:
- Split your HDL file into batches of 1,000-2,000 assignments
- Load assignments before payroll elements
- Use MERGE operation for existing assignments, INSERT for new
- Review the HDL error log file for any rejected records
- Run validation reports post-load to verify status distribution
After implementing this mapping strategy and validating your lookup values, your HDL loads should complete successfully. The key is transforming legacy codes to Oracle standard values in your ETL process, ensuring the HDL file contains only valid lookup values that Oracle recognizes.