[Home] [Help]
PACKAGE BODY: APPS.ASP_ALERT_ENGINE
Source
1 PACKAGE BODY ASP_ALERT_ENGINE as
2 /* $Header: aspaengb.pls 120.4 2006/01/18 14:00 axavier noship $ */
3 ---------------------------------------------------------------------------
4 -- Package Name: ASP_ALERT_ENGINE
5 ---------------------------------------------------------------------------
6 -- Description:
7 -- Core Alert Engine Package for Sales Alerts Backend Workflow Processing.
8 -- This package is used by the workflow activity nodes.
9 --
10 -- Procedures:
11 -- (see the specification for details)
12 --
13 -- History:
14 -- 08-Aug-2005 axavier created.
15 ---------------------------------------------------------------------------
16
17 /*-------------------------------------------------------------------------*
18 | Private Constants
19 *-------------------------------------------------------------------------*/
20 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASP_ALERT_ENGINE';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'aspaengb.pls';
22 G_MODULE CONSTANT VARCHAR2(250) := 'asp.plsql.'||G_PKG_NAME||'.';
23
24 G_ORDER_ENTITY CONSTANT VARCHAR2(30) := 'ORDER';
25 G_SVCCONTRACT_ENTITY CONSTANT VARCHAR2(30) := 'SERVICE_CONTRACT';
26 G_INVOICE_ENTITY CONSTANT VARCHAR2(30) := 'INVOICE';
27 G_SERVICE_ENTITY CONSTANT VARCHAR2(30) := 'SERVICE_REQUEST';
28
29 G_ORDER_ALERT_AGENT CONSTANT VARCHAR2(30) := 'ORDER_ALERT_AGENT';
30 G_SVCCONTRACT_ALERT_AGENT CONSTANT VARCHAR2(30) := 'SVCCONTRACT_ALERT_AGENT';
31 G_INVOICE_ALERT_AGENT CONSTANT VARCHAR2(30) := 'INVOICE_ALERT_AGENT';
32 G_SERVICE_ALERT_AGENT CONSTANT VARCHAR2(30) := 'SERVICE_ALERT_AGENT';
33 G_CUSTOM_ALERT_AGENT CONSTANT VARCHAR2(30) := 'CUSTOM';
34
35 G_ORDER_CONTENT_PROVIDER CONSTANT VARCHAR2(30) := 'ORDER_CONTENT_PROVIDER';
36 G_SVCCONTRACT_CONTENT_PROVIDER CONSTANT VARCHAR2(30) := 'SVCCONTRACT_CONTENT_PROVIDER';
37 G_INVOICE_CONTENT_PROVIDER CONSTANT VARCHAR2(30) := 'INVOICE_CONTENT_PROVIDER';
38 G_SERVICE_CONTENT_PROVIDER CONSTANT VARCHAR2(30) := 'SERVICE_CONTENT_PROVIDER';
39 G_CUSTOM_CONTENT_PROVIDER CONSTANT VARCHAR2(30) := 'CUSTOM';
40
41 G_ASP_DELIVERY_AGENT CONSTANT VARCHAR2(30) := 'ASP_DELIVERY_AGENT';
42 G_CUSTOM_DELIVERY_AGENT CONSTANT VARCHAR2(30) := 'CUSTOM';
43
44
45
46 /*-------------------------------------------------------------------------*
47 | Private Datatypes
48 *-------------------------------------------------------------------------*/
49
50 /*-------------------------------------------------------------------------*
51 | Private Variables
52 *-------------------------------------------------------------------------*/
53
54 /*-------------------------------------------------------------------------*
55 | Private Routines Specification
56 *-------------------------------------------------------------------------*/
57
58 /*-------------------------------------------------------------------------*
59 | Public Routines
60 *-------------------------------------------------------------------------*/
61
62 --------------------------------------------------------------------------------
63 -- Procedure: Get_Alert_Agent
64 -- This is a factory method, which produces appropriate agents based on the
65 -- Alert Types.
66 --
67 --------------------------------------------------------------------------------
68
69 PROCEDURE Get_Alert_Agent(
70 itemtype in VARCHAR2,
71 itemkey in VARCHAR2,
72 actid in NUMBER,
73 funcmode in VARCHAR2,
74 resultout in out NOCOPY VARCHAR2)
75 IS
76 l_event_entity varchar2(100);
77 l_api_name varchar2(100);
78 l_qualified_api_name varchar2(200);
79 l_debug_msg varchar2(1000);
80
81 l_debug_runtime number;
82 l_debug_exception number;
83 l_debug_procedure number;
84 l_debug_statment number;
85
86 BEGIN
87 l_api_name := 'Get_Alert_Agent';
88 l_debug_runtime := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
89 l_debug_exception := FND_LOG.LEVEL_EXCEPTION;
90 l_debug_procedure := FND_LOG.LEVEL_PROCEDURE;
91 l_debug_statment := FND_LOG.LEVEL_STATEMENT;
92
93 l_qualified_api_name := G_PKG_NAME||'.'||l_api_name;
94 if(l_debug_procedure >= l_debug_runtime) then
95 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Entered '||G_PKG_NAME||'.'||l_api_name);
96 end if;
97
98 If(funcmode = 'RUN') Then
99 l_event_entity := wf_engine.GetItemAttrText( itemtype =>itemtype,
100 itemkey =>itemkey,
101 aname =>'ALERT_SOURCE_OBJECT_CODE');
102 if(l_debug_procedure >= l_debug_runtime) then
103 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_event_entity '||l_event_entity);
104 end if;
105 If l_event_entity = G_ORDER_ENTITY Then
106 resultout := G_ORDER_ALERT_AGENT;
107 ElsIf l_event_entity = G_SVCCONTRACT_ENTITY Then
108 resultout := G_SVCCONTRACT_ALERT_AGENT;
109 ElsIf l_event_entity = G_INVOICE_ENTITY Then
110 resultout := G_INVOICE_ALERT_AGENT;
111 ElsIf l_event_entity = G_SERVICE_ENTITY Then
112 resultout := G_SERVICE_ALERT_AGENT;
113 Else
114 resultout:= G_CUSTOM_ALERT_AGENT;
115 End If;
116 if(l_debug_procedure >= l_debug_runtime) then
117 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'resultout '||resultout);
118 end if;
119 return;
120 End If;
121 --
122 -- Other execution modes may be created in the future. Your
123 -- activity will indicate that it does not implement a mode
124 -- by returning null
125 --
126 resultout := '';
127 return;
128
129 EXCEPTION
130 When no_data_found Then
131 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, to_char(actid), funcmode);
132 l_debug_msg := l_qualified_api_name||':NO_DATA_FOUND:'||to_char(SQLCODE)||':'||substr(SQLERRM,1,500);
133 wf_core.token('ORA_ERROR',l_debug_msg);
134 wf_core.raise('WF_ORA');
135
136 When others Then
137 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, to_char(actid), funcmode);
138 l_debug_msg := l_qualified_api_name||':OTHERS:'||to_char(SQLCODE)||':'||substr(SQLERRM,1,500);
139 wf_core.token('ORA_ERROR',l_debug_msg);
140 wf_core.raise('WF_ORA');
141
142 END Get_Alert_Agent;
143
144
145 --------------------------------------------------------------------------------
146 -- Procedure: Get_Content_Provider
147 -- This is a factory method, which produces appropriate providers based on the
148 -- Alert Types.
149 --
150 --------------------------------------------------------------------------------
151
152 PROCEDURE Get_Content_Provider(
153 itemtype in VARCHAR2,
154 itemkey in VARCHAR2,
155 actid in NUMBER,
156 funcmode in VARCHAR2,
157 resultout in out NOCOPY VARCHAR2)
158 IS
159 l_event_entity varchar2(100);
160 l_use_custom_content varchar2(10);
161 l_api_name varchar2(100);
162 l_qualified_api_name varchar2(200);
163 l_debug_msg varchar2(1000);
164 BEGIN
165 l_api_name := 'Get_Content_Provider';
166 l_qualified_api_name := G_PKG_NAME||'.'||l_api_name;
167
168 If(funcmode = 'RUN') Then
169 l_event_entity := wf_engine.GetItemAttrText( itemtype =>itemtype,
170 itemkey =>itemkey,
171 aname =>'ALERT_SOURCE_OBJECT_CODE');
172
173 begin
174 l_use_custom_content := wf_engine.GetItemAttrText( itemtype =>itemtype,
175 itemkey =>itemkey,
176 aname =>'USE_CUSTOM_CONTENT_AGENT');
177 exception
178 when others then
179 l_use_custom_content := 'NO';
180 end;
181
182 If (l_event_entity = G_ORDER_ENTITY) AND (l_use_custom_content <> 'YES') Then
183 resultout := G_ORDER_CONTENT_PROVIDER;
184 ElsIf (l_event_entity = G_SVCCONTRACT_ENTITY) AND (l_use_custom_content <> 'YES') Then
185 resultout := G_SVCCONTRACT_CONTENT_PROVIDER;
186 ElsIf (l_event_entity = G_INVOICE_ENTITY) AND (l_use_custom_content <> 'YES') Then
187 resultout := G_INVOICE_CONTENT_PROVIDER;
188 ElsIf (l_event_entity = G_SERVICE_ENTITY) AND (l_use_custom_content <> 'YES') Then
189 resultout := G_SERVICE_CONTENT_PROVIDER;
190 Else
191 resultout:= G_CUSTOM_CONTENT_PROVIDER;
192 End If;
193 return;
194 End If;
195 --
196 -- Other execution modes may be created in the future. Your
197 -- activity will indicate that it does not implement a mode
198 -- by returning null
199 --
200 resultout := '';
201 return;
202
203 EXCEPTION
204 When no_data_found Then
205 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, to_char(actid), funcmode);
206 l_debug_msg := l_qualified_api_name||':NO_DATA_FOUND:'||to_char(SQLCODE)||':'||substr(SQLERRM,1,500);
207 wf_core.token('ORA_ERROR',l_debug_msg);
208 wf_core.raise('WF_ORA');
209
210 When others Then
211 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, to_char(actid), funcmode);
212 l_debug_msg := l_qualified_api_name||':OTHERS:'||to_char(SQLCODE)||':'||substr(SQLERRM,1,500);
213 wf_core.token('ORA_ERROR',l_debug_msg);
214 wf_core.raise('WF_ORA');
215
216 END Get_Content_Provider;
217
218
219 --------------------------------------------------------------------------------
220 -- Procedure: Get_Alert_Agent
221 -- This is a factory method, which produces appropriate agents based on the
222 -- Alert Types.
223 --
224 --------------------------------------------------------------------------------
225
226 PROCEDURE Get_Delivery_Agent(
227 itemtype in VARCHAR2,
228 itemkey in VARCHAR2,
229 actid in NUMBER,
230 funcmode in VARCHAR2,
231 resultout in out NOCOPY VARCHAR2)
232 IS
233 l_event_entity varchar2(100);
234 l_use_custom_delivery varchar2(10);
235 l_api_name varchar2(100);
236 l_qualified_api_name varchar2(200);
237 l_debug_msg varchar2(1000);
238 l_debug_runtime number;
239 l_debug_exception number;
240 l_debug_procedure number;
241 l_debug_statment number;
242
243 BEGIN
244 l_api_name := 'Get_Delivery_Agent';
245 l_debug_runtime := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
246 l_debug_exception := FND_LOG.LEVEL_EXCEPTION;
247 l_debug_procedure := FND_LOG.LEVEL_PROCEDURE;
248 l_debug_statment := FND_LOG.LEVEL_STATEMENT;
249
250 l_qualified_api_name := G_PKG_NAME||'.'||l_api_name;
251 if(l_debug_procedure >= l_debug_runtime) then
252 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Entered '||G_PKG_NAME||'.'||l_api_name);
253 end if;
254
255 If(funcmode = 'RUN') Then
256 l_event_entity := wf_engine.GetItemAttrText( itemtype =>itemtype,
257 itemkey =>itemkey,
258 aname =>'ALERT_SOURCE_OBJECT_CODE');
259 begin
260 l_use_custom_delivery := wf_engine.GetItemAttrText( itemtype =>itemtype,
261 itemkey =>itemkey,
262 aname =>'USE_CUSTOM_DELIVERY_AGENT');
263 exception
264 when others then
265 l_use_custom_delivery := 'NO';
266 end;
267 if(l_debug_procedure >= l_debug_runtime) then
268 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_event_entity '||l_event_entity);
269 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_use_custom_delivery '||l_use_custom_delivery);
270 end if;
271
272 If(
273 l_event_entity in (G_ORDER_ENTITY, G_SVCCONTRACT_ENTITY, G_INVOICE_ENTITY, G_SERVICE_ENTITY,'CUSTOM')
274 AND (l_use_custom_delivery <> 'YES')
275 ) Then
276 resultout := G_ASP_DELIVERY_AGENT;
277 Else
278 resultout:= G_CUSTOM_DELIVERY_AGENT;
279 End If;
280 if(l_debug_procedure >= l_debug_runtime) then
281 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'resultout '||resultout);
282 end if;
283 return;
284 End If;
285
286 --
287 -- Other execution modes may be created in the future. Your
291 resultout := '';
288 -- activity will indicate that it does not implement a mode
289 -- by returning null
290 --
292 return;
293
294 EXCEPTION
295 When no_data_found Then
296 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, to_char(actid), funcmode);
297 l_debug_msg := l_qualified_api_name||':NO_DATA_FOUND:'||to_char(SQLCODE)||':'||substr(SQLERRM,1,500);
298 wf_core.token('ORA_ERROR',l_debug_msg);
299 wf_core.raise('WF_ORA');
300
301 When others Then
302 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, to_char(actid), funcmode);
303 l_debug_msg := l_qualified_api_name||':OTHERS:'||to_char(SQLCODE)||':'||substr(SQLERRM,1,500);
304 wf_core.token('ORA_ERROR',l_debug_msg);
305 wf_core.raise('WF_ORA');
306
307 END Get_Delivery_Agent;
308
309
310 PROCEDURE NOOP(itemtype in varchar2,
311 itemkey in varchar2,
312 actid in number,
313 funcmode in varchar2,
314 resultout in out nocopy varchar2)
315 IS
316 l_api_name varchar2(100);
317 l_qualified_api_name varchar2(200);
318 l_debug_msg varchar2(1000);
319 l_debug_runtime number;
320 l_debug_exception number;
321 l_debug_procedure number;
322 l_debug_statment number;
323
324 BEGIN
325 l_api_name := 'NOOP';
326 l_debug_runtime := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
327 l_debug_exception := FND_LOG.LEVEL_EXCEPTION;
328 l_debug_procedure := FND_LOG.LEVEL_PROCEDURE;
329 l_debug_statment := FND_LOG.LEVEL_STATEMENT;
330
331 l_qualified_api_name := G_PKG_NAME||'.'||l_api_name;
332 if(l_debug_procedure >= l_debug_runtime) then
333 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Entered '||G_PKG_NAME||'.'||l_api_name);
334 end if;
335
336 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
337 EXCEPTION
338 When others Then
339 Wf_Core.Context(G_PKG_NAME, 'Noop', l_api_name, itemkey, to_char(actid), funcmode);
340 l_debug_msg := l_qualified_api_name||':OTHERS:'||to_char(SQLCODE)||':'||substr(SQLERRM,1,500);
341 wf_core.token('ORA_ERROR',l_debug_msg);
342 wf_core.raise('WF_ORA');
343 END NOOP;
344
345 ------------------------------------------------------------------------------
346 -- Alerts_Selector
347 -- This procedure sets up the responsibility and organization context for
348 -- multi-org sensitive code.
349 ------------------------------------------------------------------------------
350
351 PROCEDURE Alerts_Selector(
352 itemtype IN VARCHAR2,
353 itemkey IN VARCHAR2,
354 actid IN NUMBER,
355 funcmode IN VARCHAR2,
356 resultout OUT NOCOPY VARCHAR2)
357 IS
358 l_user_id NUMBER;
359 l_resp_id NUMBER;
360 l_resp_appl_id NUMBER;
361 l_api_name varchar2(100);
362 l_qualified_api_name varchar2(200);
363 l_debug_msg varchar2(1000);
364
365 l_debug_runtime number;
366 l_debug_exception number;
367 l_debug_procedure number;
368 l_debug_statment number;
369
370 BEGIN
371 l_api_name := 'Alerts_Selector';
372 l_debug_runtime := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
373 l_debug_exception := FND_LOG.LEVEL_EXCEPTION;
374 l_debug_procedure := FND_LOG.LEVEL_PROCEDURE;
375 l_debug_statment := FND_LOG.LEVEL_STATEMENT;
376
377 l_qualified_api_name := G_PKG_NAME||'.'||l_api_name;
378 if(l_debug_procedure >= l_debug_runtime) then
379 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Entered '||G_PKG_NAME||'.'||l_api_name);
380 end if;
381 if(l_debug_procedure >= l_debug_runtime) then
382 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'funcmode '||funcmode);
383 end if;
384
385 IF (funcmode = 'RUN') THEN
386 resultout := 'COMPLETE';
387
388 -- Engine calls SET_CTX just before activity execution
389 ELSIF (funcmode = 'SET_CTX') THEN
390
391 -- First get the user id, resp id, and appl id
392 l_user_id := WF_ENGINE.GetItemAttrNumber
393 ( itemtype => itemtype,
394 itemkey => itemkey,
395 aname => 'USER_ID'
396 );
397
398 l_resp_id := WF_ENGINE.GetItemAttrNumber
399 ( itemtype => itemtype,
400 itemkey => itemkey,
401 aname => 'RESPONSIBILITY_ID'
402 );
403 l_resp_appl_id := WF_ENGINE.GetItemAttrNumber
404 ( itemtype => itemtype,
405 itemkey => itemkey,
406 aname => 'APPLICATION_ID'
407 );
408
409 -- Set the database session context
410 begin
411 if( l_user_id is not null and l_resp_id is not null and l_resp_appl_id is not null) then
412 FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id);
413 if(l_debug_procedure >= l_debug_runtime) then
414 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'FND_GLOBAL.Apps_Initialize called ');
415 end if;
416
417 end if;
418 exception
419 when others then
420 null;
421 end;
422 if(l_debug_procedure >= l_debug_runtime) then
423 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_user_id '||l_user_id);
424 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_resp_id '||l_resp_id);
425 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_resp_appl_id '||l_resp_appl_id);
426 end if;
427
428 MO_GLOBAL.set_policy_context ('A', null);
429
430 resultout := 'COMPLETE';
431
432 -- Notification Viewer form calls TEST_CTX just before launching a form
433 ELSIF (funcmode = 'TEST_CTX') THEN
434 resultout := 'COMPLETE';
435
436 END IF;
437
438 EXCEPTION
439 WHEN OTHERS THEN
440 WF_CORE.Context('ASP_ALERT_ENGINE', 'Alerts_Selector',
441 itemtype, itemkey, actid, funcmode);
442 RAISE;
443 END Alerts_Selector;
444
445
446
447 END ASP_ALERT_ENGINE;