[Home] [Help]
1: PACKAGE BODY CS_MESSAGES_PKG AS
2: /* $Header: csmesgb.pls 120.3 2006/01/19 22:15:01 varnaray noship $ */
3: -- ------------------------------------------------------------------------
4: -- Send_Message
5: -- Call the Workflow Notification API to send the message and insert a
2: /* $Header: csmesgb.pls 120.3 2006/01/19 22:15:01 varnaray noship $ */
3: -- ------------------------------------------------------------------------
4: -- Send_Message
5: -- Call the Workflow Notification API to send the message and insert a
6: -- new record into CS_MESSAGES.
7: -- ------------------------------------------------------------------------
8:
9: PROCEDURE Send_Message (
10: p_source_object_type IN VARCHAR2,
34:
35: l_priority_number NUMBER;
36:
37: CURSOR l_msgid_csr IS
38: SELECT cs_messages_s.NEXTVAL
39: FROM dual;
40:
41: CURSOR l_ntf_csr IS
42: SELECT ntf.notification_id
152: role => p_receiver_role,
153: msg_type => 'CS_MSGS',
154: msg_name => 'FYI_MESSAGE',
155: due_date => NULL,
156: callback => 'CS_MESSAGES_PKG.NOTIFICATION_CALLBACK',
157: context => to_char(l_message_id),
158: send_comment => NULL,
159: priority => l_priority_number );
160:
165: role => p_receiver_role,
166: msg_type => 'CS_MSGS',
167: msg_name => 'EXPANDED_FYI_MSG',
168: due_date => NULL,
169: callback => 'CS_MESSAGES_PKG.NOTIFICATION_CALLBACK',
170: context => to_char(l_message_id),
171: send_comment => NULL,
172: priority => l_priority_number );
173:
198: role => p_receiver_role,
199: msg_type => 'CS_MSGS',
200: msg_name => 'ACTION_REQUEST_MSG',
201: due_date => NULL,
202: callback => 'CS_MESSAGES_PKG.NOTIFICATION_CALLBACK',
203: context => to_char(l_message_id),
204: send_comment => NULL,
205: priority => l_priority_number );
206:
221: IF (l_user_id IS NULL) THEN
222: l_user_id := -1;
223: END IF;
224:
225: -- Insert a new record into the CS_MESSAGES table
226: INSERT INTO cs_messages (
227: message_id,
228: notification_id,
229: date_sent,
222: l_user_id := -1;
223: END IF;
224:
225: -- Insert a new record into the CS_MESSAGES table
226: INSERT INTO cs_messages (
227: message_id,
228: notification_id,
229: date_sent,
230: last_update_date,
311: wf.display_name responder,
312: msg.confirmation,
313: msg.notification_id,
314: msg.sender_role sender
315: FROM wf_notifications ntf, wf_roles wf, cs_messages msg
316: WHERE msg.message_id = l_message_id
317: AND msg.notification_id = ntf.notification_id
318: AND ntf.responder = wf.name(+)
319: FOR UPDATE OF msg.message_id;
367:
368: -- Get the comment of the responder
369: l_comment := WF_NOTIFICATION.GetAttrText(l_ntf_rec.notification_id, 'COMMENT');
370:
371: -- Update the row in the CS_MESSAGES table
372: UPDATE cs_messages
373: SET last_update_date = sysdate,
374: last_updated_by = l_user_id,
375: last_update_login = l_login_id,
368: -- Get the comment of the responder
369: l_comment := WF_NOTIFICATION.GetAttrText(l_ntf_rec.notification_id, 'COMMENT');
370:
371: -- Update the row in the CS_MESSAGES table
372: UPDATE cs_messages
373: SET last_update_date = sysdate,
374: last_updated_by = l_user_id,
375: last_update_login = l_login_id,
376: responder = l_ntf_rec.responder,
395: role => l_ntf_rec.sender,
396: msg_type => 'CS_MSGS',
397: msg_name => 'CONFIRMATION_MESSAGE',
398: due_date => NULL,
399: callback => 'CS_MESSAGES_PKG.NOTIFICATION_CALLBACK',
400: context => to_char(l_message_id),
401: send_comment => NULL );
402:
403: -- Set up the message attributes
491: --------------------------------------------------------------------------------
492:
493: L_API_VERSION CONSTANT NUMBER := 1.0;
494: L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_MESSAGE';
495: L_API_NAME_FULL CONSTANT VARCHAR2(61) := 'CS_MESSAGES' || '.' || L_API_NAME;
496: L_LOG_MODULE CONSTANT VARCHAR2(255) := 'cs.plsql.' || L_API_NAME_FULL || '.';
497:
498: l_row_count NUMBER := 0;
499:
546: (
547: L_API_VERSION
548: , p_api_version_number
549: , L_API_NAME
550: , 'CS_MESSAGES'
551: )
552: THEN
553: FND_MSG_PUB.Count_And_Get
554: (
620: FND_LOG.String
621: (
622: FND_LOG.level_statement
623: , L_LOG_MODULE || 'del_sr_message_start'
624: , 'deleting data in table cs_messages'
625: );
626: END IF ;
627:
628: -- The following delete statement deletes the rows in the
625: );
626: END IF ;
627:
628: -- The following delete statement deletes the rows in the
629: -- cs_messages table which correspond to the incident_ids in
630: -- the global temp table jtf_object_purge_param_tmp which have
631: -- purge_status NULL indicating that the SR is available for
632: -- purge.
633:
631: -- purge_status NULL indicating that the SR is available for
632: -- purge.
633:
634: DELETE /*+ index(m) */
635: cs_messages m
636: WHERE
637: source_object_type_code = 'INC'
638: AND
639: source_object_int_id IN
654: FND_LOG.String
655: (
656: FND_LOG.level_statement
657: , L_LOG_MODULE || 'del_sr_message_end'
658: , 'after deleting data in table cs_messages ' || l_row_count
659: );
660: END IF ;
661:
662: IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
731: END IF ;
732: END Delete_Message;
733: --------------------------------------------------------------------------------
734:
735: END CS_MESSAGES_PKG;