1 PACKAGE BODY CS_WF_EVENT_SUBSCRIPTIONS_PKG AS
2 /* $Header: csxevtsb.pls 120.2.12010000.2 2008/08/29 07:02:22 vpremach ship $ */
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- VPREMACH 08/28/08 Bug 7118071 : When task is created in close status,
13 -- upward propagation has to work.
14 -- Enter procedure, function bodies as shown below
15
16
17 FUNCTION CS_SR_Verify_All(p_subscription_guid in raw,
18 p_event in out nocopy WF_EVENT_T) RETURN varchar2 is
19
20
21 -- Task Related Event Parameters and Cursors
22 l_task_id NUMBER;
23 l_task_audit_id NUMBER;
24
25 CURSOR c_sr_task_sr_closure_csr IS
26 SELECT source_object_type_code,
27 source_object_id ,
28 open_flag ,
29 last_updated_by,
30 last_update_login
31 FROM jtf_tasks_b
32 WHERE task_id = l_task_id;
33
34 c_sr_task_sr_closure_rec c_sr_task_sr_closure_csr%ROWTYPE;
35
36 -- Get the status details from the SR task audit record
37
38 CURSOR c_sr_task_status_audit IS
39 SELECT old_task_status_id ,
40 new_task_status_id,
41 last_updated_by
42 FROM jtf_task_audits_b
43 WHERE task_id = l_task_id
44 AND task_audit_id = l_task_audit_id ;
45
46 c_sr_task_status_audit_rec c_sr_task_status_audit%ROWTYPE;
47
48 -- Generic Event Parameters and Cursors
49 l_event_name VARCHAR2(240) := p_event.getEventName( );
50 l_request_id NUMBER := NULL;
51 l_resp_appl_id NUMBER := NULL;
52 l_login_id NUMBER := NULL;
53 l_user_id NUMBER := NULL;
54 l_return_status VARCHAR2(1);
55 l_msg_count NUMBER;
56 l_msg_data VARCHAR2(2000);
57 l_auto_close_profile VARCHAR2(30);
58 l_status_prop_flag VARCHAR2(3) := 'N' ;
59
60 BEGIN
61
62 l_return_status := FND_API.G_RET_STS_SUCCESS;
63
64 FND_PROFILE.GET('CS_SR_AUTO_CLOSE_SR',l_auto_close_profile);
65
66 IF nvl(l_auto_close_profile, 'NO') <> 'YES' THEN
67 return 'SUCCESS';
68 END IF;
69
70 --Begin Bug 7118071
71 IF (l_event_name = 'oracle.apps.jtf.cac.task.createTask') THEN
72 l_task_id := p_event.GetValueForParameter('TASK_ID');
73
74 OPEN c_sr_task_sr_closure_csr;
75 FETCH c_sr_task_sr_closure_csr INTO c_sr_task_sr_closure_rec;
76 CLOSE c_sr_task_sr_closure_csr;
77
78 IF ( c_sr_task_sr_closure_rec.source_object_type_code <> 'SR') THEN
79 return 'SUCCESS';
80 END IF;
81 IF (NVL(c_sr_task_sr_closure_rec.open_flag ,'X') = 'N') THEN
82 l_status_prop_flag := 'Y';
83 l_request_id := c_sr_task_sr_closure_rec.source_object_id;
84 l_user_id := NVL(c_sr_task_sr_closure_rec.last_updated_by,FND_GLOBAL.USER_ID);
85 l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
86 l_login_id := NVL(c_sr_task_sr_closure_rec.last_update_login,FND_GLOBAL.LOGIN_ID) ;
87 END IF;
88 END IF;
89 --End Bug 7118071
90
91 ---- Code to handle update to SR task status
92
93 IF (l_event_name = 'oracle.apps.jtf.cac.task.updateTask') THEN
94
95 l_task_id := p_event.GetValueForParameter('TASK_ID');
96 l_task_audit_id := p_event.GetValueForParameter('TASK_AUDIT_ID');
97
98 OPEN c_sr_task_sr_closure_csr;
99 FETCH c_sr_task_sr_closure_csr INTO c_sr_task_sr_closure_rec;
100 CLOSE c_sr_task_sr_closure_csr;
101
102 IF ( c_sr_task_sr_closure_rec.source_object_type_code <> 'SR') THEN
103 return 'SUCCESS';
104 END IF;
105
106 IF (NVL(c_sr_task_sr_closure_rec.open_flag ,'X') = 'N') THEN
107
108 OPEN c_sr_task_status_audit ;
109 FETCH c_sr_task_status_audit INTO c_sr_task_status_audit_rec;
110 CLOSE c_sr_task_status_audit ;
111
112 IF (c_sr_task_status_audit_rec.new_task_status_id <> c_sr_task_status_audit_rec.old_task_status_id) THEN
113
114 l_status_prop_flag := 'Y';
115 l_request_id := c_sr_task_sr_closure_rec.source_object_id;
116 l_user_id := NVL(c_sr_task_sr_closure_rec.last_updated_by,FND_GLOBAL.USER_ID);
117 l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
118 l_login_id := NVL(c_sr_task_sr_closure_rec.last_update_login,FND_GLOBAL.LOGIN_ID) ;
119 END IF ;
120 END IF ;
121 END IF;
122
123 ---- Code to handle charge lines
124 IF (l_event_name = 'oracle.apps.cs.chg.Charges.submitted') THEN
125
126 l_status_prop_flag := 'Y';
127 l_request_id := p_event.GetValueForParameter('INCIDENT_ID');
128 l_user_id := p_event.GetValueForParameter('USER_ID');
129 l_resp_appl_id := p_event.GetValueForParameter('RESP_APPL_ID');
130 l_login_id := FND_GLOBAL.LOGIN_ID;
131
132 END IF;
133
134 IF l_status_prop_flag = 'Y' THEN
135
136 CS_SR_STATUS_PROPAGATION_PKG.SR_UPWARD_STATUS_PROPAGATION(
137 p_api_version => 1.0,
138 p_service_request_id => l_request_id,
139 p_user_id => l_user_id,
140 p_resp_appl_id => l_resp_appl_id,
141 p_login_id => l_login_id,
142 x_return_status => l_return_status,
143 x_msg_count => l_msg_count,
144 x_msg_data => l_msg_data);
145
146 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
147 -- RAISE FND_API.G_EXC_ERROR ;
148 return 'SUCCESS';
149 END IF;
150 END IF ;
151
152 return 'SUCCESS';
153
154 EXCEPTION
155 WHEN FND_API.G_EXC_ERROR THEN
156 return 'ERROR';
157
158 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
159 return 'ERROR';
160
161 WHEN OTHERS THEN
162 return 'ERROR';
163
164 END CS_SR_Verify_All;
165
166 /*
167 This business event subscription notifies the task owner and/or task assignee,
168 if conditions are met, when assigned non-field service tasks are Cancelled/Rejected
169
170 The conditions are :
171 1) The event that triggered this subscription should be "updateTask" event
172 2) The state restrictions profile option "CS_SR_TASK_STATE_ENABLED" should be set to value "Yes"
173 3) The task that got cancelled/rejected should be non-field service task
174 4) The non-field service task was in assigned state when it was cancelled/rejected
175 */
176 FUNCTION CS_SR_SendNtf_To_NonFS_Task(p_subscription_guid in raw,
177 p_event in out nocopy WF_EVENT_T) RETURN varchar2 is
178
179 -- Generic Event Parameters and Cursors
180 l_event_name VARCHAR2(240) := p_event.getEventName( );
181
182 l_task_audit_id NUMBER;
183 l_task_id NUMBER;
184 l_task_status_id NUMBER;
185 l_task_owner_id NUMBER;
186 l_task_assignee_id NUMBER;
187 l_task_resource_id NUMBER;
188 l_nid NUMBER;
189
190 l_state_restrictions_on VARCHAR2(3);
191 l_tasktype_rule VARCHAR2(30);
192 l_task_status VARCHAR2(30);
193 l_owner_role VARCHAR2(320);
194 l_owner_name VARCHAR2(240);
195
196 l_return_status VARCHAR2(1);
197 l_msg_count NUMBER;
198 l_msg_data VARCHAR2(2000);
199
200
201 CURSOR cs_sr_oldnew_task_status_csr IS
202 SELECT old_task_status_id, new_task_status_id
203 FROM JTF_TASK_AUDITS_VL
204 WHERE task_audit_id = l_task_audit_id;
205
206 cs_sr_oldnew_task_status_rec cs_sr_oldnew_task_status_csr%ROWTYPE;
207
208
209 CURSOR cs_sr_get_status_flags_csr IS
210 SELECT assigned_flag, cancelled_flag, rejected_flag, start_date_type, end_date_type, name
211 FROM JTF_TASK_STATUSES_VL
212 WHERE task_status_id = l_task_status_id;
213
214 cs_sr_get_status_flags_rec cs_sr_get_status_flags_csr%ROWTYPE;
215
216 CURSOR cs_sr_check_nonFS_type_csr IS
217 SELECT b.rule, a.task_name, a.description, a.task_number
218 FROM JTF_TASKS_VL a,
219 JTF_TASK_TYPES_B b
220 WHERE a.task_type_id = b.task_type_id
221 AND a.task_id = l_task_id;
222
223 cs_sr_check_nonFS_type_rec cs_sr_check_nonFS_type_csr%ROWTYPE;
224
225 /* The following 2 cursors will get the task resource information required to send WF notifications */
226 CURSOR cs_sr_get_task_assignee_id_csr IS
227 SELECT b.source_id
228 FROM jtf_task_assignments a, jtf_rs_resource_extns b
229 WHERE a.resource_id = b.resource_id(+) AND
230 a.task_id = l_task_id;
231
232 CURSOR cs_sr_get_task_owner_id_csr IS
233 SELECT b.source_id
234 FROM jtf_tasks_b a, jtf_rs_resource_extns b
235 WHERE a.owner_id = b.resource_id(+) AND
236 a.task_id = l_task_id;
237
238 BEGIN
239
240 l_return_status := FND_API.G_RET_STS_SUCCESS;
241
242 -- Get the value currently held by the "Service: Apply State Restrictions on Tasks" profile option
243 l_state_restrictions_on := FND_PROFILE.VALUE('CS_SR_ENABLE_TASK_STATE_RESTRICTIONS');
244
245 --INSERT into cs_tmp1 values ('l_state_restrictions_on = ' || l_state_restrictions_on);
246
247 /*
248 We want to sent error notification to the task owner and/or task assignee
249 only if :
250
251 1) Task update has occurred
252 2) An assigned non-field service task is cancelled/rejected
253 3) State restrictions are enforced
254
255 */
256 IF (l_state_restrictions_on = 'Y' AND
257 l_event_name = 'oracle.apps.jtf.cac.task.updateTask') THEN
258
259
260 l_task_id := p_event.GetValueForParameter('TASK_ID');
261 l_task_audit_id := p_event.GetValueForParameter('TASK_AUDIT_ID');
262
263
264 OPEN cs_sr_check_nonFS_type_csr;
265 FETCH cs_sr_check_nonFS_type_csr into cs_sr_check_nonFS_type_rec;
266 CLOSE cs_sr_check_nonFS_type_csr;
267
268 /* Ensure the task is non-field service task */
269 IF( cs_sr_check_nonFS_type_rec.rule is null OR
270 (cs_sr_check_nonFS_type_rec.rule is not null AND cs_sr_check_nonFS_type_rec.rule <> 'DISPATCH') ) THEN
271
272
273 --l_task_id := p_event.GetValueForParameter('TASK_ID');
274 --l_task_audit_id := p_event.GetValueForParameter('TASK_AUDIT_ID');
275
276
277 OPEN cs_sr_oldnew_task_status_csr;
278 FETCH cs_sr_oldnew_task_status_csr INTO cs_sr_oldnew_task_status_rec;
279 CLOSE cs_sr_oldnew_task_status_csr;
280
281 /* If both old and new status id's are the same, no need to proceed any further */
282 IF (cs_sr_oldnew_task_status_rec.old_task_status_id <> cs_sr_oldnew_task_status_rec.new_task_status_id) THEN
283
284
285 l_task_status_id := cs_sr_oldnew_task_status_rec.new_task_status_id;
286 OPEN cs_sr_get_status_flags_csr;
287 FETCH cs_sr_get_status_flags_csr INTO cs_sr_get_status_flags_rec;
288 CLOSE cs_sr_get_status_flags_csr;
289
290 /* First check if the current task was cancelled/rejected. Else, the subscription can exit right away */
291 IF(nvl(cs_sr_get_status_flags_rec.cancelled_flag,' ') = 'Y' OR
292 nvl(cs_sr_get_status_flags_rec.rejected_flag,' ') = 'Y') THEN
293
294
295 l_task_status := cs_sr_get_status_flags_rec.name;
296
297 l_task_status_id := cs_sr_oldnew_task_status_rec.old_task_status_id;
298 OPEN cs_sr_get_status_flags_csr;
299 FETCH cs_sr_get_status_flags_csr INTO cs_sr_get_status_flags_rec;
300 CLOSE cs_sr_get_status_flags_csr;
301
302 /* Now check if the previous task status was "Assigned" status.
303 If yes, we need to send notifications to task owner and/or task assignee
304 */
305 IF(nvl(cs_sr_get_status_flags_rec.assigned_flag,' ') = 'Y' --AND
306 -- nvl(cs_sr_get_status_flags_rec.start_date_type, ' ') = 'SCHEDULED_START' AND
307 -- nvl(cs_sr_get_status_flags_rec.end_date_type, ' ') = 'SCHEDULED_END'
308 ) THEN
309
310
311 /* Send WF notifications to task owner.. */
312 OPEN cs_sr_get_task_owner_id_csr;
313 FETCH cs_sr_get_task_owner_id_csr INTO l_task_owner_id;
314 CLOSE cs_sr_get_task_owner_id_csr;
315
316 IF(l_task_owner_id is not null) THEN
317
318
319 CS_WORKFLOW_PUB.Get_Employee_Role (
320 p_api_version => 1.0,
321 p_return_status => l_return_status,
322 p_msg_count => l_msg_count,
323 p_msg_data => l_msg_data,
324 p_employee_id => l_task_owner_id,
325 p_role_name => l_owner_role,
326 p_role_display_name => l_owner_name );
327
328 IF (l_owner_role IS NOT NULL) THEN
329
330
331 l_nid := WF_NOTIFICATION.Send(role => l_owner_role,
332 msg_type => 'SERVEREQ',
333 msg_name => 'CS_SR_NOTIFY_TASK_RESOURCES');
334
335 WF_NOTIFICATION.setattrtext(l_nid,'TASK_NUMBER',cs_sr_check_nonFS_type_rec.task_number);
336 WF_NOTIFICATION.setattrtext(l_nid,'TASK_NAME',cs_sr_check_nonFS_type_rec.task_name);
337 WF_NOTIFICATION.setattrtext(l_nid,'TASK_DESCRIPTION',cs_sr_check_nonFS_type_rec.description);
338 WF_NOTIFICATION.setattrtext(l_nid,'TASK_RESOURCE_NAME',l_owner_name);
339 WF_NOTIFICATION.setattrtext(l_nid,'ASSOCIATION_TYPE','owned by ');
340 WF_NOTIFICATION.setattrtext(l_nid,'TASK_STATUS',l_task_status);
341
342 Wf_Notification.Denormalize_Notification(l_nid);
343
344 END IF;
345
346 END IF ; -- End of IF(l_task_owner_id is not null)
347
348
349 OPEN cs_sr_get_task_assignee_id_csr;
350 LOOP
351
352 FETCH cs_sr_get_task_assignee_id_csr INTO l_task_assignee_id;
353 EXIT WHEN cs_sr_get_task_assignee_id_csr%NOTFOUND;
354
355 IF(l_task_assignee_id is not null) THEN
356
357
358 CS_WORKFLOW_PUB.Get_Employee_Role (
359 p_api_version => 1.0,
360 p_return_status => l_return_status,
361 p_msg_count => l_msg_count,
362 p_msg_data => l_msg_data,
363 p_employee_id => l_task_assignee_id,
364 p_role_name => l_owner_role,
365 p_role_display_name => l_owner_name );
366
367 IF (l_owner_role IS NOT NULL) THEN
368
369
370 l_nid := WF_NOTIFICATION.Send(role => l_owner_role,
371 msg_type => 'SERVEREQ',
372 msg_name => 'CS_SR_NOTIFY_TASK_RESOURCES');
373 WF_NOTIFICATION.setattrtext(l_nid,'TASK_NUMBER',cs_sr_check_nonFS_type_rec.task_number);
374 WF_NOTIFICATION.setattrtext(l_nid,'TASK_NAME',cs_sr_check_nonFS_type_rec.task_name);
375 WF_NOTIFICATION.setattrtext(l_nid,'TASK_DESCRIPTION',cs_sr_check_nonFS_type_rec.description);
376 WF_NOTIFICATION.setattrtext(l_nid,'TASK_RESOURCE_NAME',l_owner_name);
377 WF_NOTIFICATION.setattrtext(l_nid,'ASSOCIATION_TYPE','assigned to ');
378 WF_NOTIFICATION.setattrtext(l_nid,'TASK_STATUS',l_task_status);
379
380 Wf_Notification.Denormalize_Notification(l_nid);
381
382 END IF;
383
384 END IF; /* end of IF(l_task_assignee_id is not null) */
385
386
387 END LOOP;
388 CLOSE cs_sr_get_task_assignee_id_csr;
389
390
391
392 END IF; -- End of IF(nvl(cs_sr_get_status_flags_rec.assigned_flag,' ') = 'Y' ....
393
394 END IF; -- End of IF(nvl(cs_sr_get_status_flags_rec.cancelled_flag,' ') = 'Y' ....
395
396 END IF; -- End of IF (cs_sr_oldnew_task_status_rec.old_task_status_id ....
397
398 END IF ; -- End of IF(l_tasktype_rule is not null)
399
400 END IF; -- End of IF (l_state_restrictions_on = 'Y' AND l_event_name = 'oracle.apps.jtf.cac.task.updateTask')
401
402 return 'SUCCESS';
403
404 EXCEPTION
405
406 WHEN OTHERS THEN
407 return 'WARNING';
408
409 END CS_SR_SendNtf_To_NonFS_Task;
410
411
412 -- Enter further code below as specified in the Package spec.
413 END; -- Package Body CS_WF_EVENT_SUBSCRIPTIONS_PKG
[Home] [Help]