Search Results jtf_interaction

Overview

APPS.JTF_IH_NOTES_VL is a reporting and integration view within the Oracle E-Business Suite CRM foundation layer. It exposes interaction notes — free-text annotations captured against customer interactions — by joining the note body stored in the notes entity to the interaction header it belongs to. In Oracle EBS 12.1.1 and 12.2.2, this view is the primary read interface used by forms, concurrent programs, and integration extracts that need to retrieve interaction notes without navigating the underlying many-to-many context model. Because it is a view rather than a table, it carries no storage of its own and reflects the current state of the base tables at query time. The "_VL" suffix indicates a validation view in the Oracle Application Object Library convention, though the definition here is a plain join rather than an MLR-enabled translation view.

Underlying Base Objects

The view definition is a three-way inner join across the notes and interaction schemas:

• JTF_NOTES_VL (VIEW) — supplied as alias N; the source of the note text column NOTES and of JTF_NOTE_ID.
• JTF_NOTE_CONTEXTS (SYNONYM) — supplied as alias NC; the association table that binds a note to a context type and a context identifier. The predicate restricts NOTE_CONTEXT_TYPE to the literal 'JTF_INTERACTION'.
• JTF_IH_INTERACTIONS (SYNONYM) — supplied as alias INZ; the interaction header, joined on INTERACTION_ID to the context identifier.

The documented referenced base objects also include the package FND_GLOBAL, JTF_COMMON_PVT, and JTF_NOTES_PKG. The latter two supply the validation and security logic that the underlying notes tables inherit, while FND_GLOBAL supplies application and user context (for example, ORG_ID and USER_ID) used by the row-level security policies applied to the base notes objects. Because the join relies on those policies, query results from the view are implicitly filtered by the accessing responsibility's organization and security profile.

Key Columns

• NOTES — the note body text as stored in JTF_NOTES_VL. This is the primary payload column for reporting and extract.
• NOTE_CONTEXT_TYPE_ID — the context identifier carried on the note context row. For rows returned by this view, it equals the interaction identifier because the view constrains NOTE_CONTEXT_TYPE to 'JTF_INTERACTION'.
• INTERACTION_ID — the interaction header identifier from JTF_IH_INTERACTIONS; the effective foreign key linking the note to its interaction.

Columns such as note creation date, last update date, and created-by are not exposed by this view and must be obtained by joining JTF_NOTES_VL separately via JTF_NOTE_ID.

Common Use Cases and Queries

The view is typically used in interaction-history reports, CRM extracts, and reconciliation scripts that require note text aligned to interaction identifiers. A representative query retrieves notes for a single interaction:

• SELECT n.NOTES, n.INTERACTION_ID, n.NOTE_CONTEXT_TYPE_ID FROM APPS.JTF_IH_NOTES_VL n WHERE n.INTERACTION_ID = :p_interaction_id;

To report note volume per interaction, aggregate without retrieving the body:

• SELECT INTERACTION_ID, COUNT(*) FROM APPS.JTF_IH_NOTES_VL GROUP BY INTERACTION_ID;

When note audit attributes are required, join the notes entity independently to obtain creation and update metadata alongside the interaction identifier. Because the view applies the security context of the querying session, it should be queried from a responsibility with appropriate CRM access rather than from a custom schema, and direct DML against it is not supported.