Oracle HCM Tables Reference

🔄 Complete Oracle HCM → Payroll Flow

PER_ALL_PEOPLE_F (Core Employee Table)

PER_ALL_PEOPLE_F is a date-effective table that stores core person information in Oracle HCM. Each record is maintained with effective start and end dates, allowing tracking of historical changes.

Important Columns

Joins

• Join with PER_PERSON_NAMES_F using PERSON_ID → to get employee name
• Join with PER_ALL_ASSIGNMENTS_F using PERSON_ID → to get job & department
• Join with PER_EMAIL_ADDRESSES using PRIMARY_EMAIL_ID → email
• Join with PER_PHONES using PRIMARY_PHONE_ID → phone
• Join with PER_NATIONAL_IDENTIFIERS using PRIMARY_NID_ID → national ID

Duplicate Handling (Very Important)

WHERE SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE

Since this is a date-effective table, always apply this condition to avoid duplicate historical records.

Example Query

SELECT 
    papf.person_id,
    papf.person_number,
    papf.creation_date
FROM per_all_people_f papf
WHERE SYSDATE BETWEEN papf.effective_start_date 
                  AND papf.effective_end_date;

PER_PERSONS (Person Core Details)

PER_PERSONS stores basic personal and demographic information of a person. Unlike PER_ALL_PEOPLE_F, this table is NOT date-effective and holds static attributes such as date of birth, blood group, and place of birth.

Important Columns

Important Notes

• PARTY_ID is obsolete and not used in current implementations
• This table is NOT date-effective (no effective dates)
• Use PER_ALL_PEOPLE_F for date-effective reporting

Joins

• Join with PER_ALL_PEOPLE_F using PERSON_ID → core employee info
• Join with PER_PERSON_NAMES_F → to fetch names
• Join with PER_CITIZENSHIPS → nationality
• Join with PER_NATIONAL_IDENTIFIERS → ID details

Example Query

SELECT 
    per.person_id,
    per.date_of_birth,
    per.country_of_birth
FROM per_persons per
WHERE per.date_of_birth IS NOT NULL;

PER_PERSON_NAMES_F (Employee Name Details)

PER_PERSON_NAMES_F is a date-effective table that stores employee names in different formats. It supports multiple name types such as Global and Local names and maintains history using effective start and end dates.

Important Columns

Important Notes

• This is a date-effective table
• Always filter using EFFECTIVE_START_DATE and END_DATE
• Multiple records may exist for same PERSON_ID
• Use DISPLAY_NAME for reporting (recommended)

Joins

• Join with PER_ALL_PEOPLE_F using PERSON_ID → employee data
• Join with PER_ALL_ASSIGNMENTS_F → job and department
• Join with PER_PERSONS → personal details (DOB, etc.)

Duplicate Handling

WHERE SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE

This ensures only the current active name record is returned.

Example Query

SELECT 
    papf.person_number,
    ppnf.display_name,
    ppnf.full_name
FROM per_all_people_f papf
JOIN per_person_names_f ppnf 
    ON papf.person_id = ppnf.person_id
WHERE SYSDATE BETWEEN ppnf.effective_start_date 
                  AND ppnf.effective_end_date;

PER_ALL_ASSIGNMENTS_F (Employee Assignment Table)

PER_ALL_ASSIGNMENTS_F is one of the most important tables in Oracle HCM. It stores assignment-level details such as job, department, position, salary structure, and employment status. Each employee can have multiple assignments, and this table is date-effective, meaning it keeps historical records of assignment changes.

Core Identifiers

Status & Type

Dates & Effective Logic

Job, Department & Organization

Grade & Salary Info

Employment Details

Recruitment & Hiring

Vendor / Contract Info

Additional Important Fields

Audit Columns

Joins

• PER_ALL_PEOPLE_F → PERSON_ID
• PER_PERSON_NAMES_F → PERSON_ID
• PER_JOBS → JOB_ID
• PER_DEPARTMENTS → ORGANIZATION_ID
• HR_ALL_POSITIONS → POSITION_ID

Duplicate Handling

WHERE SYSDATE BETWEEN EFFECTIVE_START_DATE 
                  AND EFFECTIVE_END_DATE

Example Query

SELECT 
    papf.person_number,
    ppnf.display_name,
    paaf.assignment_number,
    pj.name job_name
FROM per_all_people_f papf
JOIN per_person_names_f ppnf 
  ON papf.person_id = ppnf.person_id
JOIN per_all_assignments_f paaf 
  ON papf.person_id = paaf.person_id
LEFT JOIN per_jobs pj 
  ON paaf.job_id = pj.job_id
WHERE SYSDATE BETWEEN paaf.effective_start_date 
                  AND paaf.effective_end_date;

PER_JOBS (Job Definition Table)

PER_JOBS stores job definitions used across the organization in Oracle HCM. Each job represents a role (e.g., Software Engineer, HR Manager) and is assigned to employees through PER_ALL_ASSIGNMENTS_F. This table is date-effective, meaning job details can change over time and historical records are maintained.

Core Identifiers

Date Effectivity

Organization & Structure

Job Classification & Flags

Action Tracking

Joins

• PER_ALL_ASSIGNMENTS_F → JOB_ID (to get employee job)
• PER_ALL_PEOPLE_F → via assignments
• PER_JOB_FAMILIES → JOB_FAMILY_ID (if used)

Duplicate Handling

WHERE SYSDATE BETWEEN EFFECTIVE_START_DATE 
                  AND EFFECTIVE_END_DATE

Since this is a date-effective table, always filter active records.

Example Query

SELECT 
    papf.person_number,
    ppnf.display_name,
    pj.name job_name
FROM per_all_people_f papf
JOIN per_person_names_f ppnf 
  ON papf.person_id = ppnf.person_id
JOIN per_all_assignments_f paaf 
  ON papf.person_id = paaf.person_id
JOIN per_jobs pj 
  ON paaf.job_id = pj.job_id
WHERE SYSDATE BETWEEN pj.effective_start_date 
                  AND pj.effective_end_date;

PER_DEPARTMENTS (Department / Organization Table)

PER_DEPARTMENTS stores department-level information in Oracle HCM. Each department is identified by ORGANIZATION_ID and is used to group employees within the organization structure. This table is date-effective, meaning department details can change over time.

Core Identifiers

Date Effectivity

Organization Details

Audit Columns

Joins

• PER_ALL_ASSIGNMENTS_F → ORGANIZATION_ID (to get employee department)
• HR_ORGANIZATION_UNITS → ORGANIZATION_ID (for hierarchy)
• PER_ALL_PEOPLE_F → via assignments

Duplicate Handling

WHERE SYSDATE BETWEEN EFFECTIVE_START_DATE 
                  AND EFFECTIVE_END_DATE

Since this is a date-effective table, always filter active records.

Example Query

SELECT 
    papf.person_number,
    ppnf.display_name,
    pd.name department_name
FROM per_all_people_f papf
JOIN per_person_names_f ppnf 
  ON papf.person_id = ppnf.person_id
JOIN per_all_assignments_f paaf 
  ON papf.person_id = paaf.person_id
LEFT JOIN per_departments pd 
  ON paaf.organization_id = pd.organization_id
WHERE SYSDATE BETWEEN pd.effective_start_date 
                  AND pd.effective_end_date;

PER_ASSIGNMENT_SUPERVISORS_F (Manager Relationship Table)

PER_ASSIGNMENT_SUPERVISORS_F stores the relationship between employees and their managers. It defines reporting hierarchy such as Line Manager, Project Manager, etc. This table is date-effective, meaning manager relationships can change over time.

Core Columns

Date Effectivity

Action Tracking

Joins

• PER_ALL_ASSIGNMENTS_F → ASSIGNMENT_ID
• PER_ALL_PEOPLE_F → PERSON_ID
• PER_PERSON_NAMES_F → for employee name
• PER_ALL_PEOPLE_F (again) → MANAGER_ID for manager name

Duplicate Handling

WHERE SYSDATE BETWEEN EFFECTIVE_START_DATE 
                  AND EFFECTIVE_END_DATE

Example Query

SELECT 
    emp.person_number,
    emp_name.display_name employee_name,
    mgr_name.display_name manager_name
FROM per_assignment_supervisors_f pasf
JOIN per_all_people_f emp 
  ON pasf.person_id = emp.person_id
JOIN per_person_names_f emp_name 
  ON emp.person_id = emp_name.person_id
JOIN per_all_people_f mgr 
  ON pasf.manager_id = mgr.person_id
JOIN per_person_names_f mgr_name 
  ON mgr.person_id = mgr_name.person_id
WHERE pasf.manager_type = 'LINE_MANAGER'
AND SYSDATE BETWEEN pasf.effective_start_date 
                AND pasf.effective_end_date;

PER_ASSIGNMENT_SUPERVISORS_F_ (Audit / History Table)

PER_ASSIGNMENT_SUPERVISORS_F_ is the audit version of the supervisors table. It stores historical changes including updates, inserts, and deletions. This table is mainly used for:

• Audit tracking
• Debugging hierarchy changes
• Tracking historical updates

Core Columns (Same as F Table)

Additional Audit Columns (Only in F_)

Important Notes

• This table contains multiple records for same row due to updates
• Do NOT use for normal reporting
• Use only for audit/debugging

Example Use Case

SELECT *
FROM per_assignment_supervisors_f_
WHERE audit_action_type = 'UPDATE';

PAY_ELEMENT_ENTRIES_F (Payroll Element Entry Table)

PAY_ELEMENT_ENTRIES_F stores element entries assigned to employees. Each row represents a payroll element (e.g., Basic Salary, Allowance, Deduction) assigned to a person. This table is date-effective, meaning element entries can change over time and historical records are maintained.

Core Identifiers

Date Effectivity

Entry Source & Processing

Control & Technical Columns

Audit Columns

Important Notes

• This table stores input data, not calculated payroll results
• Actual calculated values come from PAY_RUN_RESULTS
• Each entry links to element type and input values
• Multiple entries can exist per employee

Joins

• PER_ALL_PEOPLE_F → PERSON_ID
• PER_ALL_ASSIGNMENTS_F → via PERSON_ID
• PAY_ELEMENT_TYPES_F → ELEMENT_TYPE_ID
• PAY_INPUT_VALUES_F → ELEMENT_TYPE_ID
• PAY_RUN_RESULTS → ELEMENT_ENTRY_ID

Duplicate Handling

WHERE SYSDATE BETWEEN EFFECTIVE_START_DATE 
                  AND EFFECTIVE_END_DATE

Example Query

SELECT 
    papf.person_number,
    ppnf.display_name,
    pet.element_name,
    penf.element_entry_id
FROM pay_element_entries_f penf
JOIN per_all_people_f papf 
  ON penf.person_id = papf.person_id
JOIN per_person_names_f ppnf 
  ON papf.person_id = ppnf.person_id
JOIN pay_element_types_tl pet 
  ON penf.element_type_id = pet.element_type_id
WHERE SYSDATE BETWEEN penf.effective_start_date 
                  AND penf.effective_end_date;

PAY_RUN_BALANCES (Payroll Balance Table)

PAY_RUN_BALANCES stores calculated payroll balance values such as Gross Pay, Net Pay, Tax, and other accumulated balances. This table represents aggregated results from payroll processing and is used for reporting, reconciliation, and statutory calculations.

Core Identifiers

Balance Values

Payroll Context

Legal & Tax Context

Element & Processing Details

Additional Context Fields

Other References

Important Notes

• This table stores aggregated balances, not individual element results
• Balances are defined in PAY_DEFINED_BALANCES
• Used heavily in payroll reporting (Net Pay, Gross Pay, Tax)
• Data is generated during payroll run

Joins

• PAY_DEFINED_BALANCES → DEFINED_BALANCE_ID
• PAY_PAYROLL_REL_ACTIONS → PAYROLL_REL_ACTION_ID
• PER_ALL_PEOPLE_F → via payroll relationship
• PAY_RUN_RESULTS → ELEMENT_ENTRY_ID

Example Query

SELECT 
    papf.person_number,
    prb.balance_value,
    prb.effective_date
FROM pay_run_balances prb
JOIN pay_payroll_rel_actions pra 
  ON prb.payroll_rel_action_id = pra.payroll_rel_action_id
JOIN per_all_people_f papf 
  ON pra.payroll_relationship_id = papf.person_id
WHERE prb.balance_value IS NOT NULL;

PAY_ELEMENT_TYPES_TL (Element Translation Table)

PAY_ELEMENT_TYPES_TL stores the translated names and descriptions of payroll elements. It is used to display element names in different languages (US, UK, Arabic, etc.). This table is NOT date-effective but supports multiple rows per element based on language.

Core Columns

System & Audit Columns

Important Notes

• Each ELEMENT_TYPE_ID can have multiple rows (one per language)
• Always filter using LANGUAGE = USERENV('LANG')
• This table provides display names, not configuration logic

Joins

• PAY_ELEMENT_TYPES_F → ELEMENT_TYPE_ID
• PAY_ELEMENT_ENTRIES_F → ELEMENT_TYPE_ID
• PAY_RUN_RESULTS → ELEMENT_TYPE_ID

Best Practice (Important)

WHERE pet_tl.language = USERENV('LANG')

This ensures the correct language is displayed based on user session.

Example Query

SELECT 
    papf.person_number,
    pet_tl.element_name,
    rrv.result_value
FROM pay_run_results prr
JOIN pay_run_result_values rrv 
  ON prr.run_result_id = rrv.run_result_id
JOIN pay_element_types_tl pet_tl 
  ON prr.element_type_id = pet_tl.element_type_id
JOIN per_all_people_f papf 
  ON papf.person_id = prr.element_entry_id
WHERE pet_tl.language = USERENV('LANG');

PAY_ENTRY_USAGES (Element Entry Usage Table)

PAY_ENTRY_USAGES defines how and where an element entry is applied in payroll. It links an element entry to payroll relationship, term, or assignment level. This table helps Oracle determine the scope of element processing.

Core Identifiers

Date Range

Payroll Context

Technical Columns

Audit Columns

Important Notes

• This table defines where an element is applied
• USAGE_LEVEL determines processing scope
• Not always required in basic reports, but important in advanced payroll logic

Usage Level Meaning

Joins

• PAY_ELEMENT_ENTRIES_F → ELEMENT_ENTRY_ID
• PAY_PAYROLL_ASSIGNMENTS → PAYROLL_ASSIGNMENT_ID
• PAY_PAYROLL_REL_ACTIONS → PAYROLL_RELATIONSHIP_ID

Example Query

SELECT 
    penf.element_entry_id,
    peu.usage_level,
    peu.payroll_assignment_id
FROM pay_element_entries_f penf
JOIN pay_entry_usages peu 
  ON penf.element_entry_id = peu.element_entry_id
WHERE peu.usage_level = 'ASSIGNMENT';

PAY_INPUT_VALUES_F (Element Input Values Table)

PAY_INPUT_VALUES_F defines the input values required for payroll elements. Each element can have multiple input values such as Amount, Hours, Rate, etc. This table controls:

• What values users can enter
• Validation rules
• Default values
• Formula-driven calculations

Core Identifiers

Date Effectivity

User Entry & Behavior

Value & Units

Validation & Lookup

Formula Configuration

Advanced Processing

Audit Columns

System Columns

Important Notes

• This table defines what values an element can take
• Each element can have multiple input values
• Used heavily in Fast Formula calculations
• Controls validation, defaults, and UI behavior

Joins

• PAY_ELEMENT_TYPES_F → ELEMENT_TYPE_ID
• PAY_ELEMENT_ENTRIES_F → ELEMENT_TYPE_ID
• PAY_RUN_RESULT_VALUES → INPUT_VALUE_ID

Duplicate Handling

WHERE SYSDATE BETWEEN EFFECTIVE_START_DATE 
                  AND EFFECTIVE_END_DATE

Example Query

SELECT 
    pet.base_element_name,
    piv.base_name,
    piv.uom,
    piv.default_value
FROM pay_input_values_f piv
JOIN pay_element_types_f pet 
  ON piv.element_type_id = pet.element_type_id
WHERE SYSDATE BETWEEN piv.effective_start_date 
                  AND piv.effective_end_date;

PAY_ELEMENT_TYPES_F (Payroll Element Definition Table)

PAY_ELEMENT_TYPES_F defines payroll elements such as Basic Salary, Allowances, Bonuses, and Deductions. It controls:

• Element behavior
• Processing rules
• Formulas and validations
• Payroll execution order

Core Identifiers

Date Effectivity

Element Behavior & Flags

Processing Scope

Classification

Currency & Time

Formula Configuration

Advanced Processing

Technical Columns

Audit Columns

Flexfields (Grouped)

This table includes Descriptive Flexfields (DFF & DDF):

• ATTRIBUTE1 – ATTRIBUTE30
• ATTRIBUTE_NUMBER1 – ATTRIBUTE_NUMBER20
• ATTRIBUTE_DATE1 – ATTRIBUTE_DATE15
• ELEMENT_INFORMATION1 – ELEMENT_INFORMATION30

Joins

• PAY_ELEMENT_ENTRIES_F → ELEMENT_TYPE_ID
• PAY_INPUT_VALUES_F → ELEMENT_TYPE_ID
• PAY_RUN_RESULTS → ELEMENT_TYPE_ID
• PAY_ELEMENT_TYPES_TL → ELEMENT_TYPE_ID (display name)

Duplicate Handling

WHERE SYSDATE BETWEEN EFFECTIVE_START_DATE 
                  AND EFFECTIVE_END_DATE

Example Query

SELECT 
    pet.base_element_name,
    pet.processing_type,
    pet.processing_priority
FROM pay_element_types_f pet
WHERE SYSDATE BETWEEN pet.effective_start_date 
                  AND pet.effective_end_date;

PAY_PAYROLL_ACTIONS (Payroll Process Execution Table)

PAY_PAYROLL_ACTIONS stores details of payroll processes such as payroll runs, quick pay, retro runs, and costing. Each row represents a payroll process execution.

Core Identifiers

Action Details

Dates

Legislation Context

Processing Info

Audit Columns

Important Notes

• Each row represents one payroll process run
• Used to track payroll execution status
• Links to PAY_RUN_RESULTS and PAY_RUN_BALANCES

Joins

• PAY_PAYROLL_REL_ACTIONS → PAYROLL_ACTION_ID
• PAY_RUN_RESULTS → PAYROLL_ACTION_ID
• PER_ALL_PEOPLE_F → via payroll relationship

Example Query

SELECT 
    ppa.payroll_action_id,
    ppa.action_type,
    ppa.action_status,
    ppa.effective_date
FROM pay_payroll_actions ppa
WHERE ppa.action_status = 'C';

PAY_ALL_PAYROLL_ACTIONS_V (Payroll Actions View)

PAY_ALL_PAYROLL_ACTIONS_V is a view built on top of PAY_PAYROLL_ACTIONS that provides additional readable and joined data. It simplifies reporting by combining multiple payroll action details.

Key Columns

Why This View is Used

• Provides cleaner reporting structure
• Reduces need for multiple joins
• Used in OTBI and reporting queries

Important Notes

• This is a view, not a table
• Recommended for reporting instead of base table
• Performance optimized

Example Query

SELECT 
    payroll_action_id,
    action_type,
    action_status,
    effective_date
FROM pay_all_payroll_actions_v
WHERE action_status = 'C';

PAY_PAYROLL_ASSIGNMENTS (Payroll Assignment Mapping Table)

PAY_PAYROLL_ASSIGNMENTS links HR assignments with payroll structures. It acts as a bridge between employee assignment and payroll processing entities. This table is essential for understanding how an employee is mapped into payroll.

Core Identifiers

Employee Context

Date Range

Important Notes

• This table connects HR assignment with payroll structure
• Used internally by payroll engine
• Required for linking payroll results to employees

Joins

• PER_ALL_ASSIGNMENTS_F → HR_ASSIGNMENT_ID
• PER_ALL_PEOPLE_F → PERSON_ID
• PAY_PAYROLL_REL_ACTIONS → PAYROLL_RELATIONSHIP_ID
• PAY_RUN_RESULTS → PAYROLL_ASSIGNMENT_ID

Example Query

SELECT 
    ppa.person_id,
    ppa.payroll_assignment_id,
    ppa.hr_assignment_id,
    ppa.payroll_relationship_id
FROM pay_payroll_assignments ppa;

PAY_RUN_RESULTS (Payroll Calculation Results Table)

PAY_RUN_RESULTS stores calculated results of payroll elements for each payroll run. Each row represents the processed result of an element entry. This table is a core payroll processing table used to store calculation outputs before final balances are derived.

Core Identifiers

Payroll Context

Processing Details

Additional Context

Technical Columns

Important Notes

• This table stores element-level calculated results
• One row per element per payroll run
• Does NOT store final values (see PAY_RUN_RESULT_VALUES)
• Used heavily in payroll calculations and debugging

Joins

• PAY_RUN_RESULT_VALUES → RUN_RESULT_ID
• PAY_ELEMENT_TYPES_F → ELEMENT_TYPE_ID
• PAY_ELEMENT_ENTRIES_F → ELEMENT_ENTRY_ID
• PAY_PAYROLL_REL_ACTIONS → PAYROLL_REL_ACTION_ID

Example Query

SELECT 
    prr.run_result_id,
    prr.element_type_id,
    prr.payroll_assignment_id,
    prr.status
FROM pay_run_results prr
WHERE prr.status = 'C';

PAY_PAYROLL_REL_ACTIONS (Payroll Relationship Actions Table)

PAY_PAYROLL_REL_ACTIONS stores payroll processing details at the employee (payroll relationship) level. Each row represents the execution of a payroll run for a specific employee.

Core Identifiers

Processing Status

Processing Details

Technical Columns

Important Notes

• This table represents employee-level payroll processing
• Each PAYROLL_ACTION_ID will have multiple rows here (one per employee)
• Acts as the main bridge between payroll run and results

Joins

• PAY_PAYROLL_ACTIONS → PAYROLL_ACTION_ID
• PAY_RUN_RESULTS → PAYROLL_REL_ACTION_ID
• PAY_RUN_BALANCES → PAYROLL_REL_ACTION_ID
• PAY_PAYROLL_ASSIGNMENTS → PAYROLL_RELATIONSHIP_ID

Example Query

SELECT 
    pra.payroll_rel_action_id,
    pra.payroll_action_id,
    pra.payroll_relationship_id,
    pra.action_status
FROM pay_payroll_rel_actions pra
WHERE pra.action_status = 'C';

Payroll Processing Flow (End-to-End)

The following flow explains how payroll data moves across Oracle Payroll tables:

1. PAY_PAYROLL_ACTIONS
   → Payroll run created (header level)

2. PAY_PAYROLL_REL_ACTIONS
   → Employee-level processing for that run

3. PAY_PAYROLL_ASSIGNMENTS
   → Maps HR assignment to payroll structure

4. PAY_ELEMENT_ENTRIES_F
   → Input values assigned to employee

5. PAY_INPUT_VALUES_F
   → Defines what values element accepts

6. PAY_RUN_RESULTS
   → Element calculation results (per element)

7. PAY_RUN_RESULT_VALUES
   → Actual calculated values (amount, hours, etc.)

8. PAY_RUN_BALANCES
   → Final aggregated balances (Net Pay, Gross Pay)

Flow Diagram (Simplified)

Payroll Run
   ↓
PAY_PAYROLL_ACTIONS
   ↓
PAY_PAYROLL_REL_ACTIONS
   ↓
PAY_PAYROLL_ASSIGNMENTS
   ↓
PAY_ELEMENT_ENTRIES_F
   ↓
PAY_RUN_RESULTS
   ↓
PAY_RUN_RESULT_VALUES
   ↓
PAY_RUN_BALANCES

Important Understanding

• Payroll runs start at header level and go down to employee level
• Each employee gets processed separately
• Results are stored in multiple stages (results → values → balances)
• This structure allows flexibility, auditing, and recalculation

PAY_SEARCH_ACTIONS_V (Payroll Search View)

PAY_SEARCH_ACTIONS_V is a reporting view that combines payroll action, employee, and assignment data into a single searchable structure. It is primarily used in UI screens and reports to quickly fetch payroll processing details.

Employee Information

Payroll Action Details

Payroll Context

Dates & Periods

Other Details

Important Notes

• This is a view, not a base table
• Combines data from multiple payroll tables
• Used for searching payroll runs and employee processing
• Good for reporting, not for heavy joins

Joins (Internal)

• PAY_PAYROLL_ACTIONS
• PAY_PAYROLL_REL_ACTIONS
• PER_ALL_PEOPLE_F
• PER_PERSON_NAMES_F
• PER_ALL_ASSIGNMENTS_F

Example Query

SELECT 
    person_number,
    full_name,
    payroll_name,
    action_type,
    action_status_code,
    process_date
FROM pay_search_actions_v
WHERE action_status_code = 'C';

PAY_CE_TRANSACTIONS (Payroll Payment Transactions Table)

PAY_CE_TRANSACTIONS stores payroll payment details such as bank transfers, checks, and other payment methods. Each row represents a payment transaction generated after payroll processing.

Core Identifiers

Payment Details

Bank Details

Payment Method

Payroll Context

Audit Columns

Important Notes

• This table stores actual payment transactions
• Generated after payroll run and pre-payment process
• Includes bank, check, and payment method details
• Used for reconciliation and payment tracking

Joins

• PAY_PAYROLL_REL_ACTIONS → PAYROLL_REL_ACTION_ID
• PAY_PAYROLL_ACTIONS → PAYROLL_ACTION_ID
• PER_ALL_PEOPLE_F → via payroll relationship
• PAY_RUN_BALANCES → to verify amounts

Example Query

SELECT 
    pct.payroll_action_id,
    pct.amount,
    pct.currency_code,
    pct.payment_status,
    pct.payment_date
FROM pay_ce_transactions pct
WHERE pct.payment_status = 'PAID';

Complete Payroll to Payment Flow

Payroll Run Created
   ↓
PAY_PAYROLL_ACTIONS
   ↓
Employee Processing
   ↓
PAY_PAYROLL_REL_ACTIONS
   ↓
Element Input & Entries
   ↓
PAY_ELEMENT_ENTRIES_F
   ↓
Calculation Engine
   ↓
PAY_RUN_RESULTS
   ↓
Result Values
   ↓
PAY_RUN_RESULT_VALUES
   ↓
Final Balances
   ↓
PAY_RUN_BALANCES
   ↓
Payment Processing
   ↓
PAY_CE_TRANSACTIONS

This represents the complete lifecycle from payroll calculation to actual payment.

HR_DOCUMENTS_OF_RECORD (Documents of Record Table)

HR_DOCUMENTS_OF_RECORD stores employee-related documents such as passport, visa, certificates, contracts, and other official records. Each row represents a document associated with a person or assignment.

Core Identifiers

Document Details

Related Object Mapping

Publishing & Status

Descriptive Flexfields (DFF)

This table supports Descriptive Flexfields for additional customization:

• DEI_ATTRIBUTE_CATEGORY → Structure definition
• DEI_ATTRIBUTE1 to DEI_ATTRIBUTE40 → Custom text attributes
• DEI_ATTRIBUTE_NUMBER1 to DEI_ATTRIBUTE_NUMBER20 → Numeric attributes
• DEI_ATTRIBUTE_DATE1 to DEI_ATTRIBUTE_DATE15 → Date attributes
• DEI_ATTRIBUTE_TIMESTAMP1 to DEI_ATTRIBUTE_TIMESTAMP5 → Timestamp attributes

Legislative Flexfields

• DEI_INFORMATION_CATEGORY → Legislative structure
• DEI_INFORMATION1 to DEI_INFORMATION40 → Legislative attributes
• DEI_INFORMATION_NUMBER1 to DEI_INFORMATION_NUMBER20 → Numeric legislative attributes
• DEI_INFORMATION_DATE1 to DEI_INFORMATION_DATE15 → Date legislative attributes

Audit Columns

Important Notes

• Stores employee documents such as passport, visa, and certificates
• Supports attachment to person or assignment
• Highly extensible using DFF and legislative fields
• Used in HR self-service and compliance tracking

Joins

• PER_ALL_PEOPLE_F → PERSON_ID
• PER_ALL_ASSIGNMENTS_F → ASSIGNMENT_ID
• HR_DOCUMENT_TYPES_VL → DOCUMENT_TYPE_ID
• HRC_TXN_HEADER / HRC_TXN_DATA → transaction tracking

Example Query

SELECT 
    d.document_name,
    d.document_number,
    d.issued_date,
    d.date_to
FROM hr_documents_of_record d
WHERE d.person_id = :person_id;

HRC_TXN_HEADER (Transaction Header Table)

HRC_TXN_HEADER stores high-level information about transactions initiated in Oracle HCM. It is used to track business operations such as document uploads, approvals, and data changes before they are committed to base tables.

Core Identifiers

Transaction Context

Business Object Mapping

Transaction Data

Audit Columns

Important Notes

• Stores transaction header data before actual table updates
• Used in approval workflows and UI transactions
• XML_DATA_CACHE contains full transaction payload
• Works with HRC_TXN_DATA for transaction status tracking

Joins

• HRC_TXN_DATA → TRANSACTION_ID
• PER_ALL_PEOPLE_F → SUBJECT_ID (if subject = PERSON)
• HR_DOCUMENTS_OF_RECORD → OBJECT_ID (for document transactions)

Example Query

SELECT 
    txn.transaction_id,
    txn.subject,
    txn.object,
    txn.creation_date
FROM hrc_txn_header txn
WHERE txn.subject = 'PERSON';

SQL Logic

DENSE_RANK() OVER (ORDER BY PAPF.PERSON_NUMBER) S_No

TO_CHAR(ppos.last_working_date,'DD-MON-YYYY','nls_date_language=english')

TO_CHAR(hdr.last_update_date,
'DD MONTH YYYY',
'NLS_CALENDAR=''ARABIC HIJRAH'' NLS_DATE_LANGUAGE=ARABIC')

COALESCE((SELECT MAX(fl.meaning)
FROM fnd_lookup_values_tl fl
WHERE fl.lookup_type='NATIONALITY'),' - ')

Duplicate Handling

WHERE SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE