[Home] [Help]
PACKAGE BODY: APPS.OKC_TASK_ALERT_ESCL_PVT
Source
1 PACKAGE BODY OKC_TASK_ALERT_ESCL_PVT AS
2 /* $Header: OKCPALTB.pls 120.0 2005/05/25 19:30:37 appldev noship $ */
3 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
4
5 --Select escalation_owner_ID's
6 -- The following cursor changed by MSENGUPT on 12/08/2001 to change okc_rules_v to okc_rules_b
7 Cursor escal_owner_id(p_tve_id IN NUMBER) is
8 select rul.rule_information6 escalate_owner1_id
9 ,rul.rule_information7 escalate_owner2_id
10 ,rul.dnz_chr_id
11 from okc_rules_b rul
12 where to_char(p_tve_id) = rul.rule_information2
13 and rul.rule_information_category = 'NTN';
14
15 --Select Owner_names
16 Cursor escal_owner_cur(p_escal_owner_id IN NUMBER) is
17 Select fnd.user_name escalate_owner, okx.name full_name
18 from okx_resources_v okx, fnd_user fnd
19 where okx.user_id = fnd.user_id
20 and okx.id1 = p_escal_owner_id;
21
22 -- Following Local Procedure added for Bug 2477032
23
24 PROCEDURE get_fnd_msg_stack(p_msg_data IN VARCHAR2) IS
25 BEGIN
26 IF FND_MSG_PUB.Count_Msg > 1 Then
27 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
28 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MSG_PUB.Get(i,p_encoded =>FND_API.G_FALSE ));
29 END LOOP;
30 ELSE
31 FND_FILE.PUT_LINE(FND_FILE.LOG,p_msg_data);
32 END IF;
33 FND_MSG_PUB.initialize;
34 END get_fnd_msg_stack;
35
36 --------------------------------------------------------------------------------------
37 -- Start of comments
38 -- Procedure Name : task_alert
39 -- Description : This procedure sends notifications to all the task owners
40 -- before the due date is reached.
41 -- It also updates the workflow process id in tasks table
42 -- Version : 1.0
43 -- End of comments
44 --------------------------------------------------------------------------------------
45 PROCEDURE task_alert(errbuf OUT NOCOPY VARCHAR2,
46 retcode OUT NOCOPY VARCHAR2,
47 p_api_version IN NUMBER,
48 p_init_msg_list IN VARCHAR2 ,
49 p_wf_name IN VARCHAR2,
50 p_wf_process IN VARCHAR2) IS
51
52 -- Replaced name with seeded ids to avoid translation issues - Bug 1683539
53 -- Read OKCSCHRULE - Contracts Schedule Rule - Bug 1683539
54 CURSOR alert_cur IS
55 Select jtb.object_version_number
56 ,jtb.task_id
57 ,jtb.task_number
58 ,jtb.task_name
59 ,jtb.source_object_id
60 ,jtb.owner_id
61 ,jtb.planned_end_date
62 ,jtb.notification_period
63 ,res.tve_id
64 from jtf_tasks_vl jtb,
65 jtf_task_types_vl jttl,
66 jtf_task_statuses_vl jtsl,
67 okc_resolved_timevalues res
68 where jtb.actual_end_date IS NULL
69 and res.id = jtb.source_object_id
70 and jtb.workflow_process_id IS NULL
71 and jtb.source_object_type_code = 'OKC_RESTIME'
72 and jtb.task_type_id = jttl.task_type_id
73 and jttl.task_type_id = 23
74 --and jttl.name = 'OKCSCHRULE'
75 and jtb.task_status_id = jtsl.task_status_id
76 and jtsl.task_status_id = 10;
77 --and jtsl.name = 'Open';
78
79 l_workflow_process_id jtf_tasks_v.workflow_process_id%TYPE;
80 l_object_version_number NUMBER;
81 l_task_id jtf_tasks_b.task_id%TYPE;
82 l_task_number jtf_tasks_b.task_number%TYPE;
83 l_task_name jtf_tasks_tl.task_name%TYPE;
84 l_planned_end_date jtf_tasks_b.planned_end_date%TYPE;
85 l_notification_period jtf_tasks_b.notification_period%TYPE;
86 l_owner_id jtf_tasks_b.owner_id%TYPE;
87 l_owner_name fnd_user.user_name%TYPE;
88 l_actual_end_date jtf_tasks_b.actual_end_date%TYPE;
89 l_contract_id okc_rules_b.dnz_chr_id%TYPE;
90 l_escalation_owner1_id okc_rules_b.rule_information6%TYPE;
91 l_escalation_owner2_id okc_rules_b.rule_information7%TYPE;
92 l_escalate_name VARCHAR2(100);
93 l_dummy VARCHAR2(100);
94 l_escalate_owner1 fnd_user.user_name%TYPE;
95 l_escalate_owner2 fnd_user.user_name%TYPE;
96 l_alarm_interval jtf_tasks_b.alarm_interval%TYPE;
97 l_planned_date jtf_tasks_b.planned_end_date%TYPE;
98 l_source_object_id jtf_tasks_b.source_object_id%TYPE;
99 l_item_type VARCHAR2(30);
100 l_item_key NUMBER;
101 l_tve_id NUMBER;
102 l_process VARCHAR2(30);
103 l_return_status VARCHAR2(3);
104 l_api_name CONSTANT VARCHAR2(30) := 'task_alert';
105 l_success_count NUMBER := 0;
106 l_failure_count NUMBER := 0;
107
108 --Send notifications to all task owners
109 BEGIN
110 IF (l_debug = 'Y') THEN
111 OKC_DEBUG.set_indentation(l_api_name);
112 OKC_DEBUG.log('10: Entered task_alert', 2);
113 END IF;
114
115 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
116 G_PKG_NAME,
117 p_init_msg_list,
118 g_api_version,
119 p_api_version,
120 G_LEVEL,
121 g_return_status);
122 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
123 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
124 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
125 RAISE OKC_API.G_EXCEPTION_ERROR;
126 END IF;
127
128 --Initialize the return code
129 retcode := 0;
130
131 --Check if the workflow name and process name exists
132 IF p_wf_name IS NULL OR p_wf_process IS NULL THEN
133 okc_api.set_message(p_app_name => G_APP_NAME,
134 p_msg_name => G_PROCESS_NOTFOUND,
135 p_token1 => G_WF_NAME_TOKEN,
136 p_token1_value => P_WF_NAME,
137 p_token2 => G_WF_P_NAME_TOKEN,
138 p_token2_value => P_WF_PROCESS);
139 IF (l_debug = 'Y') THEN
140 OKC_DEBUG.log('20: WorkFlow Name OR Process Name do not exist ....');
141 OKC_DEBUG.log('30: WorkFlow Name ' || p_wf_name || ' OR Process Name '|| p_wf_process);
142 END IF;
143 RAISE OKC_API.G_EXCEPTION_ERROR;
144 END IF;
145 --Check if the cursor is already open
146 IF alert_cur%ISOPEN THEN
147 CLOSE alert_cur;
148 END IF;
149 FOR alert_rec in alert_cur LOOP
150 BEGIN
151 IF (l_debug = 'Y') THEN
152 OKC_DEBUG.log('40: In the alert_rec LOOP ....');
153 END IF;
154 savepoint task_alert_PVT;
155 l_object_version_number := alert_rec.object_version_number;
156 l_task_id := alert_rec.task_id;
157 l_task_number := alert_rec.task_number;
158 l_task_name := alert_rec.task_name;
159 l_source_object_id := alert_rec.source_object_id;
160 l_owner_id := alert_rec.owner_id;
161 l_planned_end_date := alert_rec.planned_end_date;
162 l_notification_period := alert_rec.notification_period;
163 l_tve_id := alert_rec.tve_id;
164
165 IF (l_debug = 'Y') THEN
166 OKC_DEBUG.log('50: l_object_version_number is ... '|| alert_rec.object_version_number);
167 OKC_DEBUG.log('60: l_task_id ... '|| alert_rec.task_id);
168 OKC_DEBUG.log('70: l_task_number ... '|| alert_rec.task_number);
169 OKC_DEBUG.log('80: l_task_name ... '|| alert_rec.task_name);
170 OKC_DEBUG.log('90: l_source_object_id ... '|| alert_rec.source_object_id);
171 OKC_DEBUG.log('100: l_owner_id ... '|| alert_rec.owner_id);
172 OKC_DEBUG.log('110: l_planned_end_date ... '|| alert_rec.planned_end_date);
173 OKC_DEBUG.log('120: l_notification_period ... '|| alert_rec.notification_period);
174 END IF;
175
176 --If the planned date - current date is less than or equal to the notification period
177 --send out notifications to all the task owners
178
179 IF (TRUNC(l_planned_end_date) - TRUNC(sysdate) <= l_notification_period) THEN
180
181 --Select all the escalation owner id's
182 IF NOT escal_owner_id%ISOPEN THEN
183 --Get escalation owner1 ID
184 OPEN escal_owner_id(l_tve_id);
185 FETCH escal_owner_id into
186 l_escalation_owner1_id, l_escalation_owner2_id, l_contract_id;
187 CLOSE escal_owner_id;
188 END IF;
189
190 IF (l_debug = 'Y') THEN
191 OKC_DEBUG.log('130: l_escalation_owner1_id ... '|| l_escalation_owner1_id);
192 OKC_DEBUG.log('140: l_escalation_owner2_id ... '|| l_escalation_owner2_id);
193 OKC_DEBUG.log('150: l_contract_id ... '|| l_contract_id);
194 END IF;
195
196 --Select the owner of the Task
197 IF NOT escal_owner_cur%ISOPEN THEN
198 --Get Task Owner
199 OPEN escal_owner_cur(l_owner_id);
200 FETCH escal_owner_cur into l_owner_name, l_dummy;
201 CLOSE escal_owner_cur;
202 END IF;
203
204 IF (l_debug = 'Y') THEN
205 OKC_DEBUG.log('151: l_owner_name ... '|| l_owner_name);
206 OKC_DEBUG.log('160: l_dummy ... '|| l_dummy);
207 END IF;
208
209 --Select escalation owner1
210 IF NOT escal_owner_cur%ISOPEN THEN
211 --Get escalation owner1
212 OPEN escal_owner_cur(l_escalation_owner1_id);
213 FETCH escal_owner_cur into l_escalate_owner1, l_escalate_name;
214 CLOSE escal_owner_cur;
215 END IF;
216
217 IF (l_debug = 'Y') THEN
218 OKC_DEBUG.log('161: l_escalate_owner1 ... '|| l_escalate_owner1);
219 OKC_DEBUG.log('162: l_escalate_name ... '|| l_escalate_name);
220 END IF;
221
222 --Select the item key
223 select okc_wf_notify_s1.nextval
224 into l_item_key
225 from dual;
226
227 l_item_key := l_item_key || l_task_id;
228 l_item_type := p_wf_name;
229 l_process := p_wf_process;
230
231 IF (l_debug = 'Y') THEN
232 OKC_DEBUG.log('170: l_item_key ... '|| l_item_key);
233 OKC_DEBUG.log('180: l_item_type ... '|| l_item_type);
234 OKC_DEBUG.log('190: l_process ... '|| l_process);
235 END IF;
236
237 --Launch The workflow to send notifications
238 WF_ENGINE.CREATEPROCESS(L_ITEM_TYPE, L_ITEM_KEY, L_PROCESS);
239
240 IF (l_debug = 'Y') THEN
241 OKC_DEBUG.log('200: Launching the Workflow to send notification .....');
242 END IF;
243
244 --set item attributes;
245 WF_ENGINE.Setitemattrtext(itemtype => l_item_type,
246 itemkey => l_item_key,
247 aname => 'TASK_NAME',
248 avalue => l_task_name);
249
250 IF (l_debug = 'Y') THEN
251 OKC_DEBUG.log('210: Setting Item Attribute TASK_NAME with '|| l_task_name);
252 END IF;
253
254 WF_ENGINE.Setitemattrtext(itemtype => l_item_type,
255 itemkey => l_item_key,
256 aname => 'TASK_OWNER',
257 avalue => l_owner_name);
258
259 IF (l_debug = 'Y') THEN
260 OKC_DEBUG.log('220: Setting Item Attribute TASK_OWNER with '|| l_owner_name);
261 END IF;
262
263 WF_ENGINE.Setitemattrdate(itemtype => l_item_type,
264 itemkey => l_item_key,
265 aname => 'DUE_DATE',
266 avalue => l_planned_end_date);
267
268 IF (l_debug = 'Y') THEN
269 OKC_DEBUG.log('230: Setting Item Attribute DUE_DATE with '|| l_planned_end_date);
270 END IF;
271
272 WF_ENGINE.Setitemattrtext(itemtype => l_item_type,
273 itemkey => l_item_key,
274 aname => 'ESCALATE_OWNER',
275 avalue => l_escalate_name);
276
277 IF (l_debug = 'Y') THEN
278 OKC_DEBUG.log('240: Setting Item Attribute ESCALATE_OWNER with '|| l_escalate_name);
279 END IF;
280
281 WF_ENGINE.Setitemattrtext(itemtype => l_item_type,
282 itemkey => l_item_key,
283 aname => 'DISPLAY_TASK_OWNER',
284 avalue => l_dummy);
285
286 IF (l_debug = 'Y') THEN
287 OKC_DEBUG.log('250: Setting Item Attribute DISPLAY_TASK_OWNER with '|| l_dummy);
288 END IF;
289
290 WF_ENGINE.Setitemattrnumber(itemtype => l_item_type,
291 itemkey => l_item_key,
292 aname => 'TASK_ID',
293 avalue => l_task_id);
294
295 IF (l_debug = 'Y') THEN
296 OKC_DEBUG.log('260: Setting Item Attribute TASK_ID with '|| l_task_id);
297 END IF;
298
299 WF_ENGINE.Setitemattrnumber(itemtype => l_item_type,
300 itemkey => l_item_key,
301 aname => 'CONTRACT_ID',
302 avalue => l_contract_id);
303
304 IF (l_debug = 'Y') THEN
305 OKC_DEBUG.log('270: Setting Item Attribute CONTRACT_ID with '|| l_contract_id);
306 END IF;
307
308 --Start the workflow process
309 WF_ENGINE.STARTPROCESS(l_item_type, l_item_key);
310
311 IF (l_debug = 'Y') THEN
312 OKC_DEBUG.log('280: started workflow process .....');
313 END IF;
314
315 -- commit;
316
317 l_return_status := OKC_API.START_ACTIVITY(
318 l_api_name,
319 G_PKG_NAME,
320 p_init_msg_list,
321 g_api_version,
322 p_api_version,
323 G_LEVEL,
324 g_return_status);
325 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
326 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
327 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
328 RAISE OKC_API.G_EXCEPTION_ERROR;
329 END IF;
330
331 IF (l_debug = 'Y') THEN
332 OKC_DEBUG.log('290: before OKC_TASK_PUB.update_task .....');
333 END IF;
334
335 --Update workflow_process_id in the tasks table
336 OKC_TASK_PUB.update_task(p_api_version => g_api_version,
337 p_object_version_number => l_object_version_number,
338 p_init_msg_list => p_init_msg_list,
339 p_task_id => l_task_id,
340 p_task_number => l_task_number,
341 p_workflow_process_id => l_item_key,
342 x_return_status => g_return_status,
343 x_msg_count => g_msg_count,
344 x_msg_data => g_msg_data);
345
346 IF (l_debug = 'Y') THEN
347 OKC_DEBUG.log('300: after OKC_TASK_PUB.update_task return_status is '|| g_return_status );
348 END IF;
349
350 IF (g_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
351 l_failure_count := l_failure_count + 1;
352 rollback to task_alert_PVT;
353 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
354 ELSIF (g_return_status = OKC_API.G_RET_STS_ERROR) THEN
355 l_failure_count := l_failure_count + 1;
356 rollback to task_alert_PVT;
357 raise OKC_API.G_EXCEPTION_ERROR;
358 ELSIF (g_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
359 l_success_count := l_success_count + 1;
360 commit;
361 END IF;
362 END IF;
363 EXCEPTION
364 WHEN OKC_API.G_EXCEPTION_ERROR THEN
365 IF (l_debug = 'Y') THEN
366 OKC_DEBUG.log(' 400:Exception Error in task_alert...', 2);
367 OKC_DEBUG.Reset_Indentation;
368 END IF;
369 FND_FILE.PUT_LINE( FND_FILE.LOG,'Task:'||l_task_number);
370 get_fnd_msg_stack(' Exception Error in task_alert is '||g_msg_data);
371
372 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
373 IF (l_debug = 'Y') THEN
374 OKC_DEBUG.log(' 400:Unexcepted Error in task_alert...', 2);
375 OKC_DEBUG.Reset_Indentation;
376 END IF;
377 FND_FILE.PUT_LINE( FND_FILE.LOG,'Task:'||l_task_number);
378 get_fnd_msg_stack(' Unexpected error in task_alert is '||g_msg_data);
379
380 WHEN OTHERS THEN
381 IF (l_debug = 'Y') THEN
382 OKC_DEBUG.log(' 400:Other Exception Error in task_alert...', 2);
383 OKC_DEBUG.Reset_Indentation;
384 END IF;
385 FND_FILE.PUT_LINE( FND_FILE.LOG,'Task:'||l_task_number);
386 retcode := 2;
387 FND_FILE.PUT_LINE( FND_FILE.LOG,substr(sqlerrm,1,250));
388 get_fnd_msg_stack(' Other Exception Error in task_alert is '||g_msg_data);
389 exit;
390 END;
391 END LOOP;
392 OKC_API.END_ACTIVITY(g_msg_count, g_msg_data);
393
394 IF (l_debug = 'Y') THEN
395 OKC_DEBUG.log('400: Exiting task_alert...', 2);
396 OKC_DEBUG.Reset_Indentation;
397 END IF;
398 FND_FILE.PUT_LINE( FND_FILE.LOG,'Success Count:'||l_success_count);
399 FND_FILE.PUT_LINE( FND_FILE.LOG,'Failure Count:'||l_failure_count);
400
401 EXCEPTION
402 WHEN OKC_API.G_EXCEPTION_ERROR THEN
403 IF (l_debug = 'Y') THEN
404 OKC_DEBUG.log(' 400:Exception Error in task_alert...', 2);
405 OKC_DEBUG.Reset_Indentation;
406 END IF;
407 FND_FILE.PUT_LINE( FND_FILE.LOG,'Task:'||l_task_number);
408
409 retcode := 2;
410 errbuf := substr(sqlerrm,1,250);
411 g_return_status := OKC_API.HANDLE_EXCEPTIONS
412 (l_api_name,
413 G_PKG_NAME,
414 'OKC_API.G_RET_STS_ERROR',
415 g_msg_count,
416 g_msg_data,
417 G_LEVEL);
418
419 get_fnd_msg_stack(' Exception Error in task_alert is '||g_msg_data);
420
421 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
422 IF (l_debug = 'Y') THEN
423 OKC_DEBUG.log(' 400:Unexcepted Error in task_alert...', 2);
424 OKC_DEBUG.Reset_Indentation;
425 END IF;
426 FND_FILE.PUT_LINE( FND_FILE.LOG,'Task:'||l_task_number);
427
428 retcode := 2;
429 errbuf := substr(sqlerrm,1,250);
430 g_return_status := OKC_API.HANDLE_EXCEPTIONS
431 (l_api_name,
432 G_PKG_NAME,
433 'OKC_API.G_RET_STS_UNEXP_ERROR',
434 g_msg_count,
435 g_msg_data,
436 G_LEVEL);
437
438 get_fnd_msg_stack(' Unexpected error in task_alert is '||g_msg_data);
439
440 WHEN OTHERS THEN
441 IF (l_debug = 'Y') THEN
442 OKC_DEBUG.log(' 400:Other Exception Error in task_alert...', 2);
443 OKC_DEBUG.Reset_Indentation;
444 END IF;
445 FND_FILE.PUT_LINE( FND_FILE.LOG,'Task:'||l_task_number);
446
447 retcode := 2;
448 errbuf := substr(sqlerrm,1,250);
449 g_return_status := OKC_API.HANDLE_EXCEPTIONS
450 (l_api_name,
451 G_PKG_NAME,
452 'OTHERS',
453 g_msg_count,
454 g_msg_data,
455 G_LEVEL);
456
457 get_fnd_msg_stack(' Other Exception Error in task_alert is '||g_msg_data);
458 END task_alert;
459
460 --------------------------------------------------------------------------------
461 -- Start of comments
462 -- Procedure Name : task_escalation1
463 -- Description : This Procedure escalates the task to the manager if
464 -- not completed by the task owner. It also updates the
465 -- workflow process id and alarm fired count in tasks table
466 -- Version : 1.0
467 -- End of comments
468 --------------------------------------------------------------------------------
469 PROCEDURE task_escalation1(
470 errbuf OUT NOCOPY VARCHAR2,
471 retcode OUT NOCOPY VARCHAR2,
472 p_api_version IN NUMBER,
473 p_init_msg_list IN VARCHAR2 ,
474 p_wf_name IN VARCHAR2,
475 p_wf_process IN VARCHAR2) IS
476
477 -- Replaced name with seeded ids to avoid translation issues - Bug 1683539
478 -- Read OKCSCHRULE - Contract Schedule Rule - Bug 1683539
479 CURSOR escalate_owner1_cur is
480 Select jtb.object_version_number
481 ,jtb.task_id
482 ,jtb.task_number
483 ,jtb.task_name
484 ,jtb.source_object_id
485 ,jtb.owner_id
486 ,jtb.planned_end_date
487 ,jtb.actual_end_date
488 ,jtb.alarm_interval
489 ,res.tve_id
490 from jtf_tasks_vl jtb,
491 jtf_task_types_tl jttl,
492 jtf_task_statuses_tl jtsl,
493 okc_resolved_timevalues res
494 where jtb.actual_end_date IS NULL
495 and res.id = jtb.source_object_id
496 and jtb.workflow_process_id IS NOT NULL
497 and jtb.alarm_fired_count IS NULL
498 and jtb.source_object_type_code = 'OKC_RESTIME'
499 and jtb.task_type_id = jttl.task_type_id
500 and jttl.task_type_id = 23
501 --and jttl.name = 'OKCSCHRULE'
502 and jtb.task_status_id = jtsl.task_status_id
503 and jtsl.task_status_id = 10;
504 --and jtsl.name = 'Open';
505
506 l_workflow_process_id jtf_tasks_v.workflow_process_id%TYPE;
507 l_object_version_number NUMBER;
508 l_tve_id NUMBER;
509 l_contract_id okc_rules_b.dnz_chr_id%TYPE;
510 l_task_id jtf_tasks_b.task_id%TYPE;
511 l_task_number jtf_tasks_b.task_number%TYPE;
512 l_task_name jtf_tasks_tl.task_name%TYPE;
513 l_planned_end_date jtf_tasks_b.planned_end_date%TYPE;
514 l_notification_period jtf_tasks_b.notification_period%TYPE;
515 l_owner_id jtf_tasks_b.owner_id%TYPE;
516 l_owner_name fnd_user.user_name%TYPE;
517 l_actual_end_date jtf_tasks_b.actual_end_date%TYPE;
518 l_escalation_owner1_id okc_rules_b.rule_information6%TYPE;
519 l_escalation_owner2_id okc_rules_b.rule_information7%TYPE;
520 l_escalate_owner1 fnd_user.user_name%TYPE;
521 l_escalate_owner2 fnd_user.user_name%TYPE;
522 l_escalate_name VARCHAR2(100);
523 l_dummy VARCHAR2(100);
524 l_alarm_interval jtf_tasks_v.alarm_interval%TYPE;
525 l_return_status VARCHAR2(3);
526 l_planned_date jtf_tasks_b.planned_end_date%TYPE;
527 l_source_object_id jtf_tasks_b.source_object_id%TYPE;
528 l_item_type VARCHAR2(30);
529 l_item_key NUMBER;
530 l_process VARCHAR2(30);
531 l_api_name CONSTANT VARCHAR2(30) := 'task_escalation1';
532 l_success_count NUMBER := 0;
533 l_failure_count NUMBER := 0;
534
535 BEGIN
536 IF (l_debug = 'Y') THEN
537 OKC_DEBUG.set_indentation(l_api_name);
538 OKC_DEBUG.log('510: Entered task_escalation1', 2);
539 END IF;
540
541 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
542 G_PKG_NAME,
543 p_init_msg_list,
544 g_api_version,
545 p_api_version,
546 G_LEVEL,
547 g_return_status);
548 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
549 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
550 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
551 RAISE OKC_API.G_EXCEPTION_ERROR;
552 END IF;
553
554 --Initialize the return code
555 retcode := 0;
556
557 --Check if the workflow name and process_name exists
558 IF p_wf_name IS NULL OR p_wf_process IS NULL THEN
559 okc_api.set_message(p_app_name => G_APP_NAME,
560 p_msg_name => G_PROCESS_NOTFOUND,
561 p_token1 => G_WF_NAME_TOKEN,
562 p_token1_value => P_WF_NAME,
563 p_token2 => G_WF_P_NAME_TOKEN,
564 p_token2_value => P_WF_PROCESS);
565
566 IF (l_debug = 'Y') THEN
567 OKC_DEBUG.log('520: WorkFlow Name OR Process Name do not exist ....');
568 OKC_DEBUG.log('530: WorkFlow Name ' || p_wf_name || ' OR Process Name '|| p_wf_process);
569 END IF;
570
571 RAISE OKC_API.G_EXCEPTION_ERROR;
572 END IF;
573
574 --Check if the cursor is already open
575 IF escalate_owner1_cur%ISOPEN THEN
576 CLOSE escalate_owner1_cur;
577 END IF;
578 FOR escalate_owner1_rec in escalate_owner1_cur LOOP
579 BEGIN
580
581 IF (l_debug = 'Y') THEN
582 OKC_DEBUG.log('540: In the escalate_owner1_rec LOOP ....');
583 END IF;
584 savepoint task_alert_pvt;
585
586 l_object_version_number := escalate_owner1_rec.object_version_number;
587 l_task_id := escalate_owner1_rec.task_id;
588 l_task_number := escalate_owner1_rec.task_number;
589 l_task_name := escalate_owner1_rec.task_name;
590 l_source_object_id := escalate_owner1_rec.source_object_id;
591 l_owner_id := escalate_owner1_rec.owner_id;
592 l_planned_end_date := escalate_owner1_rec.planned_end_date;
593 l_actual_end_date := escalate_owner1_rec.actual_end_date;
594 l_alarm_interval := escalate_owner1_rec.alarm_interval;
595 l_tve_id := escalate_owner1_rec.tve_id;
596
597 IF (l_debug = 'Y') THEN
598 OKC_DEBUG.log('550: l_object_version_number is ... '|| escalate_owner1_rec.object_version_number);
599 OKC_DEBUG.log('560: l_task_id ... '|| escalate_owner1_rec.task_id);
600 OKC_DEBUG.log('570: l_task_number ... '|| escalate_owner1_rec.task_number);
601 OKC_DEBUG.log('580: l_task_name ... '|| escalate_owner1_rec.task_name);
602 OKC_DEBUG.log('590: l_source_object_id ... '|| escalate_owner1_rec.source_object_id);
603 OKC_DEBUG.log('600: l_owner_id ... '|| escalate_owner1_rec.owner_id);
604 OKC_DEBUG.log('610: l_planned_end_date ... '|| escalate_owner1_rec.planned_end_date);
605 OKC_DEBUG.log('615: l_actual_end_date ... '|| escalate_owner1_rec.actual_end_date);
606 OKC_DEBUG.log('620: l_alarm_interval ... '|| escalate_owner1_rec.alarm_interval);
607 END IF;
608
609 -- If the current date is greater than planned end date + alarm interval
610 -- and the task is incomplete then escalate the task to the manager
611
612 IF (TRUNC(sysdate) >= TRUNC(l_planned_end_date + l_alarm_interval)) THEN
613 --Get escalation owner id
614 IF NOT escal_owner_id%ISOPEN THEN
615 --Get escalation owner1 ID
616 OPEN escal_owner_id(l_tve_id);
617 FETCH escal_owner_id into
618 l_escalation_owner1_id, l_escalation_owner2_id, l_contract_id;
619 CLOSE escal_owner_id;
620 END IF;
621
622 IF (l_debug = 'Y') THEN
623 OKC_DEBUG.log('630: l_escalation_owner1_id ... '|| l_escalation_owner1_id);
624 OKC_DEBUG.log('640: l_escalation_owner2_id ... '|| l_escalation_owner2_id);
625 OKC_DEBUG.log('650: l_contract_id ... '|| l_contract_id);
626 END IF;
627
628 --Select the owner of the Task
629 IF NOT escal_owner_cur%ISOPEN THEN
630 --Get Task Owner
631 OPEN escal_owner_cur(l_owner_id);
632 FETCH escal_owner_cur into l_dummy, l_owner_name;
633 CLOSE escal_owner_cur;
634 END IF;
635
636 IF (l_debug = 'Y') THEN
637 OKC_DEBUG.log('651: l_owner_name ... '|| l_owner_name);
638 OKC_DEBUG.log('660: l_dummy ... '|| l_dummy);
639 END IF;
640
641 --Get escalation owner1
642 IF NOT escal_owner_cur%ISOPEN THEN
643 OPEN escal_owner_cur(l_escalation_owner1_id);
644 FETCH escal_owner_cur into l_escalate_owner1, l_dummy;
645 CLOSE escal_owner_cur;
646 END IF;
647
648 IF (l_debug = 'Y') THEN
649 OKC_DEBUG.log('661: l_escalate_owner1 ... '|| l_escalate_owner1);
650 OKC_DEBUG.log('662: l_dummy ... '|| l_dummy);
651 END IF;
652
653 --Get escalation owner2
654 IF NOT escal_owner_cur%ISOPEN THEN
655 OPEN escal_owner_cur(l_escalation_owner2_id);
656 FETCH escal_owner_cur into l_escalate_owner2, l_escalate_name;
657 CLOSE escal_owner_cur;
658 END IF;
659
660 IF (l_debug = 'Y') THEN
661 OKC_DEBUG.log('663: l_escalate_owner2 ... '|| l_escalate_owner2);
662 OKC_DEBUG.log('664: l_escalate_name ... '|| l_escalate_name);
663 END IF;
664
665 select okc_wf_notify_s1.nextval
666 into l_item_key
667 from dual;
668
669 l_item_key := l_item_key || l_task_id;
670 l_item_type := p_wf_name;
671 l_process := p_wf_process;
672
673 IF (l_debug = 'Y') THEN
674 OKC_DEBUG.log('670: l_item_key ... '|| l_item_key);
675 OKC_DEBUG.log('680: l_item_type ... '|| l_item_type);
676 OKC_DEBUG.log('690: l_process ... '|| l_process);
677 END IF;
678
679 --Launch the workflow to escalate the incomplete tasks to the manager
680 WF_ENGINE.CREATEPROCESS(L_ITEM_TYPE, L_ITEM_KEY, L_PROCESS);
681
682 IF (l_debug = 'Y') THEN
683 OKC_DEBUG.log('700: Launching the Workflow to send notification .....');
684 END IF;
685
686 --set item attributes;
687 WF_ENGINE.Setitemattrtext(itemtype => l_item_type,
688 itemkey => l_item_key,
689 aname => 'TASK_NAME',
690 avalue => l_task_name);
691
692 IF (l_debug = 'Y') THEN
693 OKC_DEBUG.log('710: Setting Item Attribute TASK_NAME with '|| l_task_name);
694 END IF;
695
696 WF_ENGINE.Setitemattrtext(itemtype => l_item_type,
697 itemkey => l_item_key,
698 aname => 'TASK_OWNER',
699 avalue => l_escalate_owner1);
700
701 IF (l_debug = 'Y') THEN
702 OKC_DEBUG.log('720: Setting Item Attribute TASK_OWNER with '|| l_escalate_owner1);
703 END IF;
704
705 WF_ENGINE.Setitemattrdate(itemtype => l_item_type,
706 itemkey => l_item_key,
707 aname => 'DUE_DATE',
708 avalue => l_planned_end_date);
709
710 IF (l_debug = 'Y') THEN
711 OKC_DEBUG.log('730: Setting Item Attribute DUE_DATE with '|| l_planned_end_date);
712 END IF;
713
714 WF_ENGINE.Setitemattrtext(itemtype => l_item_type,
715 itemkey => l_item_key,
716 aname => 'ESCALATE_OWNER',
717 avalue => l_escalate_name);
718
719 IF (l_debug = 'Y') THEN
720 OKC_DEBUG.log('740: Setting Item Attribute ESCALATE_OWNER with '|| l_escalate_name);
721 END IF;
722
723 WF_ENGINE.Setitemattrtext(itemtype => l_item_type,
724 itemkey => l_item_key,
725 aname => 'DISPLAY_TASK_OWNER',
726 avalue => l_owner_name);
727
728 IF (l_debug = 'Y') THEN
729 OKC_DEBUG.log('750: Setting Item Attribute DISPLAY_TASK_OWNER with '|| l_owner_name);
730 END IF;
731
732 WF_ENGINE.Setitemattrnumber(itemtype => l_item_type,
733 itemkey => l_item_key,
734 aname => 'TASK_ID',
735 avalue => l_task_id);
736
737 IF (l_debug = 'Y') THEN
738 OKC_DEBUG.log('760: Setting Item Attribute TASK_ID with '|| l_task_id);
739 END IF;
740
741 WF_ENGINE.Setitemattrnumber(itemtype => l_item_type,
742 itemkey => l_item_key,
743 aname => 'CONTRACT_ID',
744 avalue => l_contract_id);
745
746 IF (l_debug = 'Y') THEN
747 OKC_DEBUG.log('770: Setting Item Attribute CONTRACT_ID with '|| l_contract_id);
748 END IF;
749 --Start the workflow
750 WF_ENGINE.STARTPROCESS(l_item_type, l_item_key);
751
752 IF (l_debug = 'Y') THEN
753 OKC_DEBUG.log('780: started workflow process .....');
754 END IF;
755
756 -- commit;
757
758 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
759 G_PKG_NAME,
760 p_init_msg_list,
761 g_api_version,
762 p_api_version,
763 G_LEVEL,
764 g_return_status);
765 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
766 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
767 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
768 RAISE OKC_API.G_EXCEPTION_ERROR;
769 END IF;
770
771 IF (l_debug = 'Y') THEN
772 OKC_DEBUG.log('790: before OKC_TASK_PUB.update_task .....');
773 END IF;
774
775 --Update workflow_process_id, alarm_fired_count in the tasks table
776 OKC_TASK_PUB.update_task(p_api_version => g_api_version,
777 p_object_version_number => l_object_version_number,
778 p_init_msg_list => p_init_msg_list,
779 p_task_id => l_task_id,
780 p_task_number => l_task_number,
781 p_workflow_process_id => l_item_key,
782 p_alarm_fired_count => 1,
783 x_return_status => g_return_status,
784 x_msg_count => g_msg_count,
785 x_msg_data => g_msg_data);
786
787 IF (l_debug = 'Y') THEN
788 OKC_DEBUG.log('800: after OKC_TASK_PUB.update_task return_status is '|| g_return_status);
789 END IF;
790
791 IF (g_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
792 l_failure_count := l_failure_count + 1;
793 rollback to task_alert_pvt;
794 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
795 ELSIF (g_return_status = OKC_API.G_RET_STS_ERROR) THEN
796 l_failure_count := l_failure_count + 1;
797 rollback to task_alert_pvt;
798 raise OKC_API.G_EXCEPTION_ERROR;
799 ELSIF (g_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
800 l_success_count := l_success_count + 1;
801 commit;
802 END IF;
803 END IF;
804 EXCEPTION
805 WHEN OKC_API.G_EXCEPTION_ERROR THEN
806 IF (l_debug = 'Y') THEN
807 OKC_DEBUG.log(' 400:Exception Error in task_alert...', 2);
808 OKC_DEBUG.Reset_Indentation;
809 END IF;
810 FND_FILE.PUT_LINE( FND_FILE.LOG,'Task:'||l_task_number);
811 get_fnd_msg_stack(' Exception Error in task_alert is '||g_msg_data);
812
813 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
814 IF (l_debug = 'Y') THEN
815 OKC_DEBUG.log(' 400:Unexcepted Error in task_alert...', 2);
816 OKC_DEBUG.Reset_Indentation;
817 END IF;
818 FND_FILE.PUT_LINE( FND_FILE.LOG,'Task:'||l_task_number);
819 get_fnd_msg_stack(' Unexpected error in task_alert is '||g_msg_data);
820
821 WHEN OTHERS THEN
822 IF (l_debug = 'Y') THEN
823 OKC_DEBUG.log(' 400:Other Exception Error in task_alert...', 2);
824 OKC_DEBUG.Reset_Indentation;
825 END IF;
826 FND_FILE.PUT_LINE( FND_FILE.LOG,'Task:'||l_task_number);
827
828 retcode := 2;
829 FND_FILE.PUT_LINE( FND_FILE.LOG,substr(sqlerrm,1,250));
830
831 get_fnd_msg_stack(' Other Exception Error in task_alert is '||g_msg_data);
832 exit;
833 END;
834 END LOOP;
835 OKC_API.END_ACTIVITY(g_msg_count, g_msg_data);
836
837 IF (l_debug = 'Y') THEN
838 OKC_DEBUG.log('900: Exiting task_task_escalation1...', 2);
839 OKC_DEBUG.Reset_Indentation;
840 END IF;
841 FND_FILE.PUT_LINE( FND_FILE.LOG,'Success Count:'||l_success_count);
842 FND_FILE.PUT_LINE( FND_FILE.LOG,'Failure Count:'||l_failure_count);
843
844 EXCEPTION
845 WHEN OKC_API.G_EXCEPTION_ERROR THEN
846 IF (l_debug = 'Y') THEN
847 OKC_DEBUG.log(' 900:Exception Error in task_escalation1...', 2);
848 OKC_DEBUG.Reset_Indentation;
849 END IF;
850 FND_FILE.PUT_LINE( FND_FILE.LOG,'Task:'||l_task_number);
851
852 retcode := 2;
853 errbuf := substr(sqlerrm,1,250);
854 g_return_status := OKC_API.HANDLE_EXCEPTIONS
855 (l_api_name,
856 G_PKG_NAME,
857 'OKC_API.G_RET_STS_ERROR',
858 g_msg_count,
859 g_msg_data,
860 G_LEVEL);
861
862 get_fnd_msg_stack(' Exception Error in task_escalation1 is '||g_msg_data);
863
864 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
865 IF (l_debug = 'Y') THEN
866 OKC_DEBUG.log(' 900:Unexcepted Error in task_escalation1...', 2);
867 OKC_DEBUG.Reset_Indentation;
868 END IF;
869 FND_FILE.PUT_LINE( FND_FILE.LOG,'Task:'||l_task_number);
870
871 retcode := 2;
872 errbuf := substr(sqlerrm,1,250);
873 g_return_status := OKC_API.HANDLE_EXCEPTIONS
874 (l_api_name,
875 G_PKG_NAME,
876 'OKC_API.G_RET_STS_UNEXP_ERROR',
877 g_msg_count,
878 g_msg_data,
879 G_LEVEL);
880
881 get_fnd_msg_stack(' Unexpected error in task_escaltion1 is '||g_msg_data);
882
883 WHEN OTHERS THEN
884 IF (l_debug = 'Y') THEN
885 OKC_DEBUG.log(' 900:Other Exception Error in task_escalation1...', 2);
886 OKC_DEBUG.Reset_Indentation;
887 END IF;
888 FND_FILE.PUT_LINE( FND_FILE.LOG,'Task:'||l_task_number);
889
890 retcode := 2;
891 errbuf := substr(sqlerrm,1,250);
892 g_return_status := OKC_API.HANDLE_EXCEPTIONS
893 (l_api_name,
894 G_PKG_NAME,
895 'OTHERS',
896 g_msg_count,
897 g_msg_data,
898 G_LEVEL);
899
900 get_fnd_msg_stack(' Other Exception Error in task_escalation1 is '||g_msg_data);
901
902 END task_escalation1;
903
904 ------------------------------------------------------------------------------------------------
905 -- Start of comments
906 -- Procedure Name : task_escalation2
907 -- Description : This Procedure escalates the task to to level 2(manager) if not
908 -- completed by the task owner
909 -- It also updates the workflow process id and alarm fired count in tasks table
910 -- Version : 1.0
911 -- End of comments
912 ------------------------------------------------------------------------------------------------
913
914 PROCEDURE task_escalation2(errbuf OUT NOCOPY VARCHAR2,
915 retcode OUT NOCOPY VARCHAR2,
916 p_api_version IN NUMBER,
917 p_init_msg_list IN VARCHAR2 ,
918 p_wf_name IN VARCHAR2,
919 p_wf_process IN VARCHAR2) IS
920 -- Replaced name with seeded ids to avoid translation issues - Bug 1683539
921 -- Read OKCSCHRULE - Contract Schedule Rule - Bug 1683539
922 CURSOR escalate_owner2_cur is
923 Select jtb.object_version_number
924 ,jtb.task_id
925 ,jtb.task_number
926 ,jtb.task_name
927 ,jtb.owner_id
928 ,jtb.source_object_id
929 ,jtb.planned_end_date
930 ,jtb.actual_end_date
931 ,jtb.alarm_interval
932 ,res.tve_id
933 from jtf_tasks_vl jtb,
934 jtf_task_types_vl jttl,
935 jtf_task_statuses_vl jtsl,
936 okc_resolved_timevalues res
937 where jtb.actual_end_date IS NULL
938 and res.id = jtb.source_object_id
939 and jtb.workflow_process_id IS NOT NULL
940 and jtb.alarm_fired_count = 1
941 and jtb.source_object_type_code = 'OKC_RESTIME'
942 and jtb.task_type_id = jttl.task_type_id
943 and jttl.task_type_id = 23
944 --and jttl.name = 'OKCSCHRULE'
945 and jtb.task_status_id = jtsl.task_status_id
946 and jtsl.task_status_id = 10;
947 --and jtsl.name = 'Open';
948
949 l_workflow_process_id jtf_tasks_v.workflow_process_id%TYPE;
950 l_object_version_number NUMBER;
951 l_contract_id okc_rules_b.dnz_chr_id%TYPE;
952 l_task_id jtf_tasks_b.task_id%TYPE;
953 l_task_number jtf_tasks_b.task_number%TYPE;
954 l_task_name jtf_tasks_tl.task_name%TYPE;
955 l_planned_end_date jtf_tasks_b.planned_end_date%TYPE;
956 l_notification_period jtf_tasks_b.notification_period%TYPE;
957 l_owner_id jtf_tasks_b.owner_id%TYPE;
958 l_owner_name fnd_user.user_name%TYPE;
959 l_actual_end_date jtf_tasks_b.actual_end_date%TYPE;
960 l_escalation_owner1_id okc_rules_b.rule_information6%TYPE;
961 l_escalation_owner2_id okc_rules_b.rule_information7%TYPE;
962 l_escalate_name VARCHAR2(100);
963 l_dummy VARCHAR2(100);
964 l_escalate_owner1 fnd_user.user_name%TYPE;
965 l_escalate_owner2 fnd_user.user_name%TYPE;
966 l_alarm_interval jtf_tasks_b.alarm_interval%TYPE;
967 l_return_status VARCHAR2(3);
968 l_planned_date jtf_tasks_b.planned_end_date%TYPE;
969 l_source_object_id jtf_tasks_b.source_object_id%TYPE;
970 l_item_type VARCHAR2(30);
971 l_item_key NUMBER;
972 l_tve_id NUMBER;
973 l_process VARCHAR2(30);
974 l_api_name CONSTANT VARCHAR2(30) := 'task_escalation2';
975 l_success_count NUMBER := 0;
976 l_failure_count NUMBER := 0;
977
978 BEGIN
979 IF (l_debug = 'Y') THEN
980 OKC_DEBUG.set_indentation(l_api_name);
981 OKC_DEBUG.log('1010: Entered task_escalation2', 2);
982 END IF;
983
984 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
985 G_PKG_NAME,
986 p_init_msg_list,
987 g_api_version,
988 p_api_version,
989 G_LEVEL,
990 g_return_status);
991 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
992 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
993 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
994 RAISE OKC_API.G_EXCEPTION_ERROR;
995 END IF;
996
997 --Initialize the return code
998 retcode := 0;
999
1000 --Check if the item_type and process_name exists
1001 IF p_wf_name IS NULL OR p_wf_process IS NULL THEN
1002 okc_api.set_message(p_app_name => G_APP_NAME,
1003 p_msg_name => G_PROCESS_NOTFOUND,
1004 p_token1 => G_WF_NAME_TOKEN,
1005 p_token1_value => P_WF_NAME,
1006 p_token2 => G_WF_P_NAME_TOKEN,
1007 p_token2_value => P_WF_PROCESS);
1008
1009 IF (l_debug = 'Y') THEN
1010 OKC_DEBUG.log('1020: WorkFlow Name OR Process Name do not exist ....');
1011 OKC_DEBUG.log('1030: WorkFlow Name ' || p_wf_name || ' OR Process Name '|| p_wf_process);
1012 END IF;
1013
1014 RAISE OKC_API.G_EXCEPTION_ERROR;
1015 END IF;
1016
1017 --Check if the cursor is already open
1018 IF escalate_owner2_cur%ISOPEN THEN
1019 CLOSE escalate_owner2_cur;
1020 END IF;
1021 FOR escalate_owner2_rec in escalate_owner2_cur LOOP
1022 BEGIN
1023
1024 IF (l_debug = 'Y') THEN
1025 OKC_DEBUG.log('1040: In the escalate_owner2_rec LOOP ....');
1026 END IF;
1027 savepoint task_alert_pvt;
1028
1029 l_object_version_number := escalate_owner2_rec.object_version_number;
1030 l_task_id := escalate_owner2_rec.task_id;
1031 l_task_number := escalate_owner2_rec.task_number;
1032 l_task_name := escalate_owner2_rec.task_name;
1033 l_owner_id := escalate_owner2_rec.owner_id;
1034 l_source_object_id := escalate_owner2_rec.source_object_id;
1035 l_planned_end_date := escalate_owner2_rec.planned_end_date;
1036 l_actual_end_date := escalate_owner2_rec.actual_end_date;
1037 l_alarm_interval := escalate_owner2_rec.alarm_interval;
1038 l_tve_id := escalate_owner2_rec.tve_id;
1039
1040 IF (l_debug = 'Y') THEN
1041 OKC_DEBUG.log('1050: l_object_version_number is ... '|| escalate_owner2_rec.object_version_number);
1042 OKC_DEBUG.log('1060: l_task_id ... '|| escalate_owner2_rec.task_id);
1043 OKC_DEBUG.log('1070: l_task_number ... '|| escalate_owner2_rec.task_number);
1044 OKC_DEBUG.log('1080: l_task_name ... '|| escalate_owner2_rec.task_name);
1045 OKC_DEBUG.log('1090: l_source_object_id ... '|| escalate_owner2_rec.source_object_id);
1046 OKC_DEBUG.log('2000: l_owner_id ... '|| escalate_owner2_rec.owner_id);
1047 OKC_DEBUG.log('2010: l_planned_end_date ... '|| escalate_owner2_rec.planned_end_date);
1048 OKC_DEBUG.log('2015: l_actual_end_date ... '|| escalate_owner2_rec.actual_end_date);
1049 OKC_DEBUG.log('2020: l_alarm_interval ... '|| escalate_owner2_rec.alarm_interval);
1050 END IF;
1051
1052 --If current date is greater than or equal to planned date + twice the alarm interval then
1053 --escalate the task to level 2(manager)
1054 IF (TRUNC(sysdate) >= TRUNC(l_planned_end_date + (2 * l_alarm_interval))) THEN
1055 --Get escalation owner2 ID
1056 IF NOT escal_owner_id%ISOPEN THEN
1057 --Get escalation owner1 ID
1058 OPEN escal_owner_id(l_tve_id);
1059 FETCH escal_owner_id into l_escalation_owner1_id,l_escalation_owner2_id,l_contract_id;
1060 CLOSE escal_owner_id;
1061 END IF;
1062
1063 IF (l_debug = 'Y') THEN
1064 OKC_DEBUG.log('1030: l_escalation_owner1_id ... '|| l_escalation_owner1_id);
1065 OKC_DEBUG.log('1040: l_escalation_owner2_id ... '|| l_escalation_owner2_id);
1066 OKC_DEBUG.log('1050: l_contract_id ... '|| l_contract_id);
1067 END IF;
1068
1069 --Select the owner of the Task
1070 IF NOT escal_owner_cur%ISOPEN THEN
1071 --Get Task Owner
1072 OPEN escal_owner_cur(l_owner_id);
1073 FETCH escal_owner_cur into l_dummy, l_owner_name;
1074 CLOSE escal_owner_cur;
1075 END IF;
1076
1077 IF (l_debug = 'Y') THEN
1078 OKC_DEBUG.log('1051: l_owner_name ... '|| l_owner_name);
1079 OKC_DEBUG.log('1060: l_dummy ... '|| l_dummy);
1080 END IF;
1081
1082 --Get escalation owner2
1083 IF NOT escal_owner_cur%ISOPEN THEN
1084 OPEN escal_owner_cur(l_escalation_owner2_id);
1085 FETCH escal_owner_cur into l_escalate_owner2, l_dummy;
1086 CLOSE escal_owner_cur;
1087 END IF;
1088
1089 IF (l_debug = 'Y') THEN
1090 OKC_DEBUG.log('1061: l_escalate_owner2 ... '|| l_escalate_owner2);
1091 OKC_DEBUG.log('1062: l_dummy ... '|| l_dummy);
1092 END IF;
1093
1094 select okc_wf_notify_s1.nextval
1095 into l_item_key
1096 from dual;
1097
1098 l_item_key := l_item_key || l_task_id;
1099 l_item_type := p_wf_name;
1100 l_process := p_wf_process;
1101
1102 IF (l_debug = 'Y') THEN
1103 OKC_DEBUG.log('1070: l_item_key ... '|| l_item_key);
1104 OKC_DEBUG.log('1080: l_item_type ... '|| l_item_type);
1105 OKC_DEBUG.log('1090: l_process ... '|| l_process);
1106 END IF;
1107
1108 --Launch Workflow to escalate the task to level 2 (mananger)
1109 WF_ENGINE.CREATEPROCESS(L_ITEM_TYPE, L_ITEM_KEY, L_PROCESS);
1110
1111 IF (l_debug = 'Y') THEN
1112 OKC_DEBUG.log('2000: Launching the Workflow to send notification .....');
1113 END IF;
1114
1115 --set item attributes;
1116 WF_ENGINE.Setitemattrtext(itemtype => l_item_type,
1117 itemkey => l_item_key,
1118 aname => 'TASK_NAME',
1119 avalue => l_task_name);
1120
1121 IF (l_debug = 'Y') THEN
1122 OKC_DEBUG.log('2010: Setting Item Attribute TASK_NAME with '|| l_task_name);
1123 END IF;
1124
1125 WF_ENGINE.Setitemattrtext(itemtype => l_item_type,
1126 itemkey => l_item_key,
1127 aname => 'TASK_OWNER',
1128 avalue => l_escalate_owner2);
1129
1130 IF (l_debug = 'Y') THEN
1131 OKC_DEBUG.log('2020: Setting Item Attribute TASK_OWNER with '|| l_escalate_owner2);
1132 END IF;
1133
1134 WF_ENGINE.Setitemattrdate(itemtype => l_item_type,
1135 itemkey => l_item_key,
1136 aname => 'DUE_DATE',
1137 avalue => l_planned_end_date);
1138
1139 IF (l_debug = 'Y') THEN
1140 OKC_DEBUG.log('2030: Setting Item Attribute DUE_DATE with '|| l_planned_end_date);
1141 END IF;
1142
1143 WF_ENGINE.Setitemattrtext(itemtype => l_item_type,
1144 itemkey => l_item_key,
1145 aname => 'ESCALATE_OWNER',
1146 avalue => NULL);
1147
1148 IF (l_debug = 'Y') THEN
1149 OKC_DEBUG.log('2040: Setting Item Attribute ESCALATE_OWNER with '|| NULL);
1150 END IF;
1151
1152 WF_ENGINE.Setitemattrtext(itemtype => l_item_type,
1153 itemkey => l_item_key,
1154 aname => 'DISPLAY_TASK_OWNER',
1155 avalue => l_owner_name);
1156
1157 IF (l_debug = 'Y') THEN
1158 OKC_DEBUG.log('2050: Setting Item Attribute DISPLAY_TASK_OWNER with '|| l_owner_name);
1159 END IF;
1160
1161 WF_ENGINE.Setitemattrnumber(itemtype => l_item_type,
1162 itemkey => l_item_key,
1163 aname => 'TASK_ID',
1164 avalue => l_task_id);
1165
1166 IF (l_debug = 'Y') THEN
1167 OKC_DEBUG.log('2060: Setting Item Attribute TASK_ID with '|| l_task_id);
1168 END IF;
1169
1170 WF_ENGINE.Setitemattrnumber(itemtype => l_item_type,
1171 itemkey => l_item_key,
1172 aname => 'CONTRACT_ID',
1173 avalue => l_contract_id);
1174
1175 IF (l_debug = 'Y') THEN
1176 OKC_DEBUG.log('2070: Setting Item Attribute CONTRACT_ID with '|| l_contract_id);
1177 END IF;
1178
1179 --start the workflow
1180 WF_ENGINE.STARTPROCESS(l_item_type, l_item_key);
1181
1182 IF (l_debug = 'Y') THEN
1183 OKC_DEBUG.log('2080: started workflow process .....');
1184 END IF;
1185
1186 -- commit;
1187
1188 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1189 G_PKG_NAME,
1190 p_init_msg_list,
1191 g_api_version,
1192 p_api_version,
1193 G_LEVEL,
1194 g_return_status);
1195 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1196 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1197 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1198 RAISE OKC_API.G_EXCEPTION_ERROR;
1199 END IF;
1200
1201 IF (l_debug = 'Y') THEN
1202 OKC_DEBUG.log('2090: before OKC_TASK_PUB.update_task .....');
1203 END IF;
1204
1205 --Update alarm fired count, workflow_process_id in the tasks table
1206 OKC_TASK_PUB.update_task(p_api_version => g_api_version,
1207 p_object_version_number => l_object_version_number,
1208 p_init_msg_list => p_init_msg_list,
1209 p_task_id => l_task_id,
1210 p_task_number => l_task_number,
1211 p_workflow_process_id => l_item_key,
1212 p_alarm_fired_count => 2,
1213 x_return_status => g_return_status,
1214 x_msg_count => g_msg_count,
1215 x_msg_data => g_msg_data);
1216
1217 IF (l_debug = 'Y') THEN
1218 OKC_DEBUG.log('3000: after OKC_TASK_PUB.update_task return_status is '|| g_return_status);
1219 END IF;
1220
1221 IF (g_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1222 l_failure_count := l_failure_count + 1;
1223 rollback to task_alert_pvt;
1224 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1225 ELSIF (g_return_status = OKC_API.G_RET_STS_ERROR) THEN
1226 l_failure_count := l_failure_count + 1;
1227 rollback to task_alert_pvt;
1228 raise OKC_API.G_EXCEPTION_ERROR;
1229 ELSIF (g_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1230 l_success_count := l_success_count + 1;
1231 commit;
1232 END IF;
1233 END IF;
1234 EXCEPTION
1235 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1236 IF (l_debug = 'Y') THEN
1237 OKC_DEBUG.log(' 400:Exception Error in task_alert...', 2);
1238 OKC_DEBUG.Reset_Indentation;
1239 END IF;
1240 FND_FILE.PUT_LINE( FND_FILE.LOG,'Task:'||l_task_number);
1241 get_fnd_msg_stack(' Exception Error in task_alert is '||g_msg_data);
1242
1243 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1244 IF (l_debug = 'Y') THEN
1245 OKC_DEBUG.log(' 400:Unexcepted Error in task_alert...', 2);
1246 OKC_DEBUG.Reset_Indentation;
1247 END IF;
1248 FND_FILE.PUT_LINE( FND_FILE.LOG,'Task:'||l_task_number);
1249 get_fnd_msg_stack(' Unexpected error in task_alert is '||g_msg_data);
1250
1251 WHEN OTHERS THEN
1252 IF (l_debug = 'Y') THEN
1253 OKC_DEBUG.log(' 400:Other Exception Error in task_alert...', 2);
1254 OKC_DEBUG.Reset_Indentation;
1255 END IF;
1256 FND_FILE.PUT_LINE( FND_FILE.LOG,'Task:'||l_task_number);
1257
1258 retcode := 2;
1259 FND_FILE.PUT_LINE( FND_FILE.LOG,substr(sqlerrm,1,250));
1260 exit;
1261 END;
1262 END LOOP;
1263 OKC_API.END_ACTIVITY(g_msg_count, g_msg_data);
1264
1265 IF (l_debug = 'Y') THEN
1266 OKC_DEBUG.log('4000: Exiting task_task_escalation2...', 2);
1267 OKC_DEBUG.Reset_Indentation;
1268 END IF;
1269 FND_FILE.PUT_LINE( FND_FILE.LOG,'Success Count:'||l_success_count);
1270 FND_FILE.PUT_LINE( FND_FILE.LOG,'Failure Count:'||l_failure_count);
1271
1272 EXCEPTION
1273 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1274 IF (l_debug = 'Y') THEN
1275 OKC_DEBUG.log(' 4000:Exception Error in task_escalation2...', 2);
1276 OKC_DEBUG.Reset_Indentation;
1277 END IF;
1278 FND_FILE.PUT_LINE( FND_FILE.LOG,'Task:'||l_task_name);
1279 retcode := 2;
1280 errbuf := substr(sqlerrm,1,250);
1281 g_return_status := OKC_API.HANDLE_EXCEPTIONS
1282 (l_api_name,
1283 G_PKG_NAME,
1284 'OKC_API.G_RET_STS_ERROR',
1285 g_msg_count,
1286 g_msg_data,
1287 G_LEVEL);
1288
1289 get_fnd_msg_stack(' Exception Error in task_escaltion2 is '||g_msg_data);
1290
1291 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1292 IF (l_debug = 'Y') THEN
1293 OKC_DEBUG.log(' 4000:Unexcepted Error in task_escalation2...', 2);
1294 OKC_DEBUG.Reset_Indentation;
1295 END IF;
1296 FND_FILE.PUT_LINE( FND_FILE.LOG,'Task:'||l_task_name);
1297
1298 retcode := 2;
1299 errbuf := substr(sqlerrm,1,250);
1300 g_return_status := OKC_API.HANDLE_EXCEPTIONS
1301 (l_api_name,
1302 G_PKG_NAME,
1303 'OKC_API.G_RET_STS_UNEXP_ERROR',
1304 g_msg_count,
1305 g_msg_data,
1306 G_LEVEL);
1307
1308 get_fnd_msg_stack(' Unexpected Error in task_escalation2 is '||g_msg_data);
1309
1310 WHEN OTHERS THEN
1311 IF (l_debug = 'Y') THEN
1312 OKC_DEBUG.log(' 4000:Other Exception Error in task_escalation2...', 2);
1313 OKC_DEBUG.Reset_Indentation;
1314 END IF;
1315 FND_FILE.PUT_LINE( FND_FILE.LOG,'Task:'||l_task_name);
1316
1317 retcode := 2;
1318 errbuf := substr(sqlerrm,1,250);
1319 g_return_status := OKC_API.HANDLE_EXCEPTIONS
1320 (l_api_name,
1321 G_PKG_NAME,
1322 'OTHERS',
1323 g_msg_count,
1324 g_msg_data,
1325 G_LEVEL);
1326
1327 get_fnd_msg_stack(' Other Exception Error in task_escalation2 is '||g_msg_data);
1328
1329 END task_escalation2;
1330
1331 ----------------------------------------------------------------------------------------------------
1332 -- Start of comments
1333 -- Procedure Name : okc_pdate_reach_pvt
1334 -- Description : This Procedure triggers the action assembler when the current
1335 -- date equals planned end date reached in the tasks table
1336 -- Version : 1.0
1337 -- End of comments
1338 ----------------------------------------------------------------------------------------------------
1339
1340 PROCEDURE okc_pdate_reach_pvt(errbuf OUT NOCOPY VARCHAR2,
1341 retcode OUT NOCOPY VARCHAR2,
1342 p_api_version IN NUMBER,
1343 p_init_msg_list IN VARCHAR2) IS
1344
1345 CURSOR planned_date_cur IS
1346 SELECT planned_end_date, source_object_id
1347 from jtf_tasks_b
1348 where source_object_type_code = 'OKC_RESTIME';
1349
1350 l_return_status VARCHAR2(3);
1351 l_planned_date jtf_tasks_b.planned_end_date%TYPE;
1352 l_source_object_id jtf_tasks_b.source_object_id%TYPE;
1353 l_api_name CONSTANT VARCHAR2(30) := 'okc_pdate_reach_pvt';
1354
1355 BEGIN
1356
1357 IF (l_debug = 'Y') THEN
1358 OKC_DEBUG.set_indentation(l_api_name);
1359 OKC_DEBUG.log('5010: Entered okc_pdate_reach_pvt', 2);
1360 END IF;
1361
1362 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1363 G_PKG_NAME,
1364 p_init_msg_list,
1365 g_api_version,
1366 p_api_version,
1367 G_LEVEL,
1368 g_return_status);
1369 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1370 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1371 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1372 RAISE OKC_API.G_EXCEPTION_ERROR;
1373 END IF;
1374
1375 --Initialize the return code
1376 retcode := 0;
1377
1378 --Check if the cursor is already open
1379 IF planned_date_cur%ISOPEN THEN
1380 CLOSE planned_date_cur;
1381 END IF;
1382
1383 FOR planned_date_rec in planned_date_cur LOOP
1384
1385 IF (l_debug = 'Y') THEN
1386 OKC_DEBUG.log('5020: In planned_date_rec LOOP .... ');
1387 END IF;
1388
1389 l_planned_date := planned_date_rec.planned_end_date;
1390 l_source_object_id := planned_date_rec.source_object_id;
1391
1392 IF (l_debug = 'Y') THEN
1393 OKC_DEBUG.log('5030: l_planned_date is '||l_planned_date );
1394 OKC_DEBUG.log('5040: l_source_object_id is '||l_source_object_id );
1395 END IF;
1396
1397 --If current date equals planned date then call the action assembler
1398 IF trunc(sysdate) = trunc(l_planned_date) THEN
1399
1400 IF (l_debug = 'Y') THEN
1401 OKC_DEBUG.log('5050: Before Calling the action assembler .... ');
1402 END IF;
1403
1404 OKC_SCHR_PD_ASMBLR_PVT.acn_assemble(
1405 p_api_version => g_api_version,
1406 p_init_msg_list => p_init_msg_list,
1407 x_return_status => g_return_status,
1408 x_msg_count => g_msg_count,
1409 x_msg_data => g_msg_data,
1410 p_rtv_id => l_source_object_id,
1411 p_planned_date => l_planned_date);
1412
1413 IF (l_debug = 'Y') THEN
1414 OKC_DEBUG.log('5060: After Calling the action assembler return_status is '||g_return_status);
1415 END IF;
1416
1417 IF (g_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1418 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1419 ELSIF (g_return_status = OKC_API.G_RET_STS_ERROR) THEN
1420 raise OKC_API.G_EXCEPTION_ERROR;
1421 ELSIF (g_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1422 commit;
1423 END IF;
1424 END IF;
1425 END LOOP;
1426
1427 IF (l_debug = 'Y') THEN
1428 OKC_DEBUG.log('6000: Exiting okc_pdate_reach_pvt...', 2);
1429 OKC_DEBUG.Reset_Indentation;
1430 END IF;
1431
1432 EXCEPTION
1433 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1434 IF (l_debug = 'Y') THEN
1435 OKC_DEBUG.log(' 6000:Exception Error in okc_pdate_reach_pvt...', 2);
1436 OKC_DEBUG.Reset_Indentation;
1437 END IF;
1438
1439 retcode := 2;
1440 errbuf := substr(sqlerrm,1,250);
1441 g_return_status := OKC_API.HANDLE_EXCEPTIONS
1442 (l_api_name,
1443 G_PKG_NAME,
1444 'OKC_API.G_RET_STS_ERROR',
1445 g_msg_count,
1446 g_msg_data,
1447 G_LEVEL);
1448
1449 get_fnd_msg_stack(' Exception Error in okc_pdate_reach_pvt is '||g_msg_data);
1450
1451 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1452 IF (l_debug = 'Y') THEN
1453 OKC_DEBUG.log(' 6000:Unexcepted Error in okc_pdate_reach_pvt...', 2);
1454 OKC_DEBUG.Reset_Indentation;
1455 END IF;
1456
1457 retcode := 2;
1458 errbuf := substr(sqlerrm,1,250);
1459 g_return_status := OKC_API.HANDLE_EXCEPTIONS
1460 (l_api_name,
1461 G_PKG_NAME,
1462 'OKC_API.G_RET_STS_UNEXP_ERROR',
1463 g_msg_count,
1464 g_msg_data,
1465 G_LEVEL);
1466
1467 get_fnd_msg_stack(' Unexpected Error in okc_pdate_reach_pvt is '||g_msg_data);
1468
1469 WHEN OTHERS THEN
1470 IF (l_debug = 'Y') THEN
1471 OKC_DEBUG.log(' 6000:Other Exception Error in okc_pdate_reach_pvt...', 2);
1472 OKC_DEBUG.Reset_Indentation;
1473 END IF;
1474
1475 retcode := 2;
1476 errbuf := substr(sqlerrm,1,250);
1477 g_return_status := OKC_API.HANDLE_EXCEPTIONS
1478 (l_api_name,
1479 G_PKG_NAME,
1480 'OTHERS',
1481 g_msg_count,
1482 g_msg_data,
1483 G_LEVEL);
1484
1485 get_fnd_msg_stack(' Other Exception Error in okc_pdate_reach_pvt is '||g_msg_data);
1486
1487 END okc_pdate_reach_pvt;
1488 END OKC_TASK_ALERT_ESCL_PVT;