Search Results jtf_contract_note

Overview

The APPS.CS_SR_CONTRACT_NOTES_V view is a reporting and integration construct within Oracle E-Business Suite Release 12.1.1 and 12.2.2 that exposes notes attached to service contracts and contract coverage lines. It is defined in the APPS schema and functions as a denormalized projection of the JTF Notes infrastructure, filtered specifically to records whose SOURCE_OBJECT_CODE equals 'OKS_COV_NOTE'. This linkage ties each note to a contract coverage or service line maintained in Oracle Service Contracts, and the resulting rows are joined to the corresponding service request incident, allowing contract notes to appear in the context of a service request. The view resolves lookup meanings for note type and note status, resolves the creating user name, and derives presentation attributes such as the source label and whether an extended detail body exists. Because the defining predicate restricts rows to the OKS_COV_NOTE source object code, the view is the appropriate access path when contract coverage notes must be surfaced alongside CS_INCIDENTS_ALL_B incident data, which makes it central to service request note-display frameworks and to integration interfaces that import or export contract notes.

Underlying Base Objects

The view is defined over the following documented objects: JTF_NOTES_B (synonym) for the note header, JTF_NOTES_TL (synonym) for the translated note text and detail, CS_INCIDENTS_ALL_B (synonym) supplying the incident and contract service identifiers, FND_USER (synonym) for the creator, FND_LOOKUPS (view) for note type and note status meanings, CS_LOOKUPS (view) for the service request source type that produces the SOURCE label, FND_GLOBAL (package, via userenv('LANG')) to restrict translated rows to the session language, and DBMS_LOB (synonym) for measuring the length of the notes detail column. The cardinal relationship is one row per note per incident per language, driven by JTF_NOTES_B aliased as NOTE joined to JTF_NOTES_TL on JTF_NOTE_ID. The join to CS_INCIDENTS_ALL_B is made on NOTE.SOURCE_OBJECT_ID = INC.CONTRACT_SERVICE_ID, gated by the literal SOURCE_OBJECT_CODE = 'OKS_COV_NOTE'. The lookup joins to FND_LOOKUPS are outer joins, so a note without a valid type or status code is still returned with a null meaning. The CS_LOOKUPS join is an inner join on lookup type CS_SR_SOURCE_TYPE and code CONTRACT, which produces the composite SOURCE value.

Key Columns

• INCIDENT_ID — the service request identifier from CS_INCIDENTS_ALL_B.
• ID — the JTF_NOTE_ID of the note.
• APP_OBJ_ID / APP_OBJ_CODE — the source object identifier and its code, which is 'OKS_COV_NOTE' for every row.
• NOTE_TYPE_CODE / NOTE_TYPE_MEANING — the note type code and its FND_LOOKUPS meaning.
• NOTE — the translated note text from JTF_NOTES_TL.
• NOTE_STATUS / NOTE_STATUS_MEANING — the status code and its lookup meaning.
• OBJ_ID / OBJ_NUMBER — the contract service identifier and contract number from CS_INCIDENTS_ALL_B.
• SOURCE — the concatenation of the CS_LOOKUPS meaning and the contract number.
• IS_DETAIL — 'Y' when NOTES_DETAIL has content, otherwise 'N'.
• USER_NAME — the creating user from FND_USER.
• RECORD_SOURCE_TYPE — the constant 'JTF_CONTRACT_NOTE'.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical uses include populating the notes region of a service request or contract coverage screen, extracting contract notes for a data warehouse or interface staging table, and auditing note status distributions. A query listing notes for a given incident can be written as follows:

SELECT ID, NOTE_TYPE_MEANING, NOTE_STATUS_MEANING,
       NOTE, OBJ_NUMBER, SOURCE, IS_DETAIL, USER_NAME
FROM   APPS.CS_SR_CONTRACT_NOTES_V
WHERE  INCIDENT_ID = :p_incident_id
ORDER  BY CREATION_DATE DESC;

To count contract notes by status for a contract number:

SELECT NOTE_STATUS_MEANING, COUNT(*) note_count
FROM   APPS.CS_SR_CONTRACT_NOTES_V
WHERE  OBJ_NUMBER = :p_contract_number
GROUP  BY NOTE_STATUS_MEANING;

Because the view returns translated text only for the session language, callers requiring multiple languages must query JTF_NOTES_TL directly rather than relying on this view. Similarly, only notes whose source object code is exactly OKS_COV_NOTE are exposed; notes attached to other source objects are excluded by design.