Jobs Table in Oracle Fusion HCM – A Complete Practical Guide
In Oracle Fusion HCM, the Jobs Table is one of the most critical backend components used to store job-related data across the enterprise. Understanding the Jobs Table in Oracle Fusion HCM is essential for consultants working on Core HR, reporting, integrations, and data migration activities.
From a real implementation perspective, many issues in reporting, HDL loads, and OTBI arise simply because consultants do not clearly understand how job data is stored and linked internally. This guide breaks down everything you need to know—from concept to technical usage.
What is Jobs Table in Oracle Fusion HCM?
The Jobs Table stores structured information about jobs defined in the system, such as job name, job code, effective dates, and related attributes.
In Oracle Fusion HCM, job data is primarily stored in:
- PER_JOBS_F (Main Jobs Table)
- PER_JOBS_F_TL (Translation Layer Table)
Key Concept
- PER_JOBS_F → Stores base job data (effective dated)
- PER_JOBS_F_TL → Stores translated job names (multi-language support)
This separation is critical for global implementations where organizations operate in multiple countries.
Why Jobs Table is Important in Oracle Fusion
From an implementation standpoint, jobs are used across multiple modules:
- Workforce Structures
- Assignments
- Security Profiles
- Compensation
- Reporting (OTBI / BI Publisher)
Practical Insight
In one project, a client faced reporting inconsistencies because job names were directly fetched from PER_JOBS_F instead of PER_JOBS_F_TL. This resulted in missing translations for non-English users.
Key Columns in PER_JOBS_F
Below is a simplified view of important columns:
| Column Name | Description |
|---|---|
| JOB_ID | Unique identifier of the job |
| BUSINESS_GROUP_ID | Enterprise identifier |
| JOB_CODE | Short code for the job |
| DATE_FROM | Start date |
| DATE_TO | End date |
| ACTIVE_STATUS | Indicates if job is active |
| SET_ID | Reference data set |
Consultant Tip
Always filter using:
to get current active job records.
Key Columns in PER_JOBS_F_TL
| Column Name | Description |
|---|---|
| JOB_ID | Foreign key to PER_JOBS_F |
| LANGUAGE | Language code |
| NAME | Job Name |
| SOURCE_LANG | Source language |
Example Query
pjt.NAME
FROM PER_JOBS_F pj,
PER_JOBS_F_TL pjt
WHERE pj.JOB_ID = pjt.JOB_ID
AND pjt.LANGUAGE = ‘US’
AND SYSDATE BETWEEN pj.DATE_FROM AND pj.DATE_TO;
Real-World Business Use Cases
1. Workforce Reporting
HR teams generate reports showing:
- Job distribution
- Headcount per job
- Salary benchmarking
➡ Jobs Table is directly used in OTBI and BI Publisher reports.
2. HDL Data Migration
During implementation:
- Jobs are loaded using HDL
- Data is validated against PER_JOBS_F
Common Issue: Duplicate job codes causing load failures.
3. Integration with Payroll / External Systems
Jobs data is shared with:
- Payroll systems
- Identity management tools
➡ PER_JOBS_F is used in OIC integrations for outbound interfaces.
Configuration Overview Before Jobs Exist
Before jobs are created, these setups must be completed:
- Enterprise Structure
- Business Units
- Legal Entities
- Reference Data Sets
Navigation
Navigator → Setup and Maintenance → Workforce Structures
Step-by-Step Configuration of Jobs in Oracle Fusion
Step 1 – Navigate to Job Setup
Navigator → My Client Groups → Workforce Structures → Jobs
Step 2 – Create Job
Enter the following:
- Name: Software Engineer
- Code: SE001
- Effective Start Date: 01-Jan-2025
- Set: Common Set
Step 3 – Configure Additional Info
Optional fields:
- Job Family
- Job Function
- FLSA Status
Step 4 – Save Configuration
Click Save and Close
Backend Impact
Once saved:
- Record inserted into PER_JOBS_F
- Translated name stored in PER_JOBS_F_TL
Architecture / Data Flow of Jobs Table
From a technical perspective:
- User creates job in UI
- Data stored in PER_JOBS_F
- Name stored in PER_JOBS_F_TL
- Assignments reference JOB_ID
- Reports fetch from both tables
Key Relationship
- PER_ALL_ASSIGNMENTS_M → JOB_ID → PER_JOBS_F
Testing the Jobs Setup
Test Scenario
Create an employee and assign a job:
Navigator → New Person → Hire an Employee
- Job: Software Engineer
Validation Query
pj.JOB_CODE
FROM PER_ALL_ASSIGNMENTS_M paam,
PER_JOBS_F pj
WHERE paam.JOB_ID = pj.JOB_ID;
Expected Result
- Job assigned correctly
- Data visible in reports
Common Implementation Challenges
1. Effective Dating Issues
Problem:
- Duplicate records due to incorrect dates
Solution:
- Maintain proper date ranges
2. Missing Job Names in Reports
Problem:
- Using only PER_JOBS_F
Solution:
- Always join with PER_JOBS_F_TL
3. Data Security Issues
Problem:
- Jobs not visible in certain roles
Solution:
- Check data roles and security profiles
4. HDL Load Errors
Problem:
- Invalid job codes
Solution:
- Validate data before loading
Best Practices for Working with Jobs Table
1. Always Use TL Table for Names
Never fetch job names from base table.
2. Use Effective Date Filters
3. Avoid Hardcoding Job IDs
Always use JOB_CODE or lookups.
4. Maintain Clean Job Structure
- No duplicate codes
- Use meaningful naming conventions
5. Align with Job Families
Improves reporting and analytics.
Expert Consultant Tips
- During integrations, expose both JOB_CODE and JOB_NAME
- For global clients, always handle multi-language data
- Validate job data before payroll runs
- Use OTBI subject area: Workforce Management – Worker Assignment Real Time
Frequently Asked Interview Questions
1. What is PER_JOBS_F?
It is the main table storing job data in Oracle Fusion HCM.
2. What is PER_JOBS_F_TL?
It stores translated job names for multiple languages.
3. Why is TL table required?
To support global implementations with multi-language support.
4. What is JOB_ID?
Primary key linking jobs to assignments.
5. How do you fetch job name?
By joining PER_JOBS_F with PER_JOBS_F_TL.
6. What is effective dating?
Tracking historical changes using DATE_FROM and DATE_TO.
7. Which table links job to employee?
PER_ALL_ASSIGNMENTS_M
8. What causes duplicate jobs?
Improper HDL loads or manual entries.
9. How do you filter active jobs?
Using SYSDATE between date range.
10. What is SET_ID?
Defines reference data sharing across business units.
11. How are jobs used in reporting?
Via OTBI and BI Publisher.
12. What is job code?
Unique identifier for job.
13. Can job names be multilingual?
Yes, using TL table.
14. How to debug missing jobs in reports?
Check joins and security roles.
15. What is best practice for job creation?
Use standardized naming and avoid duplicates.
Real Implementation Scenario
In a global manufacturing project:
- Jobs were created in English
- Users in France saw blank job names
Root Cause:
- Reports used PER_JOBS_F instead of TL table
Fix:
- Updated queries to include PER_JOBS_F_TL
FAQs
1. Can we directly update PER_JOBS_F?
No, always use UI, HDL, or APIs.
2. Why are job names missing in BI reports?
Because TL table is not joined.
3. How to load jobs using HDL?
Use Job.dat file with proper effective dates.
Summary
The Jobs Table in Oracle Fusion HCM is not just a backend structure—it is the foundation for workforce design, reporting, and integrations.
Understanding:
- PER_JOBS_F
- PER_JOBS_F_TL
- Effective dating
- Table relationships
is essential for any consultant working on Oracle Fusion HCM.
For deeper reference, always check the official Oracle documentation: