Oracle EBS Employee, HR and Projects Tables
This is a lookup for the Oracle E-Business Suite tables behind employees, HR organizations and Oracle Projects. It lists the key columns you join on and a tip for each, so you can write a report query without searching documentation.
| Table | Module | What it holds | Key columns to join on | Query tip |
|---|---|---|---|---|
PER_ALL_PEOPLE_F | HRMS | Person records: employees, contingent workers, applicants. | PERSON_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, EMPLOYEE_NUMBER, FULL_NAME | Date-effective. Filter with TRUNC(SYSDATE) BETWEEN effective dates. |
PER_ALL_ASSIGNMENTS_F | HRMS | Assignments linking a person to organization, job, position and supervisor. | ASSIGNMENT_ID, PERSON_ID, ORGANIZATION_ID, JOB_ID, POSITION_ID, SUPERVISOR_ID, PRIMARY_FLAG | Filter PRIMARY_FLAG = 'Y' and ASSIGNMENT_TYPE = 'E' for current employee primary assignments. |
PER_PERIODS_OF_SERVICE | HRMS | Employment periods with start and termination dates. | PERIOD_OF_SERVICE_ID, PERSON_ID, DATE_START, ACTUAL_TERMINATION_DATE | Use to find terminated employees and rehires. |
PER_JOBS | HRMS | Job definitions. | JOB_ID, BUSINESS_GROUP_ID, NAME | Join from assignments on JOB_ID. |
PER_ALL_POSITIONS | HRMS | Position definitions where position management is used. | POSITION_ID, ORGANIZATION_ID, NAME | Join from assignments on POSITION_ID. |
PER_PERSON_TYPES | HRMS | Person types such as Employee, Ex-employee, Applicant. | PERSON_TYPE_ID, SYSTEM_PERSON_TYPE, USER_PERSON_TYPE | Join from PER_ALL_PEOPLE_F.PERSON_TYPE_ID. |
PER_ADDRESSES | HRMS | Person addresses. | ADDRESS_ID, PERSON_ID, PRIMARY_FLAG, DATE_FROM, DATE_TO | Select the primary address within its date range. |
PER_PHONES | HRMS | Person phone numbers. | PHONE_ID, PARENT_ID, PARENT_TABLE, PHONE_TYPE | Parent table value tells you which entity owns the phone. |
PER_BUSINESS_GROUPS | HRMS | Business groups. | BUSINESS_GROUP_ID, NAME | Restrict reports to one business group where several exist. |
PAY_ALL_PAYROLLS_F | HRMS | Payroll definitions. | PAYROLL_ID, PAYROLL_NAME | Join from assignments on PAYROLL_ID if Payroll is used. |
HR_ALL_ORGANIZATION_UNITS | Foundation | Organizations including operating units, inventory organizations and departments. | ORGANIZATION_ID, NAME, BUSINESS_GROUP_ID | Names can be translated; join to the _TL table for the language you need. |
HR_ORGANIZATION_INFORMATION | Foundation | Classifications and attributes of organizations. | ORGANIZATION_ID, ORG_INFORMATION_CONTEXT, ORG_INFORMATION1 | Use context 'CLASS' and 'Operating Unit' style values to filter by type. |
HR_LOCATIONS_ALL | Foundation | Locations used in assignments, ship-to and bill-to. | LOCATION_ID, LOCATION_CODE, ADDRESS_LINE_1 | Join from assignments on LOCATION_ID. |
FND_USER | Foundation | Application user accounts. | USER_ID, USER_NAME, EMPLOYEE_ID, END_DATE | EMPLOYEE_ID matches PER_ALL_PEOPLE_F.PERSON_ID. Filter end dates for active users. |
PA_PROJECTS_ALL | Projects | Project headers. | PROJECT_ID, SEGMENT1 (project number), NAME, ORG_ID, PROJECT_TYPE, START_DATE, COMPLETION_DATE | Filter by ORG_ID in multi-org reports. |
PA_PROJECT_TYPES_ALL | Projects | Project type definitions that drive classification and accounting. | PROJECT_TYPE, ORG_ID, PROJECT_TYPE_CLASS_CODE | Join on PROJECT_TYPE and ORG_ID. |
PA_TASKS | Projects | Project work breakdown structure tasks. | TASK_ID, PROJECT_ID, TASK_NUMBER, TASK_NAME, PARENT_TASK_ID, TOP_TASK_ID | Use TOP_TASK_ID to roll lower level tasks up to top tasks. |
PA_PROJECT_PLAYERS | Projects | People assigned to a project and their roles. | PROJECT_ID, PERSON_ID, PROJECT_ROLE_TYPE, START_DATE_ACTIVE, END_DATE_ACTIVE | Join PERSON_ID to PER_ALL_PEOPLE_F for names. |
PA_EXPENDITURES_ALL | Projects | Expenditure batches grouped by person or organization and week. | EXPENDITURE_ID, EXPENDITURE_STATUS_CODE, INCURRED_BY_PERSON_ID, EXPENDITURE_ENDING_DATE | Parent of expenditure items. |
PA_EXPENDITURE_ITEMS_ALL | Projects | Individual cost lines charged to project tasks. | EXPENDITURE_ITEM_ID, EXPENDITURE_ID, TASK_ID, EXPENDITURE_TYPE, QUANTITY, EXPENDITURE_ITEM_DATE | Join TASK_ID to PA_TASKS to reach the project. |
PA_EXPENDITURE_TYPES | Projects | Expenditure type definitions. | EXPENDITURE_TYPE, EXPENDITURE_CATEGORY, REVENUE_CATEGORY_CODE | Use to group costs by category. |
PA_COST_DISTRIBUTION_LINES_ALL | Projects | Costing and GL accounting for expenditure items. | EXPENDITURE_ITEM_ID, LINE_NUM, AMOUNT, DR_CODE_COMBINATION_ID, CR_CODE_COMBINATION_ID | Join code combination IDs to GL_CODE_COMBINATIONS for accounts. |
PA_DRAFT_REVENUES_ALL | Projects | Draft revenue headers per project. | PROJECT_ID, DRAFT_REVENUE_NUM, RELEASED_DATE | Revenue is released before interface to GL. |
PA_DRAFT_INVOICES_ALL | Projects | Draft invoice headers per project. | PROJECT_ID, DRAFT_INVOICE_NUM, RELEASED_DATE | Released drafts are transferred to Receivables. |
PA_AGREEMENTS_ALL | Projects | Customer contract agreements funding projects. | AGREEMENT_ID, CUSTOMER_ID, AGREEMENT_NUM | Link to projects through funding records. |
PA_PROJECT_FUNDINGS | Projects | Funding allocated from agreements to projects and tasks. | PROJECT_FUNDING_ID, AGREEMENT_ID, PROJECT_ID, TASK_ID | Bridge between agreements and projects. |
PA_BUDGET_VERSIONS | Projects | Budget and forecast versions for a project. | BUDGET_VERSION_ID, PROJECT_ID, BUDGET_TYPE_CODE, CURRENT_FLAG | Filter CURRENT_FLAG = 'Y' for the active version. |
PA_PROJECT_STATUSES | Projects | Project status definitions. | PROJECT_STATUS_CODE, PROJECT_STATUS_NAME | Join from PA_PROJECTS_ALL on the status code. |
Table and column names are shown for Release 12 style data models and may differ by patch level, installed products and custom extensions. Verify with DESCRIBE or the Oracle Electronic Technical Reference Manual for your release. Supplier, purchasing, GL and payables tables are in the Oracle EBS finance tables reference.
Need EBS reporting or AI that respects HR data rules?
Optional: describe your reporting need and we will outline a read-only, on-prem approach. The table list is free without it.
Optional. The tool and every export work without this. No spam.
How to use the table list
Filter by module to see only HRMS, Projects or foundation tables, or search by a word such as employee, task or expenditure. Each row shows the columns most reports join on and a short tip about date ranges or flags. Export the list to Excel for a design document. Supplier tables such as AP_SUPPLIERS and purchasing tables are covered in the Oracle EBS finance tables reference, so this page concentrates on people and projects.
The employee model
In EBS the employee is a person record in PER_ALL_PEOPLE_F, with a row per date-effective change. Assignments in PER_ALL_ASSIGNMENTS_F link the person to organization, job, position, location and supervisor. Employment periods are in PER_PERIODS_OF_SERVICE. To find the employee behind a user, join FND_USER.EMPLOYEE_ID to PER_ALL_PEOPLE_F.PERSON_ID. To find a buyer on a purchase order, the agent id on the PO header points to a person.
- Always filter date-effective tables by the date you need
- Pick the primary assignment unless the report needs all of them
- Use views such as PER_PEOPLE_F only when you know their security behavior
The projects model
A project in PA_PROJECTS_ALL has a task hierarchy in PA_TASKS. Costs arrive as expenditure items in PA_EXPENDITURE_ITEMS_ALL, charged to a task, and are costed in PA_COST_DISTRIBUTION_LINES_ALL, where the accounting code combinations live. Revenue and invoices start as drafts in PA_DRAFT_REVENUES_ALL and PA_DRAFT_INVOICES_ALL and are released before they transfer to the ledger and receivables. Agreements and fundings connect customer contracts to projects.
Common mistakes in EBS reporting queries
Forgetting date-effective filters returns duplicate people. Forgetting the multi-org filter on _ALL tables mixes operating units. Joining projects to costs through project id alone misses the task level. Reading base tables directly also bypasses application security, so confirm that your database access and data handling follow your company policy, especially for personal HR data. Test every query on a non-production copy first.
Frequently Asked Questions
What is the employee table in Oracle EBS?
The main table is PER_ALL_PEOPLE_F, which holds person records with effective dates. Employee assignments are in PER_ALL_ASSIGNMENTS_F, and employment dates are in PER_PERIODS_OF_SERVICE. Filter by effective date to get the current row.
Which tables hold Oracle EBS Projects data?
PA_PROJECTS_ALL for projects, PA_TASKS for tasks, PA_EXPENDITURE_ITEMS_ALL for cost lines, PA_COST_DISTRIBUTION_LINES_ALL for accounting, and PA_DRAFT_REVENUES_ALL and PA_DRAFT_INVOICES_ALL for revenue and billing.
How do I link an EBS user to an employee?
Join FND_USER.EMPLOYEE_ID to PER_ALL_PEOPLE_F.PERSON_ID, and filter the person row on today's date within its effective start and end dates. Some users, such as service accounts, have no employee.
Where can I list all Oracle EBS tables?
Query ALL_TABLES or DBA_TABLES filtered by owner, such as the product schema, or use the Electronic Technical Reference Manual for your release. This page lists the commonly used HR and Projects tables rather than every table.
If you want people to ask EBS questions in plain language without opening the HR or Projects data to everyone, Netray can build a private, role-aware layer on your servers. The list is free to use as is.
Related Tools
Oracle EBS Finance Tables Reference (GL, AP, AR, FA)
Lookup of the base tables behind General Ledger, Payables, Receivables, Fixed Assets, Subledger Accounting and Purchasing, with the columns you join on.
OracleOracle EBS User and Responsibility Audit SQL Generator
Fill in a user and responsibility pattern and get ready-to-run queries for active user access, administrator access and duplicate access reviews.
OracleOracle EBS WIP Tables Reference
Lookup of Oracle EBS Work in Process tables with purpose and key columns, covering discrete jobs, operations, requirements, resources and interfaces.
OracleOracle EBS Inventory Tables Reference
Searchable lookup of the core Oracle EBS Inventory and Cost Management tables with purpose and key columns, for report writers and technical consultants.
OracleOracle EBS Profile Options Reference
A searchable lookup of the profile options EBS administrators change most: sign-on security, concurrent processing, multi-org, logging and session settings.
More Oracle tools
See all- Oracle EBS Cycle Count Workload Calculator
- Oracle EBS Discrete Manufacturing Go-Live Checklist
- Oracle EBS Inventory Accuracy Readiness Assessment
- Oracle EBS Concurrent Request Phase and Status Reference
- Oracle EBS Responsibility Design Worksheet
- Oracle EBS GL Period Close Checklist
- Oracle EBS AP Invoice Interface Template
- Oracle EBS 12.2 Upgrade Readiness Checklist
- Oracle EBS Patching Checklist for adop
Keep exploring
Free tools
- Oracle EBS Inventory Tables ReferenceSearchable lookup of the core Oracle EBS Inventory and Cost Management tables with purpose and key columns, for report writers and technical consultants.
- Oracle EBS WIP Tables ReferenceLookup of Oracle EBS Work in Process tables with purpose and key columns, covering discrete jobs, operations, requirements, resources and interfaces.
- Oracle EBS Cost Management Tables ReferenceA lookup of the Oracle EBS tables behind item cost, WIP jobs, BOM, routing and inventory transactions used in manufacturing costing.
ERP fixes and how-tos
- Data Conversion in Oracle EBS: Interface Tables, Open Interfaces and ReconciliationHow data conversion works in Oracle EBS: extract, stage, validate, load through interface tables and APIs, then reconcile. Practical steps and common failures.
- Oracle Work Order Tables: WIP_ENTITIES, WIP_DISCRETE_JOBS and the REST RouteOracle work order tables explained: WIP_ENTITIES, WIP_DISCRETE_JOBS, operations and requirements, with a join query for EBS and the Fusion REST route.
- Oracle EBS Standard Reports by Module and Sample XML for BI PublisherHow to find and run Oracle EBS standard reports by module, and how to get sample XML data to design a BI Publisher template for an EBS report.
Runs with your ERP
- Esker Order Management and Oracle E-Business SuiteHow Esker captures customer POs from email, fax and PDF and creates sales orders in Oracle EBS: integration, data flows, setup, pitfalls and a private AI layer.
- Cloud Inventory (DSI) with Oracle E-Business Suite: Mobile Barcode InventoryCloud Inventory, formerly DSI, adds offline mobile barcode inventory to Oracle E-Business Suite and JD Edwards. Data flows, setup steps, pitfalls and AI notes.
- BarTender and Oracle E-Business Suite: Label Printing from OracleHow BarTender prints labels from Oracle EBS using Oracle XML print requests and Integration Builder: methods, data flows, setup steps, pitfalls and AI help.
AI on your ERP
- AI for Oracle E-Business Suite, Without Leaving On-PremHosting Oracle EBS 12.2 on-prem or in a private cloud? Add a private LLM over concurrent programs, interface tables and AP/PO data without moving to Fusion.
- AI for Oracle EBS in Industrial Manufacturing: WIP, MRP, and BOM Questions Answered DirectlyAI for Oracle E-Business Suite in industrial manufacturing: WIP jobs, BOMs, MRP, inventory and purchasing answered in plain English, private and read-only.
Integrations
- How to use ChatGPT with Oracle E-Business SuiteUsing ChatGPT with Oracle E-Business Suite: export vs live access, custom GPT actions over ISG REST, enterprise data controls and when to keep data on-prem.
- How to connect Claude to Oracle E-Business SuiteConnect Claude to Oracle E-Business Suite via MCP, the Anthropic API, AWS Bedrock or Vertex, and know when an on-prem open-weight model is the better fit.