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