[Home] [Help]
PACKAGE BODY: APPS.XDP_MESSAGE
Source
1 PACKAGE BODY xdp_message AS
2 /* $Header: XDPMSGPB.pls 120.2 2006/04/10 23:23:26 dputhiye noship $ */
3
4 -- Procedure: retry_failed_message
5 -- Purpose: Retries Failed message
6 -- Parameters: Message to retry (character matching allowed, e.g. 'XNP%')
7 -- Comments: Responds to Workflow notification sent for failed message.
8 -- which in turn invokes workflow which retries message.
9 -- Called from: Concurrent program 'XDP Resubmit Failed Messages' (XDPRESUB.sql)
10
11 PROCEDURE retry_failed_message
12 ( errbuf OUT NOCOPY VARCHAR2 --Fixing 3957604. GSCC warning to add NOCOPY hint. Added hint
13 , retcode OUT NOCOPY VARCHAR2 --to errbuf and retcode arguments.
14 , p_msg_to_retry IN VARCHAR2 ) IS
15
16 -- Cursor to find notification for FAILED messages in XNP_MSGS matching msg_code pattern sent in to procedure
17 -- Logic depends on that subject for notification ends with ' - <msg_id>'.
18
19 /* Corrected the following SQL to fix bug 3957604. dputhiye. 27/10/04 */
20 -- CURSOR c_notification_to_respond_to IS
21 -- SELECT wf.notification_id, msg.msg_id, msg.msg_code
22 -- FROM wf_notifications wf
23 -- , xnp_msgs msg
24 -- WHERE wf.message_name like 'MSG_FAILURE_NOTIF'
25 -- AND wf.status = 'OPEN'
26 -- AND msg.msg_code like '||p_msg_to_retry||'
27 -- AND msg.msg_status = 'FAILED'
28 -- AND msg.msg_id =
29 -- SUBSTR(subject,INSTR(subject,' - ')+3,LENGTH(subject));
30
31 CURSOR c_notification_to_respond_to IS
32 SELECT wf.notification_id, msg.msg_id, msg.msg_code
33 FROM wf_notifications wf
34 , xnp_msgs msg
35 WHERE wf.message_name like 'MSG_FAILURE_NOTIF'
36 AND wf.status = 'OPEN'
37 AND msg.msg_code like p_msg_to_retry
38 AND msg.msg_status = 'FAILED'
39 AND TO_CHAR(msg.msg_id) =
40 SUBSTR(subject,INSTR(subject,' - ')+3,LENGTH(subject));
41
42 l_num_of_messages_success NUMBER := 0;
43 l_num_of_messages_fail NUMBER := 0;
44 BEGIN
45 FOR rec_notification_to_respond_to IN c_notification_to_respond_to
46 LOOP
47 BEGIN
48
49 wf_notification.SetAttrText(
50 nid => rec_notification_to_respond_to.notification_id
51 , aname => 'RESULT'
52 , avalue => 'RETRY_MSG');
53
54 wf_notification.Respond(
55 nid => rec_notification_to_respond_to.notification_id
56 , respond_comment => 'Retried processing message in batch '||
57 'together with messages with code '||p_msg_to_retry
58 , responder => fnd_global.user_id);
59
60 fnd_file.put_line(fnd_file.log,'Successfully Resubmitted Msg Code: '||
61 rec_notification_to_respond_to.msg_code||
62 ' Msg ID: '||rec_notification_to_respond_to.msg_id );
63
64 l_num_of_messages_success := l_num_of_messages_success + 1;
65 EXCEPTION
66 WHEN OTHERS THEN
67 fnd_file.put_line(fnd_file.log,'Resubmit failed '||
68 ' Error: '||sqlcode||
69 ' Msg Code: '|| rec_notification_to_respond_to.msg_code||
70 ' Msg ID: '||rec_notification_to_respond_to.msg_id );
71 l_num_of_messages_fail := l_num_of_messages_fail + 1;
72 END;
73 END LOOP;
74 fnd_file.put_line(fnd_file.log,'-----------------------------------------------');
75 fnd_file.put_line(fnd_file.log,l_num_of_messages_success||' messages successfully resubmitted');
76 fnd_file.put_line(fnd_file.log,l_num_of_messages_fail||' messages not resubmitted');
77 retcode := 0;
78 errbuf := 'Success';
79 EXCEPTION
80 WHEN OTHERS THEN
81 retcode := 2;
82 errbuf := SQLERRM;
83 END retry_failed_message;
84
85 END xdp_message;