DBA Data[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;