Results for “ast_notes_details_vl”

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

Overview

AST_NOTES_DETAILS_VL is a translatable (VL-suffixed) view owned by the APPS schema in Oracle E-Business Suite, defined within the AST — TeleSales product. It exposes note detail records maintained through the JTF (Oracle Application Object Library / CRM Foundation) Notes framework, resolving the base note rows in JTF_NOTES_B, their translation-layer text in JTF_NOTES_TL, and several decoded lookup meanings. Its status is VALID across both the 12.1.1 and 12.2.2 releases, where the underlying object metadata confirms the same APPS ownership and the same set of referenced base objects.

The view serves as a reporting and integration surface for note data associated with TeleSales and related source objects. Because it joins the translation table on TL.LANGUAGE = USERENV('LANG'), it returns the note text in the session language, making it suitable for both online queries and concurrent-program extraction. It is a common target for the search term fnd_type, since NOTE_TYPE is decoded through FND_LOOKUPS against the lookup type JTF_NOTE_TYPE, which yields the human-readable NOTE_TYPE_MEANING column.

Underlying Base Objects

The documented ETRM 12.2.2 metadata lists the referenced base objects as FND_GLOBAL (PACKAGE), FND_LOOKUPS (VIEW), JTF_COMMON_PVT (PACKAGE), JTF_NOTES_B (SYNONYM), JTF_NOTES_TL (SYNONYM), and DBMS_LOB (SYNONYM). The view text reflects this composition:

• JTF_NOTES_B — the base notes table, supplying surrogate key JTF_NOTE_ID, WHO-audit columns, ENTERED_BY, ENTERED_DATE, SOURCE_OBJECT_ID, SOURCE_OBJECT_CODE, NOTE_STATUS, NOTE_TYPE, and the fifteen ATTRIBUTE and CONTEXT descriptive-flex columns. It is outer-joined to several decode sources.
• JTF_NOTES_TL — the translation table joined on JTF_NOTE_ID and the session language, supplying NOTES and NOTES_DETAIL (the latter measured with DBMS_LOB.GETLENGTH to produce NOTES_DETAIL_SIZE).
• FND_LOOKUPS — joined twice as an outer join: FND_TYPE decodes NOTE_TYPE via lookup type JTF_NOTE_TYPE, and FND_STATUS decodes NOTE_STATUS via JTF_NOTE_STATUS. These references are why a search for fnd_type surfaces this view.
• JTF_RS_RESOURCE_EXTNS — outer-joined on RES.USER_ID = B.CREATED_BY to resolve RES.SOURCE_NAME, surfaced as CREATED_BY_NAME.

The packages FND_GLOBAL and JTF_COMMON_PVT participate through the standard JTF Notes logic retained in the view definition, while DBMS_LOB is invoked for LOB length computation.

Key Columns

• JTF_NOTE_ID — primary identifier linking the base and translation rows.
• NOTES — the note text in the resolved session language.
• NOTE_TYPE / NOTE_TYPE_MEANING — the lookup code and its decoded meaning from JTF_NOTE_TYPE.
• NOTE_STATUS / NOTE_STATUS_MEANING — the status code and decoded meaning from JTF_NOTE_STATUS.
• SOURCE_OBJECT_ID / SOURCE_OBJECT_CODE — identifies the business object to which the note is attached.
• CREATED_BY_NAME — the resource name of the creating user, resolved via JTF_RS_RESOURCE_EXTNS.
• NOTES_DETAIL_SIZE — character length of the notes detail LOB.
• ATTRIBUTE1–ATTRIBUTE15, CONTEXT — descriptive flexfield segments.
• ROW_ID — row identifier exposed for update-safe processing.

Common Use Cases and Queries

Typical uses include reporting note activity against TeleSales source objects, validating that note types and statuses resolve to defined lookups, and extracting note text for integration or archival jobs.

SELECT jtf_note_id, note_type, note_type_meaning,
       note_status_meaning, created_by_name,
       entered_date, notes_detail_size
FROM   apps.ast_notes_details_vl
WHERE  note_type = :p_note_type
AND    entered_date >= :p_from_date;

To audit unresolved lookups (a common fnd_type diagnostic):

SELECT jtf_note_id, note_type, note_type_meaning
FROM   apps.ast_notes_details_vl
WHERE  note_type_meaning IS NULL;