[Home] [Help]
PACKAGE BODY: APPS.ASP_ALERTS_SUBS
Source
1 PACKAGE BODY ASP_ALERTS_SUBS as
2 /* $Header: aspasubb.pls 120.4 2005/11/22 16:11 axavier noship $ */
3 ---------------------------------------------------------------------------
4 -- Package Name: ASP_ALERTS_SUBS
5 ---------------------------------------------------------------------------
6 -- Description:
7 -- Generic Subscription Package for Sales Alerts Related Business Events.
8 --
9 -- Procedures:
10 -- (see the specification for details)
11 --
12 -- History:
13 -- 08-Aug-2005 axavier created.
14 ---------------------------------------------------------------------------
15
16 /*-------------------------------------------------------------------------*
17 | Private Constants
18 *-------------------------------------------------------------------------*/
19 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASP_ALERTS_SUBS';
20 G_FILE_NAME CONSTANT VARCHAR2(12) := 'aspasubb.pls';
21 G_MODULE CONSTANT VARCHAR2(250) := 'asp.plsql.'||G_PKG_NAME||'.';
22
23 G_ESC_SERVICE_REQUEST_EVENT CONSTANT VARCHAR2(240) :=
24 'oracle.apps.jtf.cac.escalation.createEscalation';
25
26
27 /*-------------------------------------------------------------------------*
28 | Private Datatypes
29 *-------------------------------------------------------------------------*/
30
31 /*-------------------------------------------------------------------------*
32 | Private Variables
33 *-------------------------------------------------------------------------*/
34
35 /*-------------------------------------------------------------------------*
36 | Private Routines Specification
37 *-------------------------------------------------------------------------*/
38
39 /*-------------------------------------------------------------------------*
40 | Public Routines
41 *-------------------------------------------------------------------------*/
42
43 --------------------------------------------------------------------------------
44 --
45 -- Procedure: Initiate_Alerts
46 -- Generic Subscription Function for Sales Alerts Related Business Events.
47 -- This function will be called by the BES if a Service Request is Escalated.
48 -- This could be used by the end-customer for extending the Alerting System.
49 --
50 --------------------------------------------------------------------------------
51
52 FUNCTION Initiate_Alerts(
53 P_subscription_guid in RAW,
54 P_event in out NOCOPY WF_EVENT_T) RETURN VARCHAR2
55 IS
56
57 l_event_key number;
58 l_event_name varchar2(240);
59 l_item_key varchar2(240);
60 l_incident_id number;
61 l_api_name varchar2(100);
62
63 myList wf_parameter_list_t;
64 pos number := 1;
65 pName VARCHAR2(30);
66 pValue VARCHAR2(2000);
67
68 save_threshold number;
69 l_debug_runtime number;
70 l_debug_exception number;
71 l_debug_procedure number;
72 l_debug_statment number;
73
74 CURSOR get_ref_sr(p_task_id in number) is
75 SELECT r.object_id,
76 t.escalation_level
77 FROM jtf_tasks_vl t,
78 jtf_task_references_vl r
79 WHERE t.task_id = p_task_id
80 and t.task_id = r.task_id
81 and r.object_type_code = 'SR'
82 and r.reference_code = 'ESC'
83 and r.object_id IS NOT NULL;
84
85 BEGIN
86 l_api_name := 'Initiate_Alerts';
87 l_debug_runtime := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
88 l_debug_exception := FND_LOG.LEVEL_EXCEPTION;
89 l_debug_procedure := FND_LOG.LEVEL_PROCEDURE;
90 l_debug_statment := FND_LOG.LEVEL_STATEMENT;
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 save_threshold := wf_engine.threshold;
97 l_event_name := p_event.getEventName();
98 -- Detect the event raised and determine necessary parameters depending on the event.
99
100 if(l_debug_procedure >= l_debug_runtime) then
101 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_event_name='||l_event_name);
102 end if;
103
104 If(l_event_name = G_ESC_SERVICE_REQUEST_EVENT) Then
105 l_event_key := p_event.GetValueForParameter('TASK_ID');
106 if(l_debug_procedure >= l_debug_runtime) then
107 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_event_key='||l_event_key);
108 end if;
109 FOR get_esc_details_rec IN get_ref_sr(l_event_key)
110 LOOP
111 l_incident_id := get_esc_details_rec.object_id ;
112 -- Construct the unique item key
113 SELECT l_incident_id ||'-'|| to_char(asp_wf_alerts_s.nextval) INTO l_item_key FROM DUAL;
114
115 -- Start the ASP Alert Manager Process (ASP_ALERT_PROCESS) with the following info:
116 wf_engine.threshold := -1;
117 wf_engine.CreateProcess( itemtype => 'ASPALERT', itemkey => l_item_key, process => 'ASP_ALERT_PROCESS',user_key=>l_item_key);
118 if(l_debug_procedure >= l_debug_runtime) then
119 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_item_key='||l_item_key);
120 end if;
121
122 wf_engine.SetItemAttrText('ASPALERT', l_item_key, 'ALERT_NAME', 'SVCREQUEST_ESCALATED_ALERT');
123 wf_engine.SetItemAttrText('ASPALERT', l_item_key, 'ALERT_SOURCE_OBJECT_CODE', 'SERVICE_REQUEST');
124 wf_engine.SetItemAttrText('ASPALERT', l_item_key, 'ALERT_SOURCE_OBJECT_ID', l_incident_id);
125 wf_engine.SetItemAttrText('ASPALERT', l_item_key, 'ALERT_SOURCE_TASK_ID', l_event_key);
126 if(l_debug_procedure >= l_debug_runtime) then
127 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Before wf_engine.StartProcess');
128 end if;
129 wf_engine.StartProcess(itemtype => 'ASPALERT', itemkey => l_item_key);
130 if(l_debug_procedure >= l_debug_runtime) then
131 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'After wf_engine.StartProcess');
132 end if;
133 commit;
134
135 END LOOP;
136 ELSE
137 --Custom Events
138 -- Construct the unique item key
139 if(l_debug_procedure >= l_debug_runtime) then
140 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Subscription - CUSTOM Event');
141 end if;
142 SELECT to_char(asp_wf_alerts_s.nextval) INTO l_item_key FROM DUAL;
143
144 -- Start the ASP Alert Manager Process (ASP_ALERT_PROCESS) with the following info:
145 wf_engine.threshold := -1;
146 wf_engine.CreateProcess( itemtype => 'ASPALERT', itemkey => l_item_key, process => 'ASP_ALERT_PROCESS',user_key=>l_item_key);
147 if(l_debug_procedure >= l_debug_runtime) then
148 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_item_key='||l_item_key);
149 end if;
150
151 wf_engine.SetItemAttrText('ASPALERT', l_item_key, 'ALERT_NAME', l_event_name);
152 wf_engine.SetItemAttrText('ASPALERT', l_item_key, 'ALERT_SOURCE_OBJECT_CODE', 'CUSTOM');
153
154 myList := P_event.getParameterList();
155 if (myList is not null) then
156 pos := myList.LAST;
157 while(pos is not null) loop
158 begin
159 pName := myList(pos).getName();
160 pValue := myList(pos).getValue();
161 pName := upper(pName);
162 pos := myList.PRIOR(pos);
163 wf_engine.SetItemAttrText('ASPALERT', l_item_key, pName, pValue);
164 exception when others then
165 null;
166 end;
167 end loop;
168 end if;
169
170 if(l_debug_procedure >= l_debug_runtime) then
171 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Before wf_engine.StartProcess');
172 end if;
173 wf_engine.StartProcess(itemtype => 'ASPALERT', itemkey => l_item_key);
174 if(l_debug_procedure >= l_debug_runtime) then
175 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'After wf_engine.StartProcess');
176 end if;
177 commit;
178
179 END IF;
180 wf_engine.threshold := save_threshold;
181 RETURN 'SUCCESS';
182
183 EXCEPTION
184 WHEN others THEN
185 WF_CORE.CONTEXT(G_PKG_NAME, l_api_name, l_event_name, p_subscription_guid);
186 WF_EVENT.setErrorInfo(p_event, 'WARNING');
187 wf_engine.threshold := save_threshold;
188 if(l_debug_exception >= l_debug_runtime) then
189 fnd_log.string(l_debug_exception, G_MODULE||l_api_name,
190 'Leaving '||G_PKG_NAME||'.'||l_api_name||'with exceptions' ||
191 to_char(SQLCODE)||':'||substr(SQLERRM,1,500));
192 end if;
193 return 'WARNING';
194
195 END Initiate_Alerts;
196
197 End ASP_ALERTS_SUBS;