[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
, p_last_update_date IN DATE DEFAULT TO_DATE('1','j')
, p_last_updated_by IN NUMBER DEFAULT FND_GLOBAL.USER_ID
, p_creation_date IN DATE DEFAULT TO_DATE('1','j')
, p_created_by IN NUMBER DEFAULT FND_GLOBAL.USER_ID
, p_last_update_login IN NUMBER DEFAULT FND_GLOBAL.LOGIN_ID
, p_attribute1 IN VARCHAR2 DEFAULT NULL
, p_attribute2 IN VARCHAR2 DEFAULT NULL
, p_attribute3 IN VARCHAR2 DEFAULT NULL
, p_attribute4 IN VARCHAR2 DEFAULT NULL
, p_attribute5 IN VARCHAR2 DEFAULT NULL
, p_attribute6 IN VARCHAR2 DEFAULT NULL
, p_attribute7 IN VARCHAR2 DEFAULT NULL
, p_attribute8 IN VARCHAR2 DEFAULT NULL
, p_attribute9 IN VARCHAR2 DEFAULT NULL
, p_attribute10 IN VARCHAR2 DEFAULT NULL
, p_attribute11 IN VARCHAR2 DEFAULT NULL
, p_attribute12 IN VARCHAR2 DEFAULT NULL
, p_attribute13 IN VARCHAR2 DEFAULT NULL
, p_attribute14 IN VARCHAR2 DEFAULT NULL
, p_attribute15 IN VARCHAR2 DEFAULT NULL
, p_context IN VARCHAR2 DEFAULT NULL
, p_note_type IN VARCHAR2 DEFAULT NULL
, p_jtf_note_contexts_tab IN jtf_note_contexts_tbl_type
DEFAULT jtf_note_contexts_tab_dflt
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Create_note';
, p_last_update_date => p_last_update_date
, p_last_updated_by => p_last_updated_by
, p_creation_date => p_creation_date
, p_created_by => p_created_by
, p_last_update_login => p_last_update_login
, p_attribute1 => p_attribute1
, p_attribute2 => p_attribute2
, p_attribute3 => p_attribute3
, p_attribute4 => p_attribute4
, p_attribute5 => p_attribute5
, p_attribute6 => p_attribute6
, p_attribute7 => p_attribute7
, p_attribute8 => p_attribute8
, p_attribute9 => p_attribute9
, p_attribute10 => p_attribute10
, p_attribute11 => p_attribute11
, p_attribute12 => p_attribute12
, p_attribute13 => p_attribute13
, p_attribute14 => p_attribute14
, p_attribute15 => p_attribute15
, p_context => p_context
, p_note_type => p_note_type
, p_jtf_note_contexts_tab => p_jtf_note_contexts_tab
, p_use_AOL_security => 'F'
);
PROCEDURE Update_note
------------------------------------------------------------------------------
-- Update_note
-- Updates a note record in the JTF_NOTES table
------------------------------------------------------------------------------
( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 DEFAULT 'F'
, p_commit IN VARCHAR2 DEFAULT 'F'
, p_validation_level IN NUMBER DEFAULT 100
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_jtf_note_id IN NUMBER
, p_entered_by IN NUMBER DEFAULT FND_GLOBAL.USER_ID
, p_last_updated_by IN NUMBER
, p_last_update_date IN DATE DEFAULT SYSDATE
, p_last_update_login IN NUMBER DEFAULT NULL
, p_notes IN VARCHAR2 DEFAULT CHR(0)
, p_notes_detail IN VARCHAR2 DEFAULT CHR(0)
, p_append_flag IN VARCHAR2 DEFAULT CHR(0)
, p_note_status IN VARCHAR2 DEFAULT 'I'
, p_note_type IN VARCHAR2 DEFAULT CHR(0)
, p_jtf_note_contexts_tab IN jtf_note_contexts_tbl_type
DEFAULT jtf_note_contexts_tab_dflt
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_note';
SAVEPOINT update_note_pub;
Secure_Update_note( p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => fnd_api.g_false
, p_validation_level => p_validation_level
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_jtf_note_id => p_jtf_note_id
, p_entered_by => p_entered_by
, p_last_updated_by => p_last_updated_by
, p_last_update_date => p_last_update_date
, p_last_update_login => p_last_update_login
, p_notes => p_notes
, p_notes_detail => p_notes_detail
, p_append_flag => p_append_flag
, p_note_status => p_note_status
, p_note_type => p_note_type
, p_jtf_note_contexts_tab => p_jtf_note_contexts_tab
, p_use_AOL_security => 'F'
);
ROLLBACK TO update_note_pub;
ROLLBACK TO update_note_pub;
ROLLBACK TO update_note_pub;
END Update_note;
SELECT 'x' INTO l_dummy
FROM fnd_lookup_values
WHERE lookup_code = p_note_type
AND lookup_type = 'JTF_NOTE_TYPE'
AND language = USERENV('LANG');
, p_last_update_date IN DATE
, p_last_updated_by IN NUMBER
, p_creation_date IN DATE
, p_created_by IN NUMBER DEFAULT FND_GLOBAL.USER_ID
, p_last_update_login IN NUMBER DEFAULT FND_GLOBAL.LOGIN_ID
, p_note_context_type_id IN NUMBER DEFAULT 9.99E125
, p_note_context_type IN VARCHAR2 DEFAULT CHR(0)
, x_note_context_id OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Create_note_context';
l_last_update_date DATE;
l_insert_failure EXCEPTION;
CURSOR c_inserted
(b_note_context_id IN NUMBER
)IS SELECT 'x'
FROM jtf_note_contexts
WHERE note_context_id = b_note_context_id;
)IS SELECT note_context_id
FROM jtf_note_contexts
WHERE jtf_note_id = b_jtf_note_id
AND note_context_type = b_note_context_type
AND note_context_type_id = b_note_context_type_id;
IF p_last_update_date = fnd_api.g_miss_date
THEN
l_last_update_date := (SYSDATE);
l_last_update_date := p_last_update_date;
INSERT INTO jtf_note_contexts
( note_context_id
, jtf_note_id
, note_context_type_id
, note_context_type
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
)
VALUES
( jtf_notes_s.NEXTVAL
, p_jtf_note_id
, p_note_context_type_id
, p_note_context_type
, l_last_update_date
, p_last_updated_by
, l_creation_date
, p_created_by
, p_last_update_login
)
RETURNING note_context_id INTO l_note_context_id;
OPEN c_inserted(l_note_context_id);
FETCH c_inserted INTO l_dummy;
IF (c_inserted%NOTFOUND)
THEN
-- Insert failed, raise error
IF (c_inserted%ISOPEN)
THEN
CLOSE c_inserted;
RAISE l_insert_failure;
IF (c_inserted%ISOPEN)
THEN
CLOSE c_inserted;
IF (c_inserted%ISOPEN)
THEN
CLOSE c_inserted;
PROCEDURE Update_note_context
------------------------------------------------------------------------------
-- Procedure : Update_note_context
--
-- Updates a context record in the JTF_NOTE_CONTEXTS table
------------------------------------------------------------------------------
( p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full
, x_return_status OUT NOCOPY VARCHAR2
, p_note_context_id IN NUMBER
, p_jtf_note_id IN NUMBER
, p_note_context_type_id IN NUMBER
, p_note_context_type IN VARCHAR2
, p_last_updated_by IN NUMBER
, p_last_update_date IN DATE DEFAULT SYSDATE
, p_last_update_login IN NUMBER DEFAULT NULL
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Note_Context';
IS SELECT *
FROM JTF_NOTE_CONTEXTS
WHERE note_context_id = p_note_context_id
FOR UPDATE OF note_context_id;
UPDATE JTF_NOTE_CONTEXTS
SET last_updated_by = p_last_updated_by
, last_update_date = p_last_update_date
, last_update_login = p_last_update_login
, note_context_type_id = l_note_context_type_id
, note_context_type = l_note_context_type
WHERE CURRENT OF lrec_note_context;
END Update_note_Context;
l_select_id VARCHAR2(200);
IS SELECT select_id
, from_table
, where_clause
FROM jtf_objects_vl a
, jtf_object_usages b
WHERE a.object_code = p_object_type_code
AND a.object_code = b.object_code
AND b.object_user_code = 'NOTES';
FETCH cur_object INTO l_select_id,l_tablename,l_where_clause ;
v_create_string := 'SELECT COUNT(*) FROM '||l_tablename||
' WHERE '||l_select_id||' = :object_type_id ';
v_create_string := 'SELECT COUNT(*) FROM '||l_tablename||
' WHERE '||l_where_clause||
' AND '||l_select_id||' = :object_type_id ';
IS SELECT notes_detail
FROM jtf_notes_tl
WHERE jtf_note_id = x_jtf_note_id
FOR UPDATE;
SELECT notes_detail
INTO lob_loc
FROM jtf_notes_vl
WHERE jtf_note_id = x_jtf_note_id;
IS SELECT user_id
FROM fnd_user
WHERE user_id = p_entered_by
AND NVL (end_date, SYSDATE) >= SYSDATE
AND NVL (start_date, SYSDATE) <= SYSDATE;
PROCEDURE Secure_Delete_note
-- --------------------------------------------------------------------------
-- Start of notes
-- API Name : Delete_Note will only work when the user is granted the
-- JTF_NOTE_DELETE privilege through AOL security framework
-- Type : Public
-- Usage : Deletes a note record in the table JTF_NOTES_B/JTF_NOTES_TL
-- and JTF_NOTE_CONTEXTS
-- Pre-reqs : None
-- Parameters :
-- p_api_version IN NUMBER Required
-- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
-- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
-- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
-- x_return_status OUT VARCHAR2 Required
-- x_msg_count OUT NUMBER Required
-- x_msg_data OUT VARCHAR2 Required
-- p_jtf_note_id IN NUMBER Required Primary key of the note record
-- p_use_AOL_security IN VARCHAR2 Optional Default FND_API.G_TRUE
--
-- Version : Initial version 1.0
--
--
-- End of notes
-- --------------------------------------------------------------------------
( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 DEFAULT 'F'
, p_commit IN VARCHAR2 DEFAULT 'F'
, p_validation_level IN NUMBER DEFAULT 100
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_jtf_note_id IN NUMBER
, p_use_AOL_security IN VARCHAR2 DEFAULT 'T'
)IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_note';
l_note_delete NUMBER;
SAVEPOINT delete_note_pub;
, p_function => JTF_NOTES_SECURITY_PVT.G_FUNCTION_DELETE
, p_object_name => JTF_NOTES_SECURITY_PVT.G_OBJECT_NOTE
, p_instance_pk1_value => p_jtf_note_id
, x_grant => l_note_delete
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_DELETE');
IF (l_note_delete = 0)
THEN
FND_MESSAGE.SET_NAME('JTF', 'JTF_FUNCTION_NOT_GRANTED'); -- Security &FUNCTION function was not granted
FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_DELETE');
SELECT source_object_code
, source_object_id
INTO l_source_object_code
, l_source_object_id
FROM jtf_notes_b
WHERE jtf_note_id = p_jtf_note_id;
DELETE FROM jtf_note_contexts WHERE jtf_note_id = p_jtf_note_id;
DELETE FROM jtf_notes_tl WHERE jtf_note_id = p_jtf_note_id;
DELETE FROM jtf_notes_b WHERE jtf_note_id = p_jtf_note_id;
JTF_NOTES_EVENTS_PVT.RaiseDeleteNote
( p_NoteID => p_jtf_note_id
, p_SourceObjectCode => l_source_object_code
, p_SourceObjectID => l_source_object_id
);
ROLLBACK TO delete_note_pub;
ROLLBACK TO delete_note_pub;
END Secure_Delete_Note;
, p_last_update_date IN DATE DEFAULT TO_DATE('1','j')
, p_last_updated_by IN NUMBER DEFAULT fnd_global.user_id
, p_creation_date IN DATE DEFAULT TO_DATE('1','j')
, p_created_by IN NUMBER DEFAULT fnd_global.user_id
, p_last_update_login IN NUMBER DEFAULT fnd_global.login_id
, p_attribute1 IN VARCHAR2 DEFAULT NULL
, p_attribute2 IN VARCHAR2 DEFAULT NULL
, p_attribute3 IN VARCHAR2 DEFAULT NULL
, p_attribute4 IN VARCHAR2 DEFAULT NULL
, p_attribute5 IN VARCHAR2 DEFAULT NULL
, p_attribute6 IN VARCHAR2 DEFAULT NULL
, p_attribute7 IN VARCHAR2 DEFAULT NULL
, p_attribute8 IN VARCHAR2 DEFAULT NULL
, p_attribute9 IN VARCHAR2 DEFAULT NULL
, p_attribute10 IN VARCHAR2 DEFAULT NULL
, p_attribute11 IN VARCHAR2 DEFAULT NULL
, p_attribute12 IN VARCHAR2 DEFAULT NULL
, p_attribute13 IN VARCHAR2 DEFAULT NULL
, p_attribute14 IN VARCHAR2 DEFAULT NULL
, p_attribute15 IN VARCHAR2 DEFAULT NULL
, p_context IN VARCHAR2 DEFAULT NULL
, p_note_type IN VARCHAR2 DEFAULT NULL
, p_jtf_note_contexts_tab IN jtf_note_contexts_tbl_type
DEFAULT jtf_note_contexts_tab_dflt
, p_use_AOL_security IN VARCHAR2 DEFAULT 'T'
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Secure_Create_note';
l_last_update_date DATE := p_last_update_date;
l_last_updated_by NUMBER := p_last_updated_by;
l_grant_select NUMBER := 1;
l_grant_select_type NUMBER := 1;
IS SELECT JTF_NOTES_S.NEXTVAL
FROM DUAL;
CURSOR l_insert_check_csr
IS SELECT 'x'
FROM JTF_NOTES_B
WHERE jtf_note_id = l_jtf_note_id;
l_insert_failure EXCEPTION;
, p_last_update_date => p_last_update_date
, p_last_updated_by => p_last_updated_by
, p_creation_date => p_creation_date
, p_created_by => p_created_by
, p_last_update_login => p_last_update_login
, p_attribute1 => p_attribute1
, p_attribute2 => p_attribute2
, p_attribute3 => p_attribute3
, p_attribute4 => p_attribute4
, p_attribute5 => p_attribute5
, p_attribute6 => p_attribute6
, p_attribute7 => p_attribute7
, p_attribute8 => p_attribute8
, p_attribute9 => p_attribute9
, p_attribute10 => p_attribute10
, p_attribute11 => p_attribute11
, p_attribute12 => p_attribute12
, p_attribute13 => p_attribute13
, p_attribute14 => p_attribute14
, p_attribute15 => p_attribute15
, p_context => p_context
, p_note_type => p_note_type
, p_jtf_note_contexts_tab => p_jtf_note_contexts_tab
, x_return_status => l_return_status
);
, p_last_update_date => p_last_update_date
, p_last_updated_by => p_last_updated_by
, p_creation_date => p_creation_date
, p_created_by => p_created_by
, p_last_update_login => p_last_update_login
, p_attribute1 => p_attribute1
, p_attribute2 => p_attribute2
, p_attribute3 => p_attribute3
, p_attribute4 => p_attribute4
, p_attribute5 => p_attribute5
, p_attribute6 => p_attribute6
, p_attribute7 => p_attribute7
, p_attribute8 => p_attribute8
, p_attribute9 => p_attribute9
, p_attribute10 => p_attribute10
, p_attribute11 => p_attribute11
, p_attribute12 => p_attribute12
, p_attribute13 => p_attribute13
, p_attribute14 => p_attribute14
, p_attribute15 => p_attribute15
, p_context => p_context
, p_note_type => p_note_type
, p_jtf_note_contexts_tab => p_jtf_note_contexts_tab
, x_return_status => l_return_status
);
SELECT jtf_note_id
INTO l_jtf_note_id
FROM jtf_notes_b
WHERE jtf_note_id = p_jtf_note_id;
IF p_last_update_date = fnd_api.g_miss_date
THEN
l_last_update_date := (SYSDATE);
l_last_update_date := p_last_update_date;
, x_grant => l_grant_select
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
IF (l_grant_select = 0)
THEN
FND_MESSAGE.SET_NAME('JTF', 'JTF_FUNCTION_NOT_GRANTED'); -- Security &FUNCTION function was not granted
, x_grant => l_grant_select_type
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
IF (l_grant_select = 0)
THEN
FND_MESSAGE.SET_NAME('JTF', 'JTF_FUNCTION_NOT_GRANTED'); -- Security &FUNCTION function was not granted
JTF_NOTES_PKG.INSERT_ROW
( x_rowid => l_rowid
, x_jtf_note_id => l_jtf_note_id
, x_source_object_code => p_source_object_code
, x_note_status => l_note_status
, x_entered_by => p_entered_by
, x_entered_date => l_entered_date
, x_note_type => p_note_type
, x_attribute1 => p_attribute1
, x_attribute2 => p_attribute2
, x_attribute3 => p_attribute3
, x_attribute4 => p_attribute4
, x_attribute5 => p_attribute5
, x_attribute6 => p_attribute6
, x_attribute7 => p_attribute7
, x_attribute8 => p_attribute8
, x_attribute9 => p_attribute9
, x_attribute10 => p_attribute10
, x_attribute11 => p_attribute11
, x_attribute12 => p_attribute12
, x_attribute13 => p_attribute13
, x_attribute14 => p_attribute14
, x_attribute15 => p_attribute15
, x_context => p_context
, x_parent_note_id => l_parent_note_id
, x_source_object_id => p_source_object_id
, x_notes => l_notes
, x_notes_detail => p_notes_detail
, x_creation_date => l_creation_date
, x_created_by => p_created_by
, x_last_update_date => l_last_update_date
, x_last_updated_by => p_last_updated_by
, x_last_update_login => p_last_update_login
);
OPEN l_insert_check_csr;
FETCH l_insert_check_csr INTO l_dummy;
IF (l_insert_check_csr%notfound)
THEN
CLOSE l_insert_check_csr;
RAISE l_insert_failure;
CLOSE l_insert_check_csr;
, p_last_update_date => p_jtf_note_contexts_tab(i).last_update_date
, p_last_updated_by => p_jtf_note_contexts_tab(i).last_updated_by
, p_creation_date => p_jtf_note_contexts_tab(i).creation_date
, p_created_by => p_jtf_note_contexts_tab(i).created_by
, p_last_update_login => p_jtf_note_contexts_tab(i).last_update_login
, p_note_context_type_id => p_jtf_note_contexts_tab(i).note_context_type_id
, p_note_context_type => p_jtf_note_contexts_tab(i).note_context_type
, x_note_context_id => l_note_context_id
);
, p_last_update_date => l_last_update_date
, p_last_updated_by => p_last_updated_by
, p_creation_date => l_creation_date
, p_created_by => p_created_by
, p_last_update_login => p_last_update_login
, p_attribute1 => p_attribute1
, p_attribute2 => p_attribute2
, p_attribute3 => p_attribute3
, p_attribute4 => p_attribute4
, p_attribute5 => p_attribute5
, p_attribute6 => p_attribute6
, p_attribute7 => p_attribute7
, p_attribute8 => p_attribute8
, p_attribute9 => p_attribute9
, p_attribute10 => p_attribute10
, p_attribute11 => p_attribute11
, p_attribute12 => p_attribute12
, p_attribute13 => p_attribute13
, p_attribute14 => p_attribute14
, p_attribute15 => p_attribute15
, p_context => p_context
, p_note_type => p_note_type
, p_jtf_note_contexts_tab => p_jtf_note_contexts_tab
, x_return_status => l_return_status
, p_jtf_note_id => l_jtf_note_id
);
, p_last_update_date => l_last_update_date
, p_last_updated_by => p_last_updated_by
, p_creation_date => l_creation_date
, p_created_by => p_created_by
, p_last_update_login => p_last_update_login
, p_attribute1 => p_attribute1
, p_attribute2 => p_attribute2
, p_attribute3 => p_attribute3
, p_attribute4 => p_attribute4
, p_attribute5 => p_attribute5
, p_attribute6 => p_attribute6
, p_attribute7 => p_attribute7
, p_attribute8 => p_attribute8
, p_attribute9 => p_attribute9
, p_attribute10 => p_attribute10
, p_attribute11 => p_attribute11
, p_attribute12 => p_attribute12
, p_attribute13 => p_attribute13
, p_attribute14 => p_attribute14
, p_attribute15 => p_attribute15
, p_context => p_context
, p_note_type => p_note_type
, p_jtf_note_contexts_tab => p_jtf_note_contexts_tab
, x_return_status => l_return_status
, p_jtf_note_id => l_jtf_note_id
);
, p_last_update_date => p_last_update_date
, p_last_updated_by => p_last_updated_by
, p_creation_date => p_creation_date
)
THEN
l_bind_data_id := JTF_USR_HKS.Get_bind_data_id;
PROCEDURE Secure_Update_note
------------------------------------------------------------------------------
-- Update_note
-- Updates a note record in the JTF_NOTES table
------------------------------------------------------------------------------
( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 DEFAULT 'F'
, p_commit IN VARCHAR2 DEFAULT 'F'
, p_validation_level IN NUMBER DEFAULT 100
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_jtf_note_id IN NUMBER
, p_entered_by IN NUMBER DEFAULT fnd_global.user_id
, p_last_updated_by IN NUMBER
, p_last_update_date IN DATE DEFAULT SYSDATE
, p_last_update_login IN NUMBER DEFAULT NULL
, p_notes IN VARCHAR2 DEFAULT CHR(0)
, p_notes_detail IN VARCHAR2 DEFAULT CHR(0)
, p_append_flag IN VARCHAR2 DEFAULT CHR(0)
, p_note_status IN VARCHAR2 DEFAULT 'I'
, p_note_type IN VARCHAR2 DEFAULT CHR(0)
, p_jtf_note_contexts_tab IN jtf_note_contexts_tbl_type
DEFAULT jtf_note_contexts_tab_dflt
, p_attribute1 IN VARCHAR2 DEFAULT CHR(0)
, p_attribute2 IN VARCHAR2 DEFAULT CHR(0)
, p_attribute3 IN VARCHAR2 DEFAULT CHR(0)
, p_attribute4 IN VARCHAR2 DEFAULT CHR(0)
, p_attribute5 IN VARCHAR2 DEFAULT CHR(0)
, p_attribute6 IN VARCHAR2 DEFAULT CHR(0)
, p_attribute7 IN VARCHAR2 DEFAULT CHR(0)
, p_attribute8 IN VARCHAR2 DEFAULT CHR(0)
, p_attribute9 IN VARCHAR2 DEFAULT CHR(0)
, p_attribute10 IN VARCHAR2 DEFAULT CHR(0)
, p_attribute11 IN VARCHAR2 DEFAULT CHR(0)
, p_attribute12 IN VARCHAR2 DEFAULT CHR(0)
, p_attribute13 IN VARCHAR2 DEFAULT CHR(0)
, p_attribute14 IN VARCHAR2 DEFAULT CHR(0)
, p_attribute15 IN VARCHAR2 DEFAULT CHR(0)
, p_context IN VARCHAR2 DEFAULT CHR(0)
, p_use_AOL_security IN VARCHAR2 DEFAULT 'T'
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Secure_Update_note';
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_update_date DATE;
l_note_update_primary NUMBER;
l_note_update_secondary NUMBER;
l_note_select_type NUMBER;
IS SELECT *
FROM JTF_NOTES_B
WHERE jtf_note_id = p_jtf_note_id
FOR UPDATE OF jtf_note_id NOWAIT;
IS SELECT *
FROM JTF_NOTES_TL
WHERE JTF_NOTE_ID = p_JTF_NOTE_ID
AND USERENV('LANG') = LANGUAGE;
SAVEPOINT update_note_pvt;
IF p_last_update_date = fnd_api.g_miss_date
THEN
l_last_update_date := SYSDATE;
l_last_update_date := p_last_update_date;
IF p_last_updated_by = fnd_api.g_miss_num
THEN
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_updated_by := p_last_updated_by;
IF p_last_update_login = fnd_api.g_miss_num
THEN
l_last_update_login := FND_GLOBAL.USER_ID;
l_last_update_login := p_last_update_login;
, 'Update Note'
, 'B'
, 'C'
)
THEN
jtf_notes_cuhk.update_note_pre
( p_api_version => l_api_version
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_jtf_note_id => p_jtf_note_id
, p_entered_by => l_entered_by
, p_last_updated_by => p_last_updated_by
, p_notes => l_notes
, p_notes_detail => l_notes_detail
, p_append_flag => p_append_flag
, p_note_status => l_note_status
, p_note_type => l_note_type
, x_return_status => l_return_status
);
, 'Update Note'
, 'B'
, 'V'
)
THEN
jtf_notes_vuhk.update_note_pre
( p_api_version => l_api_version
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_jtf_note_id => p_jtf_note_id
, p_entered_by => l_entered_by
, p_last_updated_by => p_last_updated_by
, p_notes => l_notes
, p_notes_detail => l_notes_detail
, p_append_flag => p_append_flag
, p_note_status => l_note_status
, p_note_type => l_note_type
, x_return_status => l_return_status
);
, p_function => JTF_NOTES_SECURITY_PVT.G_FUNCTION_UPDATE_NOTE
, p_object_name => JTF_NOTES_SECURITY_PVT.G_OBJECT_NOTE
, p_instance_pk1_value => p_jtf_note_id
, x_grant => l_note_update_primary
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_UPDATE_NOTE');
IF (l_note_update_primary = 0)
THEN
FND_MESSAGE.SET_NAME('JTF', 'JTF_FUNCTION_NOT_GRANTED'); -- Security &FUNCTION function was not granted
FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_UPDATE_NOTE');
, p_function => JTF_NOTES_SECURITY_PVT.G_FUNCTION_UPDATE_NOTE_DTLS
, p_object_name => JTF_NOTES_SECURITY_PVT.G_OBJECT_NOTE
, p_instance_pk1_value => p_jtf_note_id
, x_grant => l_note_update_primary
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_UPDATE_NOTE_DETAILS');
IF (l_note_update_primary = 0)
THEN
FND_MESSAGE.SET_NAME('JTF', 'JTF_FUNCTION_NOT_GRANTED'); -- Security &FUNCTION function was not granted
FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_UPDATE_NOTE_DETAILS');
, p_function => JTF_NOTES_SECURITY_PVT.G_FUNCTION_UPDATE_SEC
, p_object_name => JTF_NOTES_SECURITY_PVT.G_OBJECT_NOTE
, p_instance_pk1_value => p_jtf_note_id
, x_grant => l_note_update_secondary
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_UPDATE');
IF (l_note_update_secondary = 0)
THEN
FND_MESSAGE.SET_NAME('JTF', 'JTF_FUNCTION_NOT_GRANTED'); -- Security &FUNCTION function was not granted
FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_UPDATE_PRIMARY');
, x_grant => l_note_select_type
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_UPDATE');
IF (l_note_select_type = 0)
THEN
FND_MESSAGE.SET_NAME('JTF', 'JTF_FUNCTION_NOT_GRANTED'); -- Security &FUNCTION function was not granted
FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_TYPE_UPDATE');
UPDATE JTF_NOTES_B
SET last_updated_by = l_last_updated_by
, last_update_date = l_last_update_date
, last_update_login = l_last_update_login
, note_status = l_note_status
, note_type = l_note_type
, attribute1 = l_attribute1
, attribute2 = l_attribute2
, attribute3 = l_attribute3
, attribute4 = l_attribute4
, attribute5 = l_attribute5
, attribute6 = l_attribute6
, attribute7 = l_attribute7
, attribute8 = l_attribute8
, attribute9 = l_attribute9
, attribute10 = l_attribute10
, attribute11 = l_attribute11
, attribute12 = l_attribute12
, attribute13 = l_attribute13
, attribute14 = l_attribute14
, attribute15 = l_attribute15
, context = l_context
WHERE CURRENT OF l_com_csr;
UPDATE JTF_NOTES_TL
SET NOTES = l_notes
, NOTES_DETAIL = EMPTY_CLOB()
, LAST_UPDATE_DATE = l_last_update_date
, LAST_UPDATED_BY = l_last_updated_by
, LAST_UPDATE_LOGIN = l_last_update_login
, SOURCE_LANG = USERENV('LANG')
WHERE JTF_NOTE_ID = p_jtf_note_id;
UPDATE JTF_NOTES_TL
SET NOTES = l_notes
, LAST_UPDATE_DATE = l_last_update_date
, LAST_UPDATED_BY = l_last_updated_by
, LAST_UPDATE_LOGIN = l_last_update_login
, SOURCE_LANG = USERENV('LANG')
WHERE JTF_NOTE_ID = p_jtf_note_id;
UPDATE JTF_NOTES_TL
SET NOTES = l_notes
, NOTES_DETAIL = EMPTY_CLOB()
, LAST_UPDATE_DATE = l_last_update_date
, LAST_UPDATED_BY = l_last_updated_by
, LAST_UPDATE_LOGIN = l_last_update_login
, SOURCE_LANG = USERENV('LANG')
WHERE JTF_NOTE_ID = p_jtf_note_id;
Update_note_context
( p_validation_level => p_validation_level
, x_return_status => l_return_status
, p_note_context_id => p_jtf_note_contexts_tab(i).note_context_id
, p_jtf_note_id => p_jtf_note_id
, p_note_context_type_id => p_jtf_note_contexts_tab(i).note_context_type_id
, p_note_context_type => p_jtf_note_contexts_tab(i).note_context_type
, p_last_updated_by => p_jtf_note_contexts_tab(i).last_updated_by
, p_last_update_date => p_jtf_note_contexts_tab(i).last_update_date
, p_last_update_login => p_jtf_note_contexts_tab(i).last_update_login
);
jtf_notes_cuhk.update_note_post
( p_api_version => l_api_version
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_jtf_note_id => p_jtf_note_id
, p_entered_by => l_entered_by
, p_last_updated_by => p_last_updated_by
, p_notes => l_notes
, p_notes_detail => l_notes_detail
, p_append_flag => p_append_flag
, p_note_status => l_note_status
, p_note_type => l_note_type
, x_return_status => l_return_status
);
, 'Update Note'
, 'A'
, 'V'
)
THEN
jtf_notes_vuhk.update_note_post
( p_api_version => l_api_version
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_jtf_note_id => p_jtf_note_id
, p_entered_by => l_entered_by
, p_last_updated_by => p_last_updated_by
, p_notes => l_notes
, p_notes_detail => l_notes_detail
, p_append_flag => p_append_flag
, p_note_status => l_note_status
, p_note_type => l_note_type
, x_return_status => l_return_status
);
, p_last_updated_by => p_last_updated_by
, p_notes => l_notes
, p_notes_detail => l_notes_detail
, p_append_flag => p_append_flag
, p_note_status => l_note_status
, p_note_type => l_note_type
, x_return_status => l_return_status
)
THEN
l_bind_data_id := JTF_USR_HKS.Get_bind_data_id;
JTF_NOTES_EVENTS_PVT.RaiseUpdateNote
( p_NoteID => p_jtf_note_id
, p_SourceObjectCode => l_com_rec.source_object_code
, p_SourceObjectID => l_com_rec.source_object_id
);
ROLLBACK TO update_note_pvt;
ROLLBACK TO update_note_pvt;
ROLLBACK TO update_note_pvt;
ROLLBACK TO update_note_pvt;
END Secure_Update_note;