Search Results hr_document_types_u1

Overview

HR.HR_DOCUMENT_TYPES is a transactional configuration table in the Oracle E-Business Suite Human Resources (HR) schema. It stores the definitions of the types of personal documents that can be maintained through the Documents of Record functionality. Each row represents a single document type — for example a passport, driver license, or work permit — and governs how that document type behaves for a given legislation: whether it may occur more than once for a person, whether authorization is required, and how far in advance an expiry warning should be raised.

In Oracle EBS 12.1.1 and 12.2.2 the table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and its indexes are held in APPS_TS_TX_IDX. The object is owned by the HR schema and its FND design data is registered under PER.HR_DOCUMENT_TYPES, confirming it as an Oracle-owned, VALID dictionary object. Mined from its foreign key structure, the table is classified heuristically as standalone, meaning it does not participate as a dependent child in a parent/child hierarchy. From a Data Vault modeling perspective, it is most naturally treated as a hub-like reference entity keyed by the document type identifier, with its descriptive attributes (flags, legislation, warning periods) functioning as reference data rather than a transactionally versioned satellite.

Key Information Stored

The table holds 20 documented columns. The most significant are:

• DOCUMENT_TYPE_ID (NUMBER, 15) — System-generated surrogate primary key, enforcing HR_DOCUMENT_TYPES_PK.
• SYSTEM_DOCUMENT_TYPE (VARCHAR2, 40) — Internal name of the document type; the leading column of the business-key unique index.
• CATEGORY_CODE / SUB_CATEGORY_CODE (VARCHAR2, 30) — Category classification and the sub-category, the latter sourced as a foreign key from PER_SHARED_TYPES.
• ACTIVE_INACTIVE_FLAG (VARCHAR2, 30) — Controls whether the document type is available for use.
• MULTIPLE_OCCURENCES_FLAG (VARCHAR2, 30) — Indicates whether a person may hold multiple occurrences of the type.
• LEGISLATION_CODE (VARCHAR2, 30) — The legislation to which the document type applies.
• AUTHORIZATION_REQUIRED (VARCHAR2) — Whether the document type requires authorization.
• WARNING_PERIOD (NUMBER, 15) — Number of days before expiry at which a warning should be raised.
• OBJECT_VERSION_NUMBER — System-generated row version, incremented on each update for optimistic locking.
• Standard Who columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE.
• ZD_EDITION_NAME (VARCHAR2, 30) — Editioning column that participates in both unique indexes in the 12.2 Online Patching (EBR) model.

Two unique indexes are documented: HR_DOCUMENT_TYPES_PK on (DOCUMENT_TYPE_ID, ZD_EDITION_NAME), the true surrogate key, and HR_DOCUMENT_TYPES_U1 on (SYSTEM_DOCUMENT_TYPE, ZD_EDITION_NAME), which is the business-key candidate and the object the user searched for.

Common Use Cases and Queries

Typical reporting scenarios include listing all active document types for a legislation, identifying types that require expiry warnings, and joining type definitions to a person's recorded documents of record.

-- Active document types by legislation
SELECT document_type_id, system_document_type,
       category_code, warning_period
FROM   hr.hr_document_types
WHERE  active_inactive_flag = 'Y'
AND    legislation_code = :p_legislation;

-- Resolve a business key to its surrogate
SELECT document_type_id
FROM   hr.hr_document_types
WHERE  system_document_type = :p_system_document_type;

-- Types permitting multiple occurrences
SELECT document_type_id, system_document_type
FROM   hr.hr_document_types
WHERE  multiple_occurences_flag = 'Y';

Equivalent queries should always be filtered on the edition where EBR is enabled, and read access is normally granted through the APPS schema.

Related Objects

Because the table is standalone, few objects reference it directly, and the responsibility is largely one of lookup. The most significant relationships are:

• PER_SHARED_TYPES — source of SUB_CATEGORY_CODE, the sub-category lookup.
• MTL_MWB_GTMP — references HR_DOCUMENT_TYPES via DOCUMENT_TYPE_ID.
• ZX_PTNR_NEG_TAX_LINE_GT — references HR_DOCUMENT_TYPES via DOCUMENT_TYPE_ID.
• HR_DOCUMENT_TYPES_PK and HR_DOCUMENT_TYPES_U1 — the primary and business-key unique indexes.
• Documents of Record (PER) APIs and person document tables — consume these definitions when records are created or validated for a person.

Because the FK relationships are few, changes to document type definitions should be treated as controlled reference-data maintenance.