Search Results sub_category_name




Overview
Explore in AI Chat

Overview

APPS.HR_DOCUMENT_TYPES_V is an Oracle E-Business Suite 12.1.1 and 12.2.2 view that exposes the definitions of document types used by Oracle Human Resources and, more broadly, by Oracle Advanced Benefits and related HR self-service flows. In the Oracle EBS data model, the physical definition of a document type is stored in the HR_DOCUMENT_TYPES table, with translated name and description values held in HR_DOCUMENT_TYPES_TL. This view joins those base objects to the HR_LOOKUPS view and to PER_SHARED_TYPES_VL to present each document type together with its user-facing category meaning and shared type name. The result is a consolidated, reporting-friendly source whose most frequently queried attribute is CATEGORY_CODE, the column that most users know as document_category. Because the view resolves the multilingual translation and the lookup meaning at query time, it is commonly used in extracts, Oracle Business Intelligence Publisher (BI Publisher) reports, and custom integrations that must classify documents by category without reproducing the underlying joins.

Underlying Base Objects

The view text, as documented in the ETRM metadata, selects from four objects:

The documented metadata also references the HR_API package, which manages the underlying definitions and enforces the business rules that the view simply surfaces. No DML should be performed against the view; document types are maintained through the HR API or the corresponding setup forms.

Key Columns

  • DOCUMENT_TYPE_ID — primary identifier of the document type; the join key to HR_DOCUMENT_TYPES_TL and to dependent tables such as HR_DOCUMENT_EXTRA_INFO.
  • DOCUMENT_TYPE / DESCRIPTION — the translated name and description from HR_DOCUMENT_TYPES_TL.
  • CATEGORY_CODE — the code identifying the document category (for example, the value users search for as document_category).
  • MEANING — the lookup meaning corresponding to CATEGORY_CODE, appropriate for display in reports.
  • SUB_CATEGORY_CODE / SHARED_TYPE_NAME — the shared type code and its resolved name, where applicable.
  • ACTIVE_INACTIVE_FLAG, MULTIPLE_OCCURENCES_FLAG, LEGISLATION_CODE, AUTHORIZATION_REQUIRED, WARNING_PERIOD — operational attributes controlling whether the type is active, whether multiple occurrences are permitted, the legislation it belongs to, whether authorization is required, and the warning period applied.
  • SYSTEM_DOCUMENT_TYPE — distinguishes seeded/system document types from user-defined ones.
  • ROWID and audit columns — exposed for tooling and change tracking.

Common Use Cases and Queries

Typical uses include reporting all active document types by category, driving BI Publisher layouts, validating configured document categories during data conversion, and joining to person-level document records.

List active document types within a category:

SELECT document_type_id, document_type, description
FROM   apps.hr_document_types_v
WHERE  category_code = :p_category_code
AND    active_inactive_flag = 'Y'
ORDER BY document_type;

Report counts by category using the resolved meaning:

SELECT meaning, COUNT(*) document_type_count
FROM   apps.hr_document_types_v
WHERE  active_inactive_flag = 'Y'
GROUP BY meaning
ORDER BY meaning;

Join to person documents to classify records by document category:

SELECT p.full_name, dtv.meaning, dtv.document_type
FROM   apps.per_all_people_f p,
       apps.per_person_documents ppd,
       apps.hr_document_types_v dtv
WHERE  ppd.person_id = p.person_id
AND    ppd.document_type_id = dtv.document_type_id
AND    TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date;

Because the view filters translation rows by userenv('LANG'), results reflect the language of the querying session; consumers requiring a fixed language should set the session locale or query the base tables with an explicit language predicate.