Let me provide a comprehensive solution addressing all three key aspects:
HDL File Format and Validation:
First, verify your HDL file structure follows the correct format. The METADATA section must declare all objects being loaded:
METADATA|Worker|SourceSystemOwner|SourceSystemId
METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|WorkerNumber
METADATA|WorkTerms|SourceSystemOwner|SourceSystemId|AssignmentCategory
Ensure your data rows reference the exact column headers defined in METADATA. Case sensitivity matters.
Lookup Value Mapping:
The core issue is lookup code translation. Extract all unique values from your legacy data for fields that reference lookups (AssignmentCategory, LocationCode, JobCode, etc.). Query Fusion’s lookup tables to identify gaps:
SELECT lookup_type, lookup_code, meaning
FROM FND_LOOKUP_VALUES_VL
WHERE lookup_type IN ('EMP_ASSIGN_CATEGORY', 'LOCATION', 'JOB_CODE')
AND enabled_flag = 'Y';
Create a mapping spreadsheet with three columns: Legacy_Code, Fusion_Code, Notes. For assignment categories, map to standard Fusion values (E, C, P, N). For locations and jobs, either create the lookup values in Fusion first using Manage Common Lookups task, or map to existing similar values.
Modify your HDL extraction query to include mapping logic:
SELECT
CASE legacy_assignment_type
WHEN 'PT_EMP' THEN 'E'
WHEN 'FT_EMP' THEN 'E'
WHEN 'FT_CONTRACTOR' THEN 'C'
ELSE 'E'
END as AssignmentCategory
FROM legacy_employee_table;
Error Log Analysis:
HDL generates detailed error logs in UCM (Content Server). Download the log file and search for patterns:
- “Invalid lookup code” = value doesn’t exist in Fusion
- “Invalid cross-reference” = parent record missing (e.g., Location not loaded yet)
- “Duplicate key” = record already exists or primary key conflict
For your 1,200 failed records, extract the specific error messages and group by error type. This tells you whether it’s purely lookup issues or if there are data quality problems (null required fields, invalid dates, etc.).
Recommended Action Plan:
- Run the SQL query above to get all valid Fusion lookup codes for your target lookup types
- Create mapping logic in your ETL/extraction process - don’t try to fix 1,200 records manually
- For location and job codes that must be custom, use Manage Common Lookups to pre-create them in Fusion before rerunning HDL
- Test with a small batch (50-100 records) that previously failed
- Review the error log to confirm lookup issues are resolved
- Process the full 8,500 records once validation passes
Avoid creating custom lookup values unless absolutely necessary. Standard codes ensure compatibility with Oracle’s seeded functionality, reports, and future updates. If you must create custom values, document them thoroughly and include them in your upgrade testing scope.
The EnableDuplicateKeyMode=Y parameter mentioned earlier helps with reprocessing but won’t solve lookup validation failures - you must address the root cause through proper value mapping.
This draft is based on general Oracle Fusion Cloud knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.