Results for “ast_notes_lite_vl”

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

Overview

AST_NOTES_LITE_VL is a TeleSales (AST) view in Oracle E-Business Suite that exposes a denormalized, language-aware projection of notes recorded against source objects. It consolidates note header, note text, resource, and object-type attributes into a single queryable structure, allowing reports and integrations to retrieve note content alongside descriptive resource and object meanings without joining the underlying JTF tables manually. The view inherits standard _VL semantics: it is language-sensitive, resolving translated columns through USERENV('LANG'), so it returns notes text and object meanings only in the session language.

Its practical role is to serve downstream reporting, teleSales agent-facing inquiry screens, and integration extracts that need note data together with the identity of the person who entered the note. Because the view joins note text to resource extension data on a nullable key, it also surfaces notes whose originating resource record cannot be matched.

Underlying Base Objects

The view is defined over four base objects:

• JTF_NOTES_B — the note header (base) table, providing the note identifier, source object linkage, creation/audit columns, note type, and status.
• JTF_NOTES_TL — the translatable note text, joined on JTF_NOTE_ID with T.LANGUAGE = USERENV('LANG').
• JTF_OBJECTS_TL — the translated object-code lookup supplying SOURCE_OBJECT_MEANING, joined on SOURCE_OBJECT_CODE = OT.OBJECT_CODE with the same language predicate.
• JTF_RS_RESOURCE_EXTNS — resource extension data joined as RES.USER_ID(+) = B.CREATED_BY, providing the resource's SOURCE_NAME. The outer-join symbol denotes that a resource match is optional; notes without a corresponding resource record still return.

ETRM metadata records no additionally documented referenced base objects beyond this set. Note that the view is documented as “not implemented in this database” in the source ETRM extract, meaning availability depends on the installed/migrated configuration of the JTF note schema.

Key Columns

• JTF_NOTE_ID — primary note identifier; the join key to both note header and note text.
• NOTES — the translatable note body text from JTF_NOTES_TL.
• ENTERED_BY — the party or user who entered the note, recorded on the note header.
• ENTERED_DATE — the date/time the note was entered; distinct from CREATION_DATE.
• CREATED_BY — audit column recording the creating user; used to join the resource extension for SOURCE_NAME.
• SOURCE_NAME — the name derived from JTF_RS_RESOURCE_EXTNS for the creating user, typically the resource/agent name.
• ENTERED_BY_NAME — the exposed column corresponding to the name associated with the note's originator. This column is the direct answer to searches for “entered_by_name” and is the descriptor most commonly requested in note reports.
• SOURCE_OBJECT_ID / SOURCE_OBJECT_CODE / SOURCE_OBJECT_MEANING — the identifiable source record and its translated object-type meaning.
• NOTE_TYPE / NOTE_STATUS — classification and workflow status of the note.
• CREATION_DATE / LAST_UPDATE_LOGIN — standard audit columns.

Common Use Cases and Queries

A typical reporting need is to list notes for a source object with the name of the person who entered each note:

• Agent note-history reports filtered by SOURCE_OBJECT_CODE and SOURCE_OBJECT_ID.
• Audit extracts showing ENTERED_BY, ENTERED_BY_NAME, ENTERED_DATE, and NOTE_STATUS.
• Integration feeds retrieving note text in the session language for a given object type.

SELECT jtf_note_id,
       source_object_code,
       source_object_id,
       note_type,
       note_status,
       entered_by,
       entered_by_name,
       entered_date,
       notes
FROM   ast_notes_lite_vl
WHERE  source_object_code = :object_code
AND    source_object_id   = :object_id
ORDER BY entered_date DESC;

To aggregate note activity by originator:

SELECT entered_by_name, COUNT(*) note_count
FROM   ast_notes_lite_vl
WHERE  entered_date >= :from_date
GROUP BY entered_by_name
ORDER BY note_count DESC;

Because the resource join is outer and the translation joins are language-bound, queries should be run in a session whose language has corresponding JTF_NOTES_TL and JTF_OBJECTS_TL rows; otherwise NOTES and SOURCE_OBJECT_MEANING may return null.