Search Results fnd_oam_doc_category_u1

Overview

APPLSYS.FND_OAM_DOC_CATEGORY is a reference and configuration table within the Oracle EBS Applications Object Library (OAM) schema components owned by APPLSYS. Per the ETRM documentation, this table stores information about Metalink document categories. It is a low-volume, largely static lookup table that classifies external support documentation records used by Oracle Applications Manager (OAM) diagnostics, patch recommendations, and self-service support features. The table is stored in the APPS_TS_TX_DATA tablespace with PCT Free set to 20 and is registered in FND Design Data as FND.FND_OAM_DOC_CATEGORY.

From a Data Vault modeling perspective, the heuristic classification mined from the FK structure is standalone. In practical terms, the object behaves as a reference hub: its business key (CATEGORY_KEY combined with CATEGORY_TYPE) uniquely identifies each documentation category, and dependent descriptive attributes such as CATEGORY_NAME are carried on the same row. No foreign key relationships are documented in either direction, so no link or satellite decomposition is warranted.

Key Information Stored

The table contains 8 documented columns. The most significant are:

• CATEGORY_KEY (VARCHAR2 40) — the key identifying the category; part of the composite primary key.
• CATEGORY_TYPE (VARCHAR2 30) — the type of category; the second component of the composite primary key.
• CATEGORY_NAME (VARCHAR2 256) — the human-readable name of the category, the primary descriptive attribute intended for display and reporting.
• CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard Who columns providing audit lineage for row creation and last modification.

The surrogate primary key is FND_OAM_DOC_CATEGORY_PK, defined on (CATEGORY_KEY, CATEGORY_TYPE). The unique index FND_OAM_DOC_CATEGORY_U1, which is the object the user searched for, is a NORMAL UNIQUE index in the APPS_TS_TX_IDX tablespace defined on the same two columns (CATEGORY_TYPE, CATEGORY_KEY). Because the unique index column set is identical to the primary key, no separate business-key candidate beyond the primary key exists; the composite pair serves simultaneously as surrogate identifier and natural business key. The leading column difference in index order (CATEGORY_TYPE first in U1 versus CATEGORY_KEY first in the PK) means U1 can also satisfy queries that filter or sort on CATEGORY_TYPE without an additional index.

Common Use Cases and Queries

Typical usage involves resolving category keys to display names for reporting and for validating category values passed by OAM diagnostic or support-lookup processes. The ETRM-supplied query text provides the baseline projection:

SELECT CATEGORY_KEY
     , CATEGORY_TYPE
     , CATEGORY_NAME
     , CREATED_BY
     , CREATION_DATE
     , LAST_UPDATE_DATE
     , LAST_UPDATED_BY
     , LAST_UPDATE_LOGIN
  FROM APPLSYS.FND_OAM_DOC_CATEGORY;

A parameterized lookup exploiting the U1 unique index would filter by type and key:

SELECT CATEGORY_NAME
  FROM APPLSYS.FND_OAM_DOC_CATEGORY
 WHERE CATEGORY_TYPE = :p_type
   AND CATEGORY_KEY  = :p_key;

Reporting scenarios include listing all categories of a given type, auditing new categories created within a period using CREATION_DATE, and detecting rows whose LAST_UPDATE_DATE diverges from CREATION_DATE to identify locally modified reference data. Because the table is small and standalone, it is commonly joined in a left-outer fashion to documentation tables when the category may not resolve to a registered name.

Related Objects

The ETRM relationship data documents that APPLSYS.FND_OAM_DOC_CATEGORY does not reference any database object, and it is referenced only by the underlying synonym or view wrapper APPLSYS.FND_OAM_DOC_CATEGORY#. No foreign key dependencies are recorded in either direction, so the object participates in joins only through application-level conventions rather than enforced constraints.

Objects most likely related in practice include other OAM reference and document tables in the APPLSYS schema, such as the companion documentation tables that store the Metalink document records categorized by this table, and the FND_OAM_DOC_CATEGORY# underlying object through which the table is exposed to the APPS synonym layer. Developers querying this table should address it as APPLSYS.FND_OAM_DOC_CATEGORY or via the APPS synonym, as dictated by the runtime environment. Because there are no declarative foreign keys, referential integrity between this table and any consuming documentation table is maintained entirely by application logic, and any integration or extract process should treat the object as a controlled reference set rather than a transactional source.