[Home] [Help]
PACKAGE BODY: APPS.ASP_SERVICE_ALERT_AGENT
Source
4 -- Package Name: ASP_SERVICE_ALERT_AGENT
1 PACKAGE BODY ASP_SERVICE_ALERT_AGENT as
2 /* $Header: aspaesab.pls 120.4 2005/09/13 17:19 axavier noship $ */
3 ---------------------------------------------------------------------------
5 ---------------------------------------------------------------------------
6 -- Description:
7 -- Package contains methods for evaluating the alert condition and
8 -- finds the subscribers for various alerts.
9 --
10 -- Procedures:
11 -- (see the specification for details)
12 --
13 -- History:
14 -- 10-Aug-2005 axavier created.
15 ---------------------------------------------------------------------------
16
17 /*-------------------------------------------------------------------------*
18 | Private Constants
19 *-------------------------------------------------------------------------*/
20 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASP_SERVICE_ALERT_AGENT';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'aspaesab.pls';
22 G_MODULE CONSTANT VARCHAR2(250) := 'asp.plsql.'||G_PKG_NAME||'.';
23
24 /*-------------------------------------------------------------------------*
25 | Private Datatypes
26 *-------------------------------------------------------------------------*/
27
28 /*-------------------------------------------------------------------------*
29 | Private Variables
30 *-------------------------------------------------------------------------*/
31
32 /*-------------------------------------------------------------------------*
33 | Private Routines Specification
34 *-------------------------------------------------------------------------*/
35
36 /*-------------------------------------------------------------------------*
37 | Public Routines
38 *-------------------------------------------------------------------------*/
39
40 --------------------------------------------------------------------------------
41 -- Procedure: Evaluate_Alerts
42 -- Finds all the subscribers of this alert for SMS and EMAIL Channels.
43 --
44 --------------------------------------------------------------------------------
45
46 PROCEDURE Evaluate_Alerts(
47 itemtype in VARCHAR2,
48 itemkey in VARCHAR2,
49 actid in NUMBER,
50 funcmode in VARCHAR2,
51 resultout in out NOCOPY VARCHAR2)
52 IS
53 l_event_entity varchar2(100);
54 l_api_name varchar2(100);
55 l_qualified_api_name varchar2(200);
56 l_debug_msg varchar2(1000);
57 l_alert_code varchar2(30);
58 l_subscriber_list ASP_ALERTS_PUB.SUBSCRIBER_TBL_TYPE;
59 l_return_status VARCHAR2(30);
60 l_msg_count NUMBER ;
61 l_msg_data VARCHAR2(2000) ;
62 l_sms_users varchar2(32767);
63 l_email_users varchar2(32767);
64 l_sms_fnd_users varchar2(32767);
65 l_email_fnd_users varchar2(32767);
69 l_customer_id number;
66 l_sms_users_count NUMBER ;
67 l_email_users_count NUMBER ;
68 l_incident_id number;
70 l_debug_runtime number;
71 l_debug_exception number;
72 l_debug_procedure number;
73 l_debug_statment number;
74
75 CURSOR get_customer_id(c_incident_id in number) is
76 SELECT sr.customer_id
77 FROM cs_incidents_all_b sr
78 WHERE sr.incident_id = c_incident_id
79 and rownum < 2;
80
81 BEGIN
82 l_api_name := 'Evaluate_Alerts';
83 l_debug_runtime := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
84 l_debug_exception := FND_LOG.LEVEL_EXCEPTION;
85 l_debug_procedure := FND_LOG.LEVEL_PROCEDURE;
86 l_debug_statment := FND_LOG.LEVEL_STATEMENT;
87
88 l_qualified_api_name := G_PKG_NAME||'.'||l_api_name;
89 l_sms_users_count := 0;
90 l_email_users_count := 0;
91
92 if(l_debug_procedure >= l_debug_runtime) then
93 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Entered '||G_PKG_NAME||'.'||l_api_name);
94 end if;
95
96 If(funcmode = 'RUN') Then
97 l_event_entity := wf_engine.GetItemAttrText( itemtype =>itemtype,
98 itemkey =>itemkey,
99 aname =>'ALERT_SOURCE_OBJECT_CODE');
100 l_alert_code := wf_engine.GetItemAttrText( itemtype =>itemtype,
101 itemkey =>itemkey,
102 aname =>'ALERT_NAME');
103 l_incident_id := wf_engine.GetItemAttrText( itemtype =>itemtype,
104 itemkey =>itemkey,
105 aname =>'ALERT_SOURCE_OBJECT_ID');
106
107 BEGIN
108 OPEN get_customer_id(l_incident_id);
109 FETCH get_customer_id
110 INTO l_customer_id;
111 IF (get_customer_id%NOTFOUND) THEN
112 l_customer_id := -1;
113 END IF;
114 CLOSE get_customer_id;
115
116 EXCEPTION
117 WHEN OTHERS THEN
118 l_customer_id := -1;
119 END;
120
121
122 if(l_debug_procedure >= l_debug_runtime) then
123 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_event_entity '||l_event_entity);
124 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_alert_code '||l_alert_code);
125 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_incident_id '||l_incident_id);
126 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_customer_id '||l_customer_id);
127 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Calling ASP_ALERTS_PUB.Get_Matching_Subscriptions');
128 end if;
129
130 If l_event_entity = 'SERVICE_REQUEST' Then
131 ASP_ALERTS_PUB.Get_Matching_Subscriptions(
132 p_api_version_number => 1.0,
133 p_init_msg_list => FND_API.G_FALSE,
134 p_alert_code => l_alert_code,
135 p_customer_id => l_customer_id,
136 x_subscriber_list => l_subscriber_list,
137 x_return_status => l_return_status,
138 x_msg_count => l_msg_count,
139 x_msg_data => l_msg_data
140 );
141
142 if(l_debug_procedure >= l_debug_runtime) then
143 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_return_status '||l_return_status);
144 end if;
145 IF l_return_status <> 'S' THEN
146 --Set debug messages
147 wf_engine.SetItemAttrText(itemtype, itemkey, 'FOUND_SUBS', 'NO');
148 resultout := 'COMPLETE:SUCCESS';
149 return;
150 END IF;
151
152 IF l_subscriber_list.count > 0 THEN
153 BEGIN
154 For i in l_subscriber_list.first..l_subscriber_list.last
155 loop
159 l_sms_users := l_sms_users || l_subscriber_list(i).SUBSCRIBER_NAME;
156 if(l_subscriber_list(i).DELIVERY_CHANNEL = 'SMS') then
157 l_sms_users_count := l_sms_users_count+1;
158 if(l_sms_users_count = 1) then
160 l_sms_fnd_users := l_sms_fnd_users || l_subscriber_list(i).USER_ID;
161 else
162 l_sms_users := l_sms_users || ',' || l_subscriber_list(i).SUBSCRIBER_NAME;
163 l_sms_fnd_users := l_sms_fnd_users || ',' || l_subscriber_list(i).USER_ID;
164 end if;
165 elsif(l_subscriber_list(i).DELIVERY_CHANNEL = 'EMAIL') then
166 l_email_users_count := l_email_users_count+1;
167 if(l_email_users_count = 1) then
168 l_email_users := l_email_users || l_subscriber_list(i).SUBSCRIBER_NAME;
169 l_email_fnd_users := l_email_fnd_users || l_subscriber_list(i).USER_ID;
170 else
171 l_email_users := l_email_users || ',' || l_subscriber_list(i).SUBSCRIBER_NAME;
172 l_email_fnd_users := l_email_fnd_users || ',' || l_subscriber_list(i).USER_ID;
173 end if;
174 else
175 null;
176 end if;
177 end loop;
178 Exception
179 When Others then
180 wf_engine.SetItemAttrText(itemtype, itemkey, 'FOUND_SUBS', 'NO');
181 END;
182
183 if(l_sms_users_count > 0) then
184 wf_engine.SetItemAttrText(itemtype, itemkey, 'FOUND_SMS_SUBS', 'YES');
185 if(l_debug_procedure >= l_debug_runtime) then
186 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'FOUND_SMS_SUBS '||'YES');
187 end if;
188 end if;
189 if(l_email_users_count > 0) then
190 wf_engine.SetItemAttrText(itemtype, itemkey, 'FOUND_EMAIL_SUBS', 'YES');
194 end if;
191 if(l_debug_procedure >= l_debug_runtime) then
192 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'FOUND_EMAIL_SUBS '||'YES');
193 end if;
195
196 wf_engine.SetItemAttrText(itemtype, itemkey, 'SMS_USERS', l_sms_users);
197 wf_engine.SetItemAttrText(itemtype, itemkey, 'EMAIL_USERS', l_email_users);
198 wf_engine.SetItemAttrText(itemtype, itemkey, 'SMS_FND_USERS', l_sms_fnd_users);
199 wf_engine.SetItemAttrText(itemtype, itemkey, 'EMAIL_FND_USERS', l_email_fnd_users);
200 wf_engine.SetItemAttrText(itemtype, itemkey, 'FOUND_SUBS', 'YES');
201
202 if(l_debug_procedure >= l_debug_runtime) then
203 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_sms_users_count '||l_sms_users_count);
204 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_email_users_count '||l_email_users_count);
205 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'SMS_USERS '||l_sms_users);
206 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'EMAIL_USERS '||l_email_users);
207 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'FOUND_SUBS '||'YES');
208 end if;
209
210 ELSE
211 wf_engine.SetItemAttrText(itemtype, itemkey, 'FOUND_SUBS', 'NO');
212 END IF;--l_subscriber_list.count > 0
213
214 resultout := 'COMPLETE:SUCCESS';
215 return;
216 Else
217 resultout := 'COMPLETE:NULL';
218 return;
219 End If;
220 End If;
221 --
222 -- Other execution modes may be created in the future. Your
223 -- activity will indicate that it does not implement a mode
224 -- by returning null
225 --
226 resultout := '';
227 return;
228
229
230 EXCEPTION
231 When no_data_found Then
232 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, to_char(actid), funcmode);
233 l_debug_msg := l_qualified_api_name||':NO_DATA_FOUND:'||to_char(SQLCODE)||':'||substr(SQLERRM,1,500);
234 wf_core.token('ORA_ERROR',l_debug_msg);
235 wf_core.raise('WF_ORA');
236
237 When others Then
238 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, to_char(actid), funcmode);
239 l_debug_msg := l_qualified_api_name||':OTHERS:'||to_char(SQLCODE)||':'||substr(SQLERRM,1,500);
240 wf_core.token('ORA_ERROR',l_debug_msg);
241 wf_core.raise('WF_ORA');
242
243 END Evaluate_Alerts;
244
245 End ASP_SERVICE_ALERT_AGENT;