Oracle HCM Tables and Views: Which Reference Answers What? The answer depends on whether you are documenting schema objects, building an integration, or reporting on workforce data: those tasks use different references, and confusing them creates brittle SQL and misleading results.
Key Takeaways
| Your task | Start with | Why it fits |
|---|---|---|
| Inspect object names and fields | Table-and-column schema reference | Finds documented columns, keys, and candidate relationships. |
| Build an integration | Supported REST, SOAP, or HDL guidance | Defines the interface and its expected payload or load behavior. |
| Create an analytics report | OTBI subject area or reporting view | Can provide business semantics and security behavior suited to reporting. |
| Trace a person to an assignment | PERSON_ID plus effective-date rules | Connects candidate records, but does not by itself select one current row. |
| Check declared relationships | Primary- and foreign-key constraint metadata | Identifies declared row relationships; object dependencies answer a different question. |
| Look up view metadata | HR_SEARCH_VIEW_ATTRIBUTES | A relevant object to inspect when researching search-view attributes. |
What Oracle HCM table and view references describe
An Oracle HCM table stores rows in a relational database object. A view presents rows through a query, which may select from one table or combine, filter, and transform data from several objects.
Schema references help you identify object names and inspect documented columns, data types, and field purposes. Key and constraint details help establish how rows are identified and which relationships the database declares.
Indexes describe database access paths. They can inform query investigation, but an index does not prove that a join is correct or guarantee performance in every reporting environment.
Keep schema documentation separate from business-facing guidance. A table name does not establish that the object is an appropriate reporting source or a supported interface for writing data into Oracle HCM Cloud.
Choose a reference based on the task
Use table-and-column references when you need to inspect object names, fields, and documented relationships for SQL against an available reporting schema. They answer structural questions about the Oracle HCM data model, not every question about business meaning or access.
For integrations, start with the supported interface documentation, such as REST, SOAP, or HDL guidance. A database table’s structure is not an integration contract or permission to write directly to application tables.
Choose the payload format defined by the supported endpoint rather than translating database columns directly. REST commonly uses JSON resource representations for supported resource operations; follow the endpoint’s required fields and operation semantics. SOAP uses the service’s XML request and response contract, which may expose operations that are not simple resource calls. For bulk worker loads, HDL uses data files organized around business objects and their components and attributes; check the object syntax, reference keys, and load mode in its guidance. In each case, use the published contract and its response or error behavior, not a table definition.
For analytics, first assess the relevant OTBI subject area or reporting view. Those sources may apply business semantics and security behavior that a transactional-table query does not, so choosing raw tables before checking the reporting layer can change both the result and who can see it.
For a portable inventory of object names, columns, suffixes, and module groupings, the free Oracle HCM Schema Reference PDF and Oracle Cloud Tables serve different lookup needs. The linked page says the PDF covers 14,950 tables with key columns, suffixes, and module breakdown and is emailed within 24 hours; it describes the searchable tool as covering tables and columns, with a join path finder and access priced at $1.50.
Choose the PDF when you want an offline inventory to scan or keep alongside project notes. Choose the searchable reference when you need to search for a particular column or investigate possible join columns between two objects.
Trace a business question to tables and columns
Start with the business entity and the output you need. A report about a person, assignment, or department should lead you to candidate objects and documented columns, not to guesses based on a familiar-looking table name.
For a person-and-assignment query, PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_M are concrete candidates. Both expose PERSON_ID, which can connect person and assignment records when the task and effective dates align.
PER_ALL_PEOPLE_F is effective-dated. Constrain EFFECTIVE_START_DATE and EFFECTIVE_END_DATE for the as-of date you intend to report, or historical person rows with the same PERSON_ID can match.
An assignment can also have rows across effective dates or sequences. A join on PERSON_ID alone does not select one current assignment; define the reporting date and assignment-selection rule first.
Apply the reporting date and join conditions to each relevant object; the output grain depends on the relationships between those objects.
Bind :as_of_date as a date value and apply the assignment rules your report requires. Do not add DISTINCT just to hide extra rows; first determine whether the rows represent valid assignments, multiple effective sequences, or an incorrect join.
Verify keys, foreign keys, and dependency direction
A primary key identifies a row at the correct grain. PERSON_ID is a business identifier that can recur across effective-dated person rows, so it does not necessarily identify one row by itself.
Where you can query Oracle metadata, ALL_CONSTRAINTS and ALL_CONS_COLUMNS describe declared constraints and their columns. A foreign key’s referenced constraint helps establish which key it points to and the direction of that declared relationship.
ALL_DEPENDENCIES answers a different question: which database objects depend on other objects. For example, a view can depend on underlying tables, but that dependency does not establish a foreign-key relationship between their rows.
Reporting environments can restrict access to database metadata. Use the schema reference together with the query environment you actually have, and do not treat a missing constraint listing as proof that no logical relationship exists.
To find foreign-key references to a particular table, search constraint metadata for constraints whose referenced constraint belongs to that table, then inspect the associated columns. The metadata views and privileges available in your session determine which relationships you can see.
For example, this query finds declared foreign keys visible in your session that reference a table name. It pairs columns by position so composite keys remain aligned:
SELECT fk.owner AS fk_owner, fk.table_name AS fk_table, fk.constraint_name, fkc.column_name AS fk_column, pk.owner AS referenced_owner, pk.table_name AS referenced_table, pkc.column_name AS referenced_column FROM all_constraints fk JOIN all_cons_columns fkc ON fkc.owner = fk.owner AND fkc.constraint_name = fk.constraint_name JOIN all_constraints pk ON pk.owner = fk.r_owner AND pk.constraint_name = fk.r_constraint_name JOIN all_cons_columns pkc ON pkc.owner = pk.owner AND pkc.constraint_name = pk.constraint_name AND pkc.position = fkc.position WHERE fk.constraint_type = 'R' AND pk.table_name = UPPER(:table_name) ORDER BY fk.owner, fk.table_name, fk.constraint_name, fkc.position. If the same table name exists under multiple owners, add AND pk.owner = UPPER(:owner) to narrow the results. A missing result can reflect metadata visibility or an undeclared logical relationship.
Select reporting sources and handle lookup data
Prefer a reporting-oriented view or OTBI subject area when it supplies the business meaning and security behavior your report needs. Use transactional tables when the report genuinely needs their detail and your environment permits that access.
Lookup tables map stored codes to meanings or categories. A lookup join should match the relevant lookup type and code, and should account for language and active-date conditions where the lookup structure exposes them.
A hard-coded meaning can become the wrong label when language or effective period matters. Joining a lookup with the wrong type, language, or date scope can multiply rows or display an unintended description.
Check the grain and security behavior of every source before aggregation. Effective-dated records, secured data, and one-to-many relationships all affect row counts and totals, even when the SQL runs without an error.
That is why the “right table” is not always the lowest-level table you can find. For OTBI reporting, BI Publisher, or SQL in an approved reporting environment, choose the source that matches the required business definition and the detail level of the output.
Use schema search as a repeatable verification workflow
Search the business term and likely object names, then inspect candidate columns, key information, and related objects before drafting SQL. Table, column, and join-path search makes that discovery process repeatable rather than dependent on guesses.
Confirm that the reference matches the relevant Fusion HCM release and intended use. Product schema details, integration contracts, and reporting behavior are different reference types, and details can differ across releases.
If one reference does not surface a candidate, search distinctive column names and related-object names in a broader schema inventory. Then validate the object in the appropriate product, integration, or reporting documentation before using it.
Before relying on a query, test its row grain and join behavior with effective-date and security conditions in mind. Record the chosen source, join keys, and date logic so another developer can review the result and maintain it.
Before publishing, test the report under the intended reporting role and check that its row grain and totals match the business question. If the source’s purpose or security behavior cannot be verified in the available schema documentation, confirm it in supported reporting guidance rather than inferring it from the object name.
The linked reference page describes the Oracle Cloud Tables search tool as covering HCM, Financials, SCM, CX Sales, and Common Features. It searches tables and columns and includes a join path finder, which can help move from a business term to candidate objects or compare possible join columns.
Frequently Asked Questions
Can a view be based on more than one Oracle HCM table?
Yes. A view can join several tables and expose a single result set, including columns that have been renamed or calculated in the view definition. When you inspect a view, account for its filters and expressions as well as its underlying objects.
Why can joining an effective-dated table produce duplicate rows?
Compare the row count before and after each join, then inspect the keys on the first step where it increases.
Can I use Oracle HCM database tables as a direct integration interface?
No. Use the supported integration interface and follow its documented payload and processing rules.
What should I do when a schema reference does not list a table I need?
Check whether the needed data is exposed through another object in the release you use.
Conclusion
Oracle HCM Tables and Views: Which Reference Answers What? Start with the task: schema references describe objects and columns, integration guidance defines supported interfaces, and OTBI or reporting views serve analytics with their own business and security behavior.
Then trace the business entity to candidate tables, verify keys and relationship direction, and apply effective-date rules before counting or aggregating rows. That is the difference between finding an Oracle HCM table and using it correctly.