Search Results cs_sr_all_notes_v

Overview

CS_SR_ALL_NOTES_V is a Service (CS) module view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It functions as the base view powering the notes section of the Service Request workbench tab, consolidating note records attached to service requests and their associated tasks into a single, uniformly structured result set. The view is defined with a UNION ALL, combining notes whose SOURCE_OBJECT_CODE is 'SR' (service request notes stored in JTF_NOTES_B) with notes attached to JTF_TASKS_B task records, and tagging each row with a RECORD_SOURCE_TYPE of either 'JTF_SR_NOTE' or 'JTF_TASK_NOTE'. Because it decodes lookup meanings inline—note type, note status, and source type—it is well suited to reporting, OAF-based workbench rendering, and integration extraction where human-readable values are required without additional joins from the calling layer.

Underlying Base Objects

The documented base objects include JTF_NOTES_B and JTF_NOTES_TL, which hold the note header and translated note text; CS_INCIDENTS_ALL_B, the service request header table supplying INCIDENT_ID and INCIDENT_NUMBER; CS_LOOKUPS, a view providing the source type meaning used to build the SOURCE column; FND_LOOKUPS, joined twice to resolve note type and note status meanings; FND_USER, supplying the creator's user name; and JTF_TASKS_B with JTF_TASK_ASSIGNMENTS for the task-based branch of the union. The view also references CSF_DEBRIEF_HEADERS, CSI_ITEM_INSTANCES, FND_GLOBAL, and DBMS_LOB. Every join to the lookup tables uses the Oracle outer-join operator (+), so notes with unmapped lookup codes are retained rather than dropped. Language filtering is applied through NOTE_TL.LANGUAGE = USERENV('LANG'), ensuring each session sees translated note text. DBMS_LOB.GETLENGTH is used against NOTES_DETAIL to set the IS_DETAIL flag.

Key Columns

• INCIDENT_ID — the service request identifier from CS_INCIDENTS_ALL_B; also aliased as OBJ_ID, anchoring the note to its request.
• ID — the JTF_NOTE_ID of the note record.
• APP_OBJ_ID / APP_OBJ_CODE — the source object identifier and code carried from the note record.
• NOTE_TYPE_CODE / NOTE_TYPE_MEANING — the note type lookup code and its 'JTF_NOTE_TYPE' meaning.
• NOTE — the translated note text from JTF_NOTES_TL.
• NOTE_STATUS / NOTE_STATUS_MEANING — the status code and its 'JTF_NOTE_STATUS' meaning.
• RECORD_SOURCE_TYPE — 'JTF_SR_NOTE' or 'JTF_TASK_NOTE', distinguishing request notes from task notes.
• OBJ_NUMBER — the incident number or task number depending on the union branch.
• USER_NAME — creator from FND_USER, derived from CREATED_BY.
• SOURCE — concatenation of CS_LOOKUPS meaning and object number.
• IS_DETAIL — 'Y' when NOTES_DETAIL contains content, otherwise 'N'.
• TRUE_STMT — literal 'T', a constant placeholder used by the workbench for conditional rendering.
• Standard audit columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical uses include Service Request workbench note panels, audit listings of notes by request or task, and extraction feeds that require decoded note type and status values.

• Listing all notes for a request:
  SELECT id, record_source_type, note_type_meaning, note, user_name FROM cs_sr_all_notes_v WHERE incident_id = :p_incident_id ORDER BY creation_date DESC;
• Finding notes containing detail text:
  SELECT incident_id, obj_number, note_type_meaning FROM cs_sr_all_notes_v WHERE is_detail = 'Y';
• Separating request notes from task notes:
  SELECT record_source_type, COUNT(*) FROM cs_sr_all_notes_v GROUP BY record_source_type;
• Notes by status for reporting:
  SELECT note_status_meaning, COUNT(*) FROM cs_sr_all_notes_v WHERE record_source_type = 'JTF_SR_NOTE' GROUP BY note_status_meaning;