The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cs_messages_s.NEXTVAL
FROM dual;
SELECT ntf.notification_id
FROM wf_notifications ntf
WHERE ntf.group_id = l_ntf_group_id;
SELECT meaning
FROM cs_lookups
WHERE lookup_type = 'MESSAGE_PRIORITY'
AND lookup_code = p_priority;
INSERT INTO cs_messages (
message_id,
notification_id,
date_sent,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
source_object_type_code,
source_object_int_id,
source_object_ext_id,
sender,
sender_role,
receiver,
priority,
expand_roles,
action_code,
confirmation,
message,
responder,
response_date,
response,
responder_comment )
VALUES (
l_message_id,
l_notification_id,
sysdate,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id,
p_source_obj_type_code,
p_source_object_int_id,
p_source_object_ext_id,
p_sender,
p_sender_role,
p_receiver,
p_priority,
p_expand_roles,
p_action_code,
p_confirmation,
p_message,
NULL,
NULL,
NULL,
NULL );
SELECT ntf.end_date,
wf.display_name responder,
msg.confirmation,
msg.notification_id,
msg.sender_role sender
FROM wf_notifications ntf, wf_roles wf, cs_messages msg
WHERE msg.message_id = l_message_id
AND msg.notification_id = ntf.notification_id
AND ntf.responder = wf.name(+)
FOR UPDATE OF msg.message_id;
SELECT meaning
FROM cs_lookups
WHERE lookup_type = 'MESSAGE_RESPONSE'
AND lookup_code = text_value;
UPDATE cs_messages
SET last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login = l_login_id,
responder = l_ntf_rec.responder,
response_date = l_ntf_rec.end_date,
responder_comment = l_comment,
response = text_value
WHERE CURRENT OF l_ntf_csr;
* This procedure physically deletes all the messages that are linked to SRs
* that are to be purged.
* @param p_object_type Type of object for which this procedure is being called.
* (Here it will be 'SR')
* @param p_processing_set_id Id that helps the API in identifying the set of
* SRs for which the child
* objects have to be deleted.
* @rep:scope internal
* @rep:product CS
* @rep:displayname Delete Messages
*/
PROCEDURE Delete_Message
(
p_api_version_number IN NUMBER := 1.0
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_object_type IN VARCHAR2
, p_processing_set_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
--------------------------------------------------------------------------------
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_MESSAGE';
DELETE /*+ index(m) */
cs_messages m
WHERE
source_object_type_code = 'INC'
AND
source_object_int_id IN
(
SELECT /*+ no_unnest no_semijoin cardinality(10) */
object_id
FROM
jtf_object_purge_param_tmp
WHERE
object_type = 'SR'
AND p_processing_set_id = processing_set_id
AND NVL(purge_status, 'S') = 'S'
);
END Delete_Message;