Search Results cs_sr_contract_notes_v

Overview

CS_SR_CONTRACT_NOTES_V is a service module (CS) database view owned by the APPS schema in Oracle E-Business Suite. It serves as the base view for the notes section of the Workbench tab, presenting contract-related notes associated with service incidents. The view consolidates note data held in the common JTF Notes repository with contract service information from the incidents table, translating internal lookup codes into user-facing meanings and resolving the creator's user name from FND_USER.

Within the ETRM reporting and integration layer, this view functions as a denormalized, read-only projection that shields downstream consumers from the multi-table join logic required to assemble contract notes. Reporting tools, Oracle Forms-based workbench screens, and external integrations can query the view directly rather than reconstructing the joins across JTF_NOTES_B, JTF_NOTES_TL, CS_INCIDENTS_ALL_B, and the lookup views. The view exposes a constant TRUE_STMT column set to 'T' and a constant RECORD_SOURCE_TYPE of 'JTF_CONTRACT_NOTE', which act as literal discriminators for downstream logic and are the tokens commonly surfaced when object metadata is searched.

Underlying Base Objects

The view is defined over the following documented objects: CS_INCIDENTS_ALL_B, CS_LOOKUPS (a view), FND_GLOBAL (a package), FND_LOOKUPS (a view), FND_USER, JTF_NOTES_B, JTF_NOTES_TL, and DBMS_LOB.

• JTF_NOTES_B — stores the note header, note type, source object identifier and code, status, and audit columns.
• JTF_NOTES_TL — the translation table supplying the note text; joined on the note identifier and restricted to the session language via USERENV('LANG').
• CS_INCIDENTS_ALL_B — supplies the incident identifier, contract service identifier, and contract number; joined on NOTE.SOURCE_OBJECT_ID = INC.CONTRACT_SERVICE_ID with SOURCE_OBJECT_CODE equal to 'OKS_COV_NOTE'.
• FND_USER — resolves CREATED_BY to a user name.
• FND_LOOKUPS — joined twice (outer joins) to translate note type and note status codes into meanings.
• CS_LOOKUPS — filtered to lookup type CS_SR_SOURCE_TYPE and code CONTRACT to construct the SOURCE concatenation.
• DBMS_LOB — used via GETLENGTH on NOTES_DETAIL to derive the IS_DETAIL flag.

Key Columns

• INCIDENT_ID — the service incident associated with the note.
• ID — the JTF note identifier, aliased from NOTE.JTF_NOTE_ID.
• APP_OBJ_ID / APP_OBJ_CODE — the source object identifier and code from the note record.
• NOTE_TYPE_CODE / NOTE_TYPE_MEANING — the note type code and its lookup meaning.
• NOTE — the note text in the session language.
• TRUE_STMT — a constant value of 'T'.
• NOTE_STATUS / NOTE_STATUS_MEANING — status code and its translated meaning.
• OBJ_ID / OBJ_NUMBER — the contract service identifier and contract number.
• USER_NAME — name of the user who created the note.
• SOURCE — a concatenation of the contract lookup meaning and contract number.
• IS_DETAIL — 'Y' when detailed note text exists, otherwise 'N'.

Common Use Cases and Queries

Typical uses include workbench display of contract notes, audit listings of notes by contract or creator, and integration extracts feeding external repositories.

SELECT incident_id, id, note_type_meaning, note, user_name
FROM   apps.cs_sr_contract_notes_v
WHERE  obj_number = :contract_number
ORDER BY creation_date DESC;

SELECT source, COUNT(*) note_count
FROM   apps.cs_sr_contract_notes_v
WHERE  true_stmt = 'T'
GROUP BY source;

The TRUE_STMT column is frequently referenced as a constant filter token in metadata searches and in tooling that validates the view's structure.