Results for “jtf_notes_tl”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

JTF_NOTES_TL is the translated base (TL) table for the Notes module within the JTF – CRM Foundation product of Oracle E-Business Suite. It stores language-specific, translatable content for notes created anywhere in the EBS CRM and application stack that uses the common Notes framework. Because the Notes module is a shared infrastructure component, JTF_NOTES_TL typically holds far more rows than any single application's own note tables, servicing reference material attached to customers, opportunities, service requests, tasks, and other entities.

The table resides in the JTF schema and is documented as VALID. Its descriptive columns (NOTES and NOTES_DETAIL) are the translated attributes, and one row exists per note per installed language. From a Data Vault modeling perspective, the mined metadata classifies this object as satellite-leaning: it hangs off a parent business entity key and stores descriptive, time-variant attributes, which is the classic pattern of a satellite attached to a hub or link. This is offered as a heuristic modeling suggestion rather than an enforced design.

Key Information Stored

The primary key is the composite JTF_NOTES_TL_PK, defined on (JTF_NOTE_ID, LANGUAGE). JTF_NOTE_ID is the surrogate identifier of the underlying note, while LANGUAGE identifies the language row. An additional unique index, JTF_NOTES_TL_U1, covers the same (JTF_NOTE_ID, LANGUAGE) pair, so the primary key is also the business-key candidate for uniqueness. The translation key is therefore the note identifier plus the language code, and no translation row exists without its base row.

The most significant columns include:

• JTF_NOTE_ID — the note identifier; also the foreign key to the base table JTF_NOTES_B.
• NOTES — the short translated note text, the primary translated attribute.
• NOTES_DETAIL — the longer translated detail body accompanying the note.
• LANGUAGE — the language of the translated row (part of the primary key).
• SOURCE_LANG — the source language from which the translation derives, supporting the standard MLS language-derivation logic.
• SECURITY_GROUP_ID — the security grouping, with a documented foreign key to FND_SECURITY_GROUPS, used for multi-org and data-security filtering.
• CREATION_DATE, CREATED_BY — standard WHO audit columns for row creation.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns for the most recent change.

The documented physical schema lists eleven columns in total at ETRM 12.2.2, matching the list above.

Common Use Cases and Queries

The dominant use case is retrieving a note's content in the session language, falling back to the source language when no translated row exists. A typical pattern joins JTF_NOTES_TL to JTF_NOTES_B and filters by language:

• Lookup by primary key: SELECT notes, notes_detail FROM jtf_notes_tl WHERE jtf_note_id = :p_id AND language = USERENV('LANG');
• Translation-coverage reporting: count rows per LANGUAGE for a given note set to find missing translations.
• Fallback query: LEFT JOIN JTF_NOTES_TL on JTF_NOTE_ID and LANGUAGE, using SOURCE_LANG to resolve content where the requested language is absent.
• Audit/aging reports: group by CREATION_DATE, CREATED_BY, or LAST_UPDATE_DATE to track note maintenance activity.
• Security-scoped extracts: filter by SECURITY_GROUP_ID to honor multi-org and data-security assignments.

Because translations multiply row counts, reporting queries should always constrain on JTF_NOTE_ID and LANGUAGE rather than scanning the table.

Related Objects

The most significant related objects, based on the documented foreign key and primary key relationships, are:

• JTF_NOTES_B — the base (non-translated) Notes table. Joined via JTF_NOTES_TL.JTF_NOTE_ID = JTF_NOTES_B.JTF_NOTE_ID; this is the mandatory parent for every TL row.
• FND_SECURITY_GROUPS — referenced by JTF_NOTES_TL.SECURITY_GROUP_ID, providing the security grouping definition used to scope note visibility.
• JTF_NOTES_TL_PK — the primary key constraint on (JTF_NOTE_ID, LANGUAGE).
• JTF_NOTES_TL_U1 — the unique index on the same columns, reinforcing the business key.
• Notes module APIs and views — the JTF Notes PL/SQL APIs and the Notes DDL views built over JTF_NOTES_B and JTF_NOTES_TL provide the supported read/write interface, and any consumer application joins through them rather than touching the TL table directly.

Applications in CRM Foundation and downstream modules that attach notes to their entities depend on this table indirectly through JTF_NOTES_B, making it a shared translation satellite across the EBS note framework.