Search Results jtf_note_status




Overview
Explore in AI Chat

Overview

APPS.CS_SR_ALL_NOTES_V is a consolidated Oracle E-Business Suite reporting view that exposes Service Request (SR) and task notes from the CRM/JTF note repository in a denormalized, user-readable form. It is defined in the APPS schema and is part of the Service (CS) module's note-reporting infrastructure. The view draws together note text, note type, note status, source object, and creator metadata, producing a single row per note regardless of whether the note is attached to a service request incident or a task.

Its principal functional value is the resolution of reference codes into descriptive meanings. In particular, the view joins FND_LOOKUPS against the lookup type JTF_NOTE_STATUS to derive the NOTE_STATUS_MEANING column, and against JTF_NOTE_TYPE to derive NOTE_TYPE_MEANING. Because it also returns the raw NOTE_STATUS code, the view serves both lookup-code-driven logic and human-readable reporting. The view is structured as a UNION ALL over two branches, distinguished by the RECORD_SOURCE_TYPE column, which takes the values JTF_SR_NOTE or JTF_TASK_NOTE.

Underlying Base Objects

The view is defined over a mixture of base tables, synonyms, and reference views. The note content itself originates from JTF_NOTES_B (the base note entity) and JTF_NOTES_TL (the translated note text and detail), joined on JTF_NOTE_ID. The NOTE_TL join is restricted to the session language via USERENV('LANG'), ensuring only the appropriate translation row is returned.

Source context is supplied by CS_INCIDENTS_ALL_B (service request incidents) in the SR branch and JTF_TASKS_B (with JTF_TASK_ASSIGNMENTS documented as a referenced object) in the task branch. Both branches join FND_USER to resolve CREATED_BY to USER_NAME. Reference meanings are resolved through the FND_LOOKUPS view for note type and note status, and through CS_LOOKUPS for the source meaning (CS_SR_SOURCE_TYPE). The DBMS_LOB package is invoked via GETLENGTH to determine whether a note carries extended detail text. FND_GLOBAL is listed as a referenced package, consistent with the language/environment functions used in the definition.

Key Columns

  • INCIDENT_ID / OBJ_ID — the service request incident identifier; OBJ_NUMBER carries the incident number.
  • ID — the underlying JTF_NOTE_ID of the note.
  • APP_OBJ_ID / APP_OBJ_CODE — the source object the note is attached to and its object code.
  • NOTE_TYPE_CODE / NOTE_TYPE_MEANING — the note type code and its JTF_NOTE_TYPE lookup meaning.
  • NOTE_STATUS / NOTE_STATUS_MEANING — the raw note status code and its JTF_NOTE_STATUS lookup meaning; this pair is the object of interest for searches on jtf_note_status.
  • NOTES — the translated note text.
  • IS_DETAIL — Y/N flag indicating whether extended detail (NOTES_DETAIL) exists.
  • RECORD_SOURCE_TYPE — JTF_SR_NOTE or JTF_TASK_NOTE, identifying the originating branch.
  • SOURCE — a concatenation of the lookup meaning and the object number.
  • Audit columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, and USER_NAME.

Common Use Cases and Queries

The view supports note auditing, status reporting, and service-request dashboards. A frequent requirement is to list all notes for a given service request with their status meaning:

  • Retrieve notes by incident, ordering by creation date, exposing NOTE_STATUS_MEANING.
  • Report open or unresolved notes by filtering on NOTE_STATUS codes derived from JTF_NOTE_STATUS.
  • Distinguish SR notes from task notes using RECORD_SOURCE_TYPE.
  • Identify notes carrying extended detail using IS_DETAIL = 'Y'.

Sample query:

SELECT incident_id, id, note_type_meaning, note_status_meaning, notes, user_name, creation_date FROM apps.cs_sr_all_notes_v WHERE incident_id = :p_incident_id AND record_source_type = 'JTF_SR_NOTE' ORDER BY creation_date DESC;

Because translation and lookup joins use outer-join syntax on FND_LOOKUPS, notes with unmapped or null codes are still returned with null meanings, which should be anticipated in reports. Access is typically granted through the APPS schema, and the view should be treated as read-only for reporting and integration purposes.