[Home] [Help]
PACKAGE BODY: APPS.JTF_EC_WORKFLOW_PKG
Source
1 PACKAGE BODY JTF_EC_WORKFLOW_PKG as
2 /* $Header: jtfecwfb.pls 120.6.12020000.2 2012/07/25 14:54:54 aditysin ship $ */
3
4 FUNCTION get_resource_name(
5 p_resource_type IN VARCHAR2,
6 p_resource_id IN NUMBER
7 ) RETURN VARCHAR2 is
8 TYPE cur_typ IS REF CURSOR;
9 c cur_typ;
10 l_api_name VARCHAR2(20) := 'Get_Resource_Name';
11 l_sql_statement VARCHAR2(500) := NULL;
12 l_resource_name jtf_tasks_b.source_object_name%TYPE := NULL;
13 l_where_clause jtf_objects_b.where_clause%TYPE := NULL;
14
15 -------------------------------------------------------------------------
16 -- Create a SQL statement for getting the resource name
17 -------------------------------------------------------------------------
18
19 cursor c_get_res_name is
20 SELECT where_clause,
21 'SELECT '||select_name||' FROM '||from_table||' WHERE '||select_id|| ' = :RES'
22 FROM jtf_objects_vl
23 WHERE object_code = p_resource_type;
24
25 BEGIN
26
27 open c_get_res_name;
28 fetch c_get_res_name into l_where_clause, l_sql_statement;
29 close c_get_res_name;
30
31 if l_sql_statement is not NULL then
32
33 l_sql_statement := l_sql_statement; -- || to_char(p_resource_id);
34
35 if l_where_clause is not NULL then
36 l_sql_statement := l_sql_statement||' AND '||l_where_clause;
37 end if;
38
39 OPEN c FOR l_sql_statement USING p_resource_id;
40 FETCH c INTO l_resource_name;
41 CLOSE c;
42
43 -- EXECUTE IMMEDIATE l_sql_statement INTO l_resource_name;
44 RETURN l_resource_name;
45 else
46
47 RETURN Null;
48
49 end if;
50
51 EXCEPTION
52
53 WHEN OTHERS THEN
54 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
55 then FND_MSG_PUB.Add_Exc_Msg
56 (G_PKG_NAME,
57 l_api_name
58 );
59 end if;
60 RETURN Null;
61
62 END get_resource_name;
63
64
65 -------------------------------------------------------------------------
66 -- Include a role in the notification list
67 -------------------------------------------------------------------------
68
69 PROCEDURE include_role(
70 p_role_name IN VARCHAR2,
71 x_return_status OUT NOCOPY VARCHAR2) is
72
73 l_api_name VARCHAR2(30) := 'Include_Role';
74 i BINARY_INTEGER := jtf_ec_workflow_pkg.NotifList.COUNT;
75
76 BEGIN
77
78 -- doesn't perform a role validation
79
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81
82 jtf_ec_workflow_pkg.NotifList(i+1).name := p_role_name;
83
84 EXCEPTION
85 WHEN FND_API.G_EXC_ERROR THEN
86 x_return_status := FND_API.G_Ret_Sts_Error;
87 WHEN OTHERS THEN
88
89 x_return_status := fnd_api.g_ret_sts_unexp_error;
90 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
91 then FND_MSG_PUB.Add_Exc_Msg
92 (G_PKG_NAME,
93 l_api_name
94 );
95 end if;
96
97 END include_role;
98
99 -------------------------------------------------------------------------
100 -- Get the name of the user who created the escalation document
101 -------------------------------------------------------------------------
102 PROCEDURE get_user_name(p_user_id IN NUMBER,
103 x_user_name OUT NOCOPY VARCHAR2,
104 x_return_status OUT NOCOPY VARCHAR2) is
105
106 l_api_name VARCHAR2(30) := 'Get_User_Name';
107
108 cursor c_user_name(p_user_id IN NUMBER) is
109 SELECT per.full_name name
110 FROM per_people_f per,
111 fnd_user f
112 WHERE f.employee_id = per.person_id
113 AND f.user_id = p_user_id
114 union
115 SELECT user_name name
116 FROM fnd_user
117 WHERE employee_id is null
118 AND user_id = p_user_id;
119
120 BEGIN
121
122 x_return_status := FND_API.G_RET_STS_SUCCESS;
123
124 open c_user_name(p_user_id);
125 fetch c_user_name into x_user_name;
126 if c_user_name%NOTFOUND then
127 x_user_name := NULL;
128 end if;
129 close c_user_name;
130
131 EXCEPTION
132 WHEN FND_API.G_EXC_ERROR THEN
133 x_return_status := FND_API.G_Ret_Sts_Error;
134 WHEN OTHERS THEN
135
136 x_return_status := fnd_api.g_ret_sts_unexp_error;
137 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
138 then FND_MSG_PUB.Add_Exc_Msg
139 (G_PKG_NAME,
140 l_api_name
141 );
142 end if;
143
144 END get_user_name;
145
146 -------------------------------------------------------------------------
147 -- Get the owner name for the escalated referenced
148 -- documents
149 -------------------------------------------------------------------------
150
151 PROCEDURE get_doc_owner_name(p_doc_type IN VARCHAR2,
152 p_doc_number IN VARCHAR2,
153 x_resource_id OUT NOCOPY NUMBER,
154 x_person_name OUT NOCOPY VARCHAR2,
155 x_return_status OUT NOCOPY VARCHAR2) is
156
157 TYPE cur_typ IS REF CURSOR;
158 c cur_typ;
159
160 l_api_name VARCHAR2(30) := 'Get_Doc_Owner_Name';
161
162 l_owner_name per_all_people_f.full_name%TYPE := NULL;
163 l_owner_id NUMBER := NULL;
164 l_resource_type jtf_objects_vl.object_code%TYPE := NULL;
165 l_resource_type_dev jtf_objects_vl.object_code%TYPE := NULL;
166 l_phase_owner_name VARCHAR2(240);
167 l_dev_owner_name VARCHAR2(240);
168 l_phase_owner_id NUMBER;
169 l_dev_owner_id NUMBER;
170 l_role_name wf_users.name%TYPE := NULL;
171 l_return_status varchar2(2) := 'x';
172 l_new_line varchar2(4) := '
173 ';
174
175 -------------------------------------------------------------------------
176 -- Get the owner name/id for the referenced TASKs
177 -------------------------------------------------------------------------
178
179 cursor c_get_task_owner_id is
180 SELECT owner_id,
181 owner_type_code
182 FROM jtf_tasks_vl
183 WHERE task_number = p_doc_number;
184
185
186 -------------------------------------------------------------------------
187 -- Get the owner name/id for the referenced SRs
188 -------------------------------------------------------------------------
189
190 l_sr_sql_statement VARCHAR2(200) := 'SELECT incident_owner_id, resource_type FROM cs_incidents_all_vl WHERE incident_number = :p_doc_number';
191
192 /* cursor c_get_sr_owner_id is
193 SELECT incident_owner_id,
194 resource_type
195 FROM cs_incidents_all_vl
196 WHERE incident_number = p_doc_number; */
197
198 -------------------------------------------------------------------------
199 -- Get the owner name/id for the referenced DFs and ENHs
200 -------------------------------------------------------------------------
201
202 /* -- that code will be used when DF is ready for it */
203
204 l_df_sql_statement VARCHAR2(200) := 'SELECT phase_owner_id, phase_owner_resource_type FROM css_def_defects_b WHERE defect_number = :p_doc_number';
205
206 /* l_df_sql_statement VARCHAR2(200) := 'SELECT phase_owner_id, dev_owner_id FROM css_def_defects_all WHERE defect_number = :p_doc_number'; */
207
208
209 /* cursor c_get_df_owner_name is
210 SELECT phase_owner_id,
211 dev_owner_id
212 FROM css_def_defects_all
213 WHERE defect_number = p_doc_number; */
214
215 BEGIN
216 x_return_status := FND_API.G_RET_STS_SUCCESS;
217
218 if p_doc_type = 'TASK' then
219
220 open c_get_task_owner_id;
221 fetch c_get_task_owner_id into l_owner_id, l_resource_type;
222 close c_get_task_owner_id;
223
224 if l_owner_id is not NULL AND l_resource_type = 'RS_EMPLOYEE' then --bug 5890282
225
226 l_owner_name := get_resource_name(l_resource_type,
227 l_owner_id);
228
229 l_role_name := jtf_rs_resource_pub.get_wf_role(l_owner_id);
230
231 if l_role_name is not NULL then
232 include_role(l_role_name,
233 l_return_status);
234 elsif l_owner_name is not NULL then
235 g_notif_not_sent := g_notif_not_sent || l_new_line || l_owner_name;
236 else
237 g_notif_not_sent := g_notif_not_sent || l_new_line ||l_resource_type||' = '|| to_char(l_owner_id);
238 end if;
239
240
241
242 else
243 l_owner_id := NULL;
244 l_owner_name := NULL;
245 end if;
246
247
248 x_person_name := l_owner_name;
249 x_resource_id := l_owner_id;
250
251 elsif p_doc_type = 'SR' then
252
253 OPEN c FOR l_sr_sql_statement USING p_doc_number;
254 FETCH c INTO l_owner_id, l_resource_type;
255 CLOSE c;
256
257 /* open c_get_sr_owner_id;
258 fetch c_get_sr_owner_id into l_owner_id, l_resource_type;
259 close c_get_sr_owner_id; */
260
261 if l_owner_id is not NULL AND l_resource_type = 'RS_EMPLOYEE' then --bug 5890282
262
263
264 l_owner_name := get_resource_name(l_resource_type,
265 l_owner_id);
266
267 l_role_name := jtf_rs_resource_pub.get_wf_role(l_owner_id);
268
269 if l_role_name is not NULL then
270 include_role(l_role_name,
271 l_return_status);
272 elsif l_owner_name is not NULL then
273 g_notif_not_sent := g_notif_not_sent || l_new_line || l_owner_name;
274 else
275 g_notif_not_sent := g_notif_not_sent || l_new_line ||l_resource_type||' = '|| to_char(l_owner_id);
276 end if;
277
278
279
280 else
281 l_owner_id := NULL;
282 l_owner_name := NULL;
283
284 end if;
285
286 x_person_name := l_owner_name;
287 x_resource_id := l_owner_id;
288
289 elsif (p_doc_type = 'DF') or (p_doc_type = 'ENH') then
290
291
292 /* -- to be used when DF is ready */
293 OPEN c FOR l_df_sql_statement USING p_doc_number;
294 FETCH c INTO l_phase_owner_id, l_resource_type;
295 CLOSE c;
296
297
298 /* OPEN c FOR l_df_sql_statement USING p_doc_number;
299 FETCH c INTO l_phase_owner_id, l_dev_owner_id;
300 CLOSE c; */
301
302
303 /* open c_get_df_owner_name;
304 fetch c_get_df_owner_name into l_phase_owner_id,
305 l_dev_owner_id;
306 close c_get_df_owner_name; */
307
308 if l_phase_owner_id is not NULL AND l_resource_type = 'RS_EMPLOYEE' then --bug 5890282
309
310 /* l_phase_owner_name := get_resource_name('RS_EMPLOYEE',
311 l_phase_owner_id); */
312 /* -- to be used when DF is ready */
313
314 l_phase_owner_name := get_resource_name(l_resource_type,
315 l_phase_owner_id);
316
317 x_person_name := l_phase_owner_name;
318 x_resource_id := l_phase_owner_id;
319
320 l_role_name := jtf_rs_resource_pub.get_wf_role(l_phase_owner_id);
321
322 if l_role_name is not NULL then
323 include_role(l_role_name,
324 l_return_status);
325
326
327 elsif l_phase_owner_name is not NULL then
328 g_notif_not_sent := g_notif_not_sent || l_new_line || l_phase_owner_name;
329 else
330 --Bug2415943 g_notif_not_sent := g_notif_not_sent || l_new_line || 'RS_EMPLOYEE = ' || to_char(l_phase_owner_id);
331 g_notif_not_sent := g_notif_not_sent || l_new_line || l_resource_type || ' = ' || to_char(l_phase_owner_id);
332 end if;
333
334 else
335 x_person_name := NULL;
336 x_resource_id := NULL;
337
338 end if;
339 end if;
340
341 EXCEPTION
342 WHEN FND_API.G_EXC_ERROR THEN
343 x_return_status := FND_API.G_Ret_Sts_Error;
344 WHEN OTHERS THEN
345
346 x_return_status := fnd_api.g_ret_sts_unexp_error;
347 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
348 then FND_MSG_PUB.Add_Exc_Msg
349 (G_PKG_NAME,
350 l_api_name
351 );
352 end if;
353
354 END get_doc_owner_name;
355
356
357 -------------------------------------------------------------------------
358 -- Initialize the new line attributes needed for the notification layout
359 -------------------------------------------------------------------------
360
361 PROCEDURE init_new_lines(
362 p_itemtype IN VARCHAR2,
363 p_itemkey IN VARCHAR2) is
364
365 l_new_line VARCHAR2(4):= '
366 '; -- this is a new line. Do not touch.
367 l_api_name VARCHAR2(20) := 'Init_New_Lines';
368
369 BEGIN
370
371 wf_engine.SetItemAttrText (
372 itemtype => p_itemtype ,
373 itemkey => p_itemkey,
374 aname => 'NLTO',
375 avalue => l_new_line
376 );
377 wf_engine.SetItemAttrText (
378 itemtype => p_itemtype,
379 itemkey => p_itemkey,
380 aname => 'NLTL',
381 avalue => l_new_line
382 );
383 wf_engine.SetItemAttrText (
384 itemtype => p_itemtype,
385 itemkey => p_itemkey,
386 aname => 'NLTS',
387 avalue => l_new_line
388 );
389 wf_engine.SetItemAttrText (
390 itemtype => p_itemtype,
391 itemkey => p_itemkey,
392 aname => 'NLTD',
393 avalue => l_new_line
394 );
395
396 EXCEPTION
397
398 WHEN OTHERS THEN
399 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
400 then FND_MSG_PUB.Add_Exc_Msg
401 (G_PKG_NAME,
402 l_api_name
403 );
404 end if;
405 raise;
406 END init_new_lines;
407
408 -------------------------------------------------------------------------
409 -- Hide the owner line in the notification when there are no owner changes
410 -------------------------------------------------------------------------
411
412 PROCEDURE hide_owner_line(
413 p_itemtype IN VARCHAR2,
414 p_itemkey IN VARCHAR2) is
415 l_api_name VARCHAR2(20) := 'Hide_Owner_Line';
416 BEGIN
417
418 wf_engine.SetItemAttrText (
419 itemtype => p_itemtype,
420 itemkey => p_itemkey,
421 aname => 'NLTO',
422 avalue => NULL
423 );
424 wf_engine.SetItemAttrText (
425 itemtype => p_itemtype,
426 itemkey => p_itemkey,
427 aname => 'NLHO',
428 avalue => NULL
429 );
430 wf_engine.SetItemAttrText (
431 itemtype => p_itemtype,
432 itemkey => p_itemkey,
433 aname => 'EOOV',
434 avalue => NULL
435 );
436 wf_engine.SetItemAttrText (
437 itemtype => p_itemtype,
438 itemkey => p_itemkey,
439 aname => 'OWNER_NAME_OLD',
440 avalue => NULL
441 );
442 wf_engine.SetItemAttrText (
443 itemtype => p_itemtype,
444 itemkey => p_itemkey,
445 aname => 'EONV',
446 avalue => NULL
447 );
448 wf_engine.SetItemAttrText (
449 itemtype => p_itemtype,
450 itemkey => p_itemkey,
451 aname => 'OWNER_NAME_C',
452 avalue => NULL
453 );
454 EXCEPTION
455 WHEN OTHERS THEN
456 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
457 then FND_MSG_PUB.Add_Exc_Msg
458 (G_PKG_NAME,
459 l_api_name
460 );
461 end if;
462 raise;
463 END hide_owner_line;
464
465 -------------------------------------------------------------------------
466 -- Hide the status line in the notification when there are no status changes
467 -------------------------------------------------------------------------
468
469 PROCEDURE hide_status_line(
470 p_itemtype IN VARCHAR2,
471 p_itemkey IN VARCHAR2) is
472 l_api_name VARCHAR2(20) := 'Hide_Status_Line';
473
474 BEGIN
475 wf_engine.SetItemAttrText (
476 itemtype => p_itemtype,
477 itemkey => p_itemkey,
478 aname => 'NLTS',
479 avalue => NULL
480 );
481 wf_engine.SetItemAttrText (
482 itemtype => p_itemtype,
483 itemkey => p_itemkey,
484 aname => 'NLHS',
485 avalue => NULL
486 );
487 wf_engine.SetItemAttrText (
488 itemtype => p_itemtype,
489 itemkey => p_itemkey,
490 aname => 'ESOV',
491 avalue => NULL
492 );
493 wf_engine.SetItemAttrText (
494 itemtype => p_itemtype,
495 itemkey => p_itemkey,
496 aname => 'ESC_STATUS_OLD',
497 avalue => NULL
498 );
499 wf_engine.SetItemAttrText (
500 itemtype => p_itemtype,
501 itemkey => p_itemkey,
502 aname => 'ESNV',
503 avalue => NULL
504 );
505
506 wf_engine.SetItemAttrText (
507 itemtype => p_itemtype,
508 itemkey => p_itemkey,
509 aname => 'ESC_STATUS',
510 avalue => NULL
511 );
512
513 EXCEPTION
514
515 WHEN OTHERS THEN
516 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
517 then FND_MSG_PUB.Add_Exc_Msg
518 (G_PKG_NAME,
519 l_api_name
520 );
521 end if;
522 raise;
523 End hide_status_line;
524
525 -------------------------------------------------------------------------
526 -- Hide the level line in the notification when there are no level changes
527 -------------------------------------------------------------------------
528
529 PROCEDURE hide_level_line(
530 p_itemtype IN VARCHAR2,
531 p_itemkey IN VARCHAR2) is
532 l_api_name VARCHAR2(20) := 'Hide_Level_Line';
533
534 BEGIN
535
536 wf_engine.SetItemAttrText (
537 itemtype => p_itemtype,
538 itemkey => p_itemkey,
539 aname => 'NLTL',
540 avalue => NULL
541 );
542 wf_engine.SetItemAttrText (
543 itemtype => p_itemtype,
544 itemkey => p_itemkey,
545 aname => 'NLHL',
546 avalue => NULL
547 );
548 wf_engine.SetItemAttrText (
549 itemtype => p_itemtype,
550 itemkey => p_itemkey,
551 aname => 'ELOV',
552 avalue => NULL
553 );
554 wf_engine.SetItemAttrText (
555 itemtype => p_itemtype,
556 itemkey => p_itemkey,
557 aname => 'ESC_LEVEL_OLD',
558 avalue => NULL
559 );
560 wf_engine.SetItemAttrText (
561 itemtype => p_itemtype,
562 itemkey => p_itemkey,
563 aname => 'ELNV',
564 avalue => NULL
565 );
566 wf_engine.SetItemAttrText (
567 itemtype => p_itemtype,
568 itemkey => p_itemkey,
569 aname => 'ESC_LEVEL',
570 avalue => NULL
571 );
572 EXCEPTION
573
574 WHEN OTHERS THEN
575 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
576 then FND_MSG_PUB.Add_Exc_Msg
577 (G_PKG_NAME,
578 l_api_name
579 );
580 end if;
581 raise;
582 END hide_level_line;
583
584 -------------------------------------------------------------------------
585 -- Hide the target date line in the notification when there are no changes
586 -------------------------------------------------------------------------
587
588 PROCEDURE hide_target_line(
589 p_itemtype IN VARCHAR2,
590 p_itemkey IN VARCHAR2) is
591 l_api_name VARCHAR2(20) := 'Hide_Target_Line';
592
593 BEGIN
594
595 wf_engine.SetItemAttrText (
596 itemtype => p_itemtype,
597 itemkey => p_itemkey,
598 aname => 'NLTD',
599 avalue => NULL
600 );
601 wf_engine.SetItemAttrText (
602 itemtype => p_itemtype,
603 itemkey => p_itemkey,
604 aname => 'NLHD',
605 avalue => NULL
606 );
607 wf_engine.SetItemAttrText (
608 itemtype => p_itemtype,
609 itemkey => p_itemkey,
610 aname => 'ETDOV',
611 avalue => NULL
612 );
613 wf_engine.SetItemAttrDate (
614 itemtype => p_itemtype,
615 itemkey => p_itemkey,
616 aname => 'TARGET_DATE_OLD',
617 avalue => NULL
618 );
619 wf_engine.SetItemAttrText (
620 itemtype => p_itemtype,
621 itemkey => p_itemkey,
622 aname => 'ETDNV',
623 avalue => NULL
624 );
625 wf_engine.SetItemAttrText (
626 itemtype => p_itemtype,
627 itemkey => p_itemkey,
628 aname => 'TARGET_DATE',
629 avalue => NULL
630 );
631
632 EXCEPTION
633
634 WHEN OTHERS THEN
635 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
636 then FND_MSG_PUB.Add_Exc_Msg
637 (G_PKG_NAME,
638 l_api_name
639 );
640 end if;
641 raise;
642 END hide_target_line;
643
644
645 -------------------------------------------------------------------------
646 -- Get the details (object_type,number) for the escalated referenced
647 -- documents
648 -------------------------------------------------------------------------
649
650 PROCEDURE get_doc_details(
651 p_task_id IN VARCHAR2,
652 x_doc_type OUT NOCOPY VARCHAR2,
653 x_doc_number OUT NOCOPY VARCHAR2,
654 x_doc_owner_name OUT NOCOPY VARCHAR2,
655 x_doc_details_t OUT NOCOPY VARCHAR2,
656 x_doc_details_h OUT NOCOPY VARCHAR2,
657 x_return_status OUT NOCOPY VARCHAR2) is
658
659 l_api_name VARCHAR2(30) := 'Get_Doc_Details';
660
661 l_select_statement varchar2(500) := NULL;
662 l_object_number jtf_task_references_vl.object_name%TYPE;
663 l_object_desc varchar2(1000);
664 l_doc_details_t varchar2(4000);
665 l_doc_details_h varchar2(4000);
666 l_resource_id NUMBER :=NULL;
667 l_person_name per_all_people_f.full_name%TYPE;
668 l_temp_status VARCHAR2(2);
669
670 cursor get_doc_details is
671 SELECT jo.name object_type,
672 jo.object_code,
673 tr.object_name object_number
674 FROM jtf_objects_vl jo,
675 jtf_task_references_vl tr
676 WHERE tr.task_id = p_task_id
677 AND tr.object_type_code = jo.object_code
678 AND tr.reference_code = 'ESC';
679
680 BEGIN
681 x_return_status := FND_API.G_RET_STS_SUCCESS;
682
683 for details_rec in get_doc_details loop
684 l_object_number := details_rec.object_number;
685
686 get_doc_owner_name(p_doc_type => details_rec.object_code,
687 p_doc_number => details_rec.object_number,
688 x_resource_id => l_resource_id,
689 x_person_name => l_person_name,
690 x_return_status => l_temp_status);
691
692 if fnd_msg_pub.check_msg_level( fnd_msg_pub.g_msg_lvl_debug_medium )
693 AND l_temp_status <> FND_API.G_RET_STS_SUCCESS
694 then
695 fnd_message.set_name ('JTF', 'JTF_EC_DET_ERROR');
696 fnd_msg_pub.Add;
697 end if;
698
699 x_doc_type := details_rec.object_type;
700 x_doc_number := details_rec.object_number;
701 x_doc_owner_name := l_person_name;
702
703 l_doc_details_t := l_doc_details_t||'
704 '||rpad(details_rec.object_type, 31)||rpad(l_object_number,15)||l_person_name;
705
706 l_doc_details_h := l_doc_details_h||'<BR>'||rpad(details_rec.object_type, 35,'.')||rpad(l_object_number,20,'.')||l_person_name;
707
708 end loop;
709
710 x_doc_details_t := l_doc_details_t;
711 x_doc_details_h := l_doc_details_h;
712
713 EXCEPTION
714
715 WHEN OTHERS THEN
716
717 x_return_status := fnd_api.g_ret_sts_unexp_error;
718
719 if FND_MSG_PUB.Check_Msg_Level
720 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
721 then
722 FND_MSG_PUB.Add_Exc_Msg
723 (G_PKG_NAME,
724 l_api_name
725 );
726 end if;
727
728 END get_doc_details;
729
730
731 ---------------------------------------------------------------------------
732 -- Create a list (PL/SQL table) with all of the people who will be notified
733 ---------------------------------------------------------------------------
734
735 PROCEDURE SetNotifList(p_task_id IN NUMBER,
736 p_owner_id IN NUMBER,
737 p_old_owner_id IN NUMBER := FND_API.G_MISS_NUM,
738 p_owner_type_code IN VARCHAR2,
739 p_old_owner_type_code IN VARCHAR2,
740 x_row_number OUT NOCOPY NUMBER,
741 x_return_status OUT NOCOPY VARCHAR2) Is
742
743 l_api_name VARCHAR2(30) := 'SetNotifList';
744 l_name per_all_people_f.full_name%TYPE := NULL; --Bug 2700953
745 l_role_name wf_users.name%TYPE := NULL;
746 l_display_name wf_users.display_name%TYPE := NULL;
747 l_manager_id per_all_people_f.person_id%TYPE := NULL;
748 l_temp_status VARCHAR2(2);
749 l_new_line VARCHAR2(4) := '
750 ';
751
752 -------------------------------------------------------------------------
753 -- All employee contacts that should be notified
754 -------------------------------------------------------------------------
755 Cursor c_emp_contacts Is
756 SELECT contact_id
757 FROM jtf_task_contacts
758 WHERE task_id = p_task_id
759 AND contact_type_code = 'EMP'
760 AND nvl(escalation_notify_flag, 'N') = 'Y';
761
762 -------------------------------------------------------------------------
763 -- Employee contact name
764 -------------------------------------------------------------------------
765 cursor get_employee_name(p_emp_id NUMBER) is
766 SELECT full_name
767 FROM per_all_people_F
768 WHERE person_id = p_emp_id;
769 --FROM per_employees_current_x
770 --WHERE employee_id = p_emp_id;
771
772 -------------------------------------------------------------------------
773 -- all external contacts that should be notified
774 -------------------------------------------------------------------------
775
776 cursor c_ext_contact_details is
777 SELECT ct.contact_id,
778 p.subject_party_name contact_name
779 FROM jtf_task_contacts ct,
780 jtf_party_all_contacts_v p
781 WHERE ct.task_id = p_task_id
782 AND NVL(ct.escalation_notify_flag, 'N') = 'Y'
783 AND ct.contact_type_code ='CUST'
784 AND ct.contact_id IN (p.subject_party_id, p.party_id);
785
786 -------------------------------------------------------------------------
787 -- Owner's Manager Person ID
788 -------------------------------------------------------------------------
789
790 cursor c_owner_manager(p_resource_id in NUMBER) is
791 SELECT manager_person_id
792 FROM jtf_rs_emp_dtls_vl
793 WHERE resource_id = p_resource_id;
794
795 BEGIN
796
797 x_return_status := FND_API.G_RET_STS_SUCCESS;
798
799 -------------------------------------------------------------------------
800 -- Include the Esc Owner in the Notification List
801 -------------------------------------------------------------------------
802
803 --Bug 2415943 Include the Esc Owner only if owner_type_code is RS_EMPLOYEE Start
804 if (p_owner_type_code = 'RS_EMPLOYEE') then
805
806 l_role_name := jtf_rs_resource_pub.get_wf_role(p_owner_id);
807
808
809 if l_role_name is not NULL then
810
811 include_role(l_role_name,
812 l_temp_status);
813 else
814
815 --Bug 2415943 l_name := get_resource_name('RS_EMPLOYEE', p_owner_id);
816
817 l_name := get_resource_name(p_owner_type_code, p_owner_id);
818
819 if l_name is not NULL then
820
821 g_notif_not_sent := g_notif_not_sent || l_new_line ||l_name;
822 else
823
824 --Bug 2415943 g_notif_not_sent := g_notif_not_sent || l_new_line || 'RS_EMPLOYEE = ' || to_char(p_owner_id);
825 g_notif_not_sent := g_notif_not_sent || l_new_line || p_owner_type_code || ' = ' || to_char(p_owner_id);
826
827 end if;
828
829 end if;
830
831
832 -------------------------------------------------------------------------
833 -- Include the Owner Manager in the Notification List
834 -------------------------------------------------------------------------
835
836 -- get manager
837
838 l_name := NULL;
839
840 open c_owner_manager(p_owner_id);
841 fetch c_owner_manager into l_manager_id;
842 close c_owner_manager;
843
844 if (l_manager_id is not NULL) then
845 l_role_name := NULL;
846
847 wf_directory.GetUserName
848 ('PER',
849 l_manager_id,
850 l_role_name,
851 l_display_name);
852
853 if l_role_name is not NULL then
854 include_role(l_role_name,
855 l_temp_status);
856 else open get_employee_name(l_manager_id);
857 fetch get_employee_name into l_name;
858 close get_employee_name;
859
860 if l_name is not NULL then
861 g_notif_not_sent := g_notif_not_sent || l_new_line || l_name;
862 else
863 g_notif_not_sent := g_notif_not_sent || l_new_line || 'PERSON_ID = '|| to_char(l_manager_id);
864 end if;
865 end if;
866
867
868 end if;
869 end if;
870
871 --Bug 2415943 Include the Esc Owner only if owner_type_code is RS_EMPLOYEE End
872
873 -------------------------------------------------------------------------
874 -- Include the Old Owner in the Notification List
875 -------------------------------------------------------------------------
876
877 --Bug 2415943 Include Old Owner only if old_owner_type_code is RS_EMPLOYEE Start
878
879 if (p_old_owner_type_code = 'RS_EMPLOYEE') then
880
881 if p_old_owner_id <> FND_API.G_MISS_NUM then
882
883 l_role_name := NULL;
884 l_name := NULL;
885
886 l_role_name := jtf_rs_resource_pub.get_wf_role(p_old_owner_id);
887
888 if l_role_name is not NULL then
889
890 include_role(l_role_name,
891 l_temp_status);
892 else
893
894 --Bug 2415943 l_name := get_resource_name('RS_EMPLOYEE', p_old_owner_id);
895
896 l_name := get_resource_name(p_old_owner_type_code, p_old_owner_id);
897
898 if l_name is not NULL then
899 g_notif_not_sent := g_notif_not_sent || l_new_line ||l_name;
900 else
901
902 --Bug 2415943 g_notif_not_sent := g_notif_not_sent || l_new_line || 'RS_EMPLOYEE = ' || to_char(p_old_owner_id);
903 g_notif_not_sent := g_notif_not_sent || l_new_line || p_old_owner_type_code || ' = ' || to_char(p_old_owner_id);
904 end if;
905 end if;
906 end if;
907
908 end if;
909
910 --Bug 2415943 Include Old Owner only if old_owner_type_code is RS_EMPLOYEE End
911
912 -------------------------------------------------------------------------
913 -- Include the Employee Contacts in the Notification List
914 -------------------------------------------------------------------------
915
916 For emp_rec in c_emp_contacts Loop
917 l_role_name := NULL;
918
919 if emp_rec.contact_id is not null then
920
921 wf_directory.GetUserName
922 ('PER',
923 emp_rec.contact_id,
924 l_role_name,
925 l_display_name);
926
927 if l_role_name is not NULL then
928
929 include_role(l_role_name,
930 l_temp_status);
931
932 else open get_employee_name(emp_rec.contact_id);
933 fetch get_employee_name into l_name;
934 close get_employee_name;
935
936 if l_name is not NULL then
937 g_notif_not_sent := g_notif_not_sent || l_new_line || l_name;
938 else
939 g_notif_not_sent := g_notif_not_sent || l_new_line || 'PERSON_ID = '|| to_char(emp_rec.contact_id);
940 end if;
941 end if;
942
943 end if;
944
945 End Loop;
946
947 -------------------------------------------------------------------------
948 -- Include the External Contacts in the Notification List
949 -------------------------------------------------------------------------
950
951 For ext_rec in c_ext_contact_details Loop
952 l_role_name := NULL;
953
954 if ext_rec.contact_id is not null then
955 wf_directory.GetUserName
956 ('HZ_PARTY',
957 ext_rec.contact_id,
958 l_role_name,
959 l_display_name);
960 end if;
961
962 if l_role_name is not NULL then
963
964 include_role(l_role_name,
965 l_temp_status);
966 elsif ext_rec.contact_name is not null then
967 g_notif_not_sent := g_notif_not_sent|| l_new_line || ext_rec.contact_name;
968 elsif ext_rec.contact_id is not null then
969 g_notif_not_sent := g_notif_not_sent|| l_new_line || 'CONTACT_ID = '|| to_char(ext_rec.contact_id);
970 end if;
971
972 End Loop;
973
974
975 EXCEPTION
976
977 WHEN OTHERS THEN
978
979 x_return_status := fnd_api.g_ret_sts_unexp_error;
980
981 if FND_MSG_PUB.Check_Msg_Level
982 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
983 then
984 FND_MSG_PUB.Add_Exc_Msg
985 (G_PKG_NAME,
986 l_api_name
987 );
988 end if;
989
990 END SetNotifList;
991
992 -------------------------------------------------------------------------
993 -- Set up the necessary data and Start the WF Notification Process
994 -------------------------------------------------------------------------
995
996 PROCEDURE Start_Resc_Workflow(
997 p_api_version IN NUMBER,
998 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
999 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
1000 x_return_status OUT NOCOPY VARCHAR2,
1001 x_msg_count OUT NOCOPY NUMBER,
1002 x_msg_data OUT NOCOPY VARCHAR2,
1003 p_task_id IN NUMBER,
1004 p_doc_created IN VARCHAR2 := FND_API.G_MISS_CHAR,
1005 p_owner_changed IN VARCHAR2 := FND_API.G_MISS_CHAR,
1006 p_owner_type_changed IN VARCHAR2 := FND_API.G_MISS_CHAR,
1007 p_level_changed IN VARCHAR2 := FND_API.G_MISS_CHAR,
1008 p_status_changed IN VARCHAR2 := FND_API.G_MISS_CHAR,
1009 p_target_date_changed IN VARCHAR2 := FND_API.G_MISS_CHAR,
1010 p_old_owner_id IN NUMBER := FND_API.G_MISS_NUM,
1011 p_old_owner_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
1012 p_old_level IN VARCHAR2 := FND_API.G_MISS_CHAR,
1013 p_old_status_id IN NUMBER := FND_API.G_MISS_NUM,
1014 p_old_target_date IN DATE := FND_API.G_MISS_DATE,
1015 p_wf_process_name IN VARCHAR2 DEFAULT 'ESC_NOTIF_PROCESS',
1016 p_wf_item_type_name IN VARCHAR2 DEFAULT 'JTFEC',
1017 x_notif_not_sent OUT NOCOPY VARCHAR2,
1018 x_wf_process_id OUT NOCOPY NUMBER
1019
1020 ) IS
1021
1022 task_details_rec task_details_rec_type;
1023
1024 l_api_version CONSTANT NUMBER := 1.0;
1025 l_api_name CONSTANT VARCHAR2(30) := 'Start_Resc_Workflow';
1026 l_itemkey wf_item_activity_statuses.item_key%TYPE;
1027 l_wf_key NUMBER;
1028 l_old_owner_id jtf_tasks_vl.owner_id%TYPE := p_old_owner_id;
1029 --l_old_owner_type_code jtf_tasks_vl.owner_type_code%TYPE := p_old_owner_type_code;
1030 l_user_name per_people_f.full_name%TYPE;
1031 l_esc_status jtf_ec_statuses_vl.description%TYPE;
1032 l_esc_level fnd_lookups.meaning%TYPE;
1033 l_temp_status VARCHAR2(1);
1034 -- l_found VARCHAR2(1) := 'x';
1035 l_counter NUMBER(5);
1036 l_customer_name hz_parties.party_name%TYPE := NULL;
1037 l_event VARCHAR2(50) DEFAULT NULL;
1038 l_doc_number jtf_task_references_vl.object_name%TYPE;
1039 l_owner_name VARCHAR2(200);
1040 l_doc_type jtf_objects_vl.name%TYPE;
1041 l_doc_details_t VARCHAR2(4000);
1042 l_doc_details_h VARCHAR2(4000);
1043 l_notif_type VARCHAR2(1) := 'N';
1044 l_owner_changed VARCHAR2(1):= 'N';
1045 l_level_changed VARCHAR2(1):= 'N';
1046 l_status_changed VARCHAR2(1):= 'N';
1047 l_target_date_changed VARCHAR2(1):= 'N';
1048 l_new_line VARCHAR2(4):= '
1049 '; -- this is a new line. Do not touch.
1050 l_errname varchar2(60);
1051 l_errmsg varchar2(2000);
1052 l_errstack varchar2(4000);
1053
1054
1055
1056 -------------------------------------------------------------------------
1057 -- Generate the unique WF process itemkey
1058 -------------------------------------------------------------------------
1059 cursor c_wf_key is
1060 SELECT jtf_task_workflow_process_s.nextval
1061 FROM dual;
1062
1063 -------------------------------------------------------------------------
1064 -- Get esc document details
1065 -------------------------------------------------------------------------
1066
1067 cursor resc_task_details is
1068 SELECT t.task_name,
1069 t.task_number,
1070 t.description,
1071 t.owner_type_code owner_code,
1072 t.owner_id,
1073 t.escalation_level,
1074 t.task_status_id,
1075 t.planned_end_date target_date,
1076 t.creation_date date_opened,
1077 t.last_update_date date_changed,
1078 t.last_updated_by update_id,
1079 t.created_by create_id
1080 FROM jtf_tasks_vl t,
1081 jtf_task_types_vl tt
1082 WHERE t.task_id = p_task_id
1083 AND t.task_type_id = tt.task_type_id
1084 AND tt.task_type_id = 22;
1085
1086 -------------------------------------------------------------------------
1087 -- Get customer name
1088 -------------------------------------------------------------------------
1089 cursor c_cust_name is
1090 SELECT p.party_name customer_name
1091 FROM jtf_tasks_vl t,
1092 hz_parties p
1093 WHERE t.task_id = p_task_id
1094 AND t.customer_id = p.party_id;
1095
1096 -------------------------------------------------------------------------
1097 -- Get the status
1098 -------------------------------------------------------------------------
1099
1100 cursor c_esc_status(p_status_id in NUMBER) is
1101 SELECT name
1102 FROM jtf_ec_statuses_vl
1103 WHERE task_status_id = p_status_id;
1104
1105 -------------------------------------------------------------------------
1106 -- Get the escalation level
1107 -------------------------------------------------------------------------
1108
1109 cursor c_esc_level (p_level in VARCHAR2) is
1110 SELECT meaning
1111 FROM fnd_lookups
1112 WHERE lookup_code = p_level
1113 AND lookup_type = 'JTF_TASK_ESC_LEVEL'
1114 AND enabled_flag = 'Y'
1115 AND start_date_active < sysdate
1116 AND nvl(end_date_active, sysdate) >= sysdate;
1117
1118 -------------------------------------------------------------------------
1119 -- Get the old escalation level
1120 -------------------------------------------------------------------------
1121
1122 cursor c_esc_old_level (p_level in VARCHAR2) is
1123 SELECT meaning
1124 FROM fnd_lookups
1125 WHERE lookup_code = p_level
1126 AND lookup_type = 'JTF_TASK_ESC_LEVEL';
1127
1128 -------------------------------------------------------------------------
1129 -- Get the escalated documents
1130 -------------------------------------------------------------------------
1131
1132 cursor c_esc_documents is
1133 SELECT object_name
1134 FROM jtf_task_references_vl
1135 WHERE task_id = p_task_id
1136 AND reference_code = 'ESC';
1137
1138
1139 BEGIN
1140
1141 SAVEPOINT Start_Resc_Workflow;
1142
1143 -- Standard call to check for call compatibility.
1144
1145
1146 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1147 p_api_version,
1148 l_api_name,
1149 G_PKG_NAME)
1150 THEN
1151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1152 END IF;
1153
1154 -- Check p_init_msg_list
1155
1156
1157 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1158 FND_MSG_PUB.initialize;
1159 END IF;
1160
1161 -- Initialize API return status to success
1162
1163 x_return_status := FND_API.G_RET_STS_SUCCESS;
1164
1165
1166 -------------------------------------------------------------------------
1167 -- parameters validation
1168 -------------------------------------------------------------------------
1169
1170 -- task_id
1171
1172 open resc_task_details;
1173 fetch resc_task_details into task_details_rec;
1174
1175 if resc_task_details%NOTFOUND then
1176 close resc_task_details;
1177 fnd_message.set_name ('JTF', 'JTF_EC_TASK_NOT_FOUND');
1178 fnd_message.set_token ('TASK_ID', p_task_id);
1179 -- Add message to API message list.
1180 fnd_msg_pub.Add;
1181 raise fnd_api.g_exc_error;
1182 end if;
1183 close resc_task_details;
1184
1185 -- event validation
1186
1187 if (p_doc_created <> 'Y')
1188 AND (p_level_changed <> 'Y')
1189 AND (p_status_changed <> 'Y')
1190 AND (p_owner_changed <> 'Y')
1191 AND (p_target_date_changed <> 'Y')
1192 then
1193 fnd_message.set_name ('JTF', 'JTF_EC_INVALID_EVENT');
1194 fnd_msg_pub.add;
1195 RAISE fnd_api.g_exc_error;
1196 end if;
1197
1198 if (p_level_changed = 'Y') AND (p_old_level = FND_API.G_MISS_CHAR) then
1199 fnd_message.set_name ('JTF', 'JTF_EC_OLD_LEVEL');
1200 fnd_msg_pub.add;
1201 RAISE fnd_api.g_exc_error;
1202 end if;
1203
1204 if ((p_owner_changed = 'Y') AND (p_old_owner_id = FND_API.G_MISS_NUM)) then
1205 fnd_message.set_name ('JTF', 'JTF_EC_OLD_OWNER');
1206 fnd_msg_pub.add;
1207 RAISE fnd_api.g_exc_error;
1208 end if;
1209
1210 if (p_status_changed = 'Y') AND (p_old_status_id = FND_API.G_MISS_NUM) then
1211 fnd_message.set_name ('JTF', 'JTF_EC_OLD_STATUS');
1212 fnd_msg_pub.add;
1213 RAISE fnd_api.g_exc_error;
1214 end if;
1215
1216 if (p_target_date_changed = 'Y') AND (p_old_target_date = FND_API.G_MISS_DATE) then
1217 fnd_message.set_name ('JTF', 'JTF_EC_OLD_TARGET');
1218 fnd_msg_pub.add;
1219 RAISE fnd_api.g_exc_error;
1220 end if;
1221
1222 l_owner_changed := p_owner_changed;
1223 l_level_changed := p_level_changed;
1224 l_status_changed := p_status_changed;
1225 l_target_date_changed := p_target_date_changed;
1226
1227 -------------------------------------------------------------------------
1228 -- initialize the table with the performer details
1229 -------------------------------------------------------------------------
1230
1231 JTF_EC_WORKFLOW_PKG.NotifList.Delete; -- := JTF_EC_WORKFLOW_PKG.G_Miss_NotifList;
1232
1233 g_notif_not_sent := NULL;
1234
1235 -------------------------------------------------------------------------
1236 -- create itemkey for the WF process
1237 -------------------------------------------------------------------------
1238
1239 open c_wf_key;
1240 fetch c_wf_key into l_wf_key;
1241 close c_wf_key;
1242 l_itemkey := to_char(p_task_id) || to_char(l_wf_key);
1243 x_wf_process_id := l_itemkey;
1244
1245
1246 wf_engine.CreateProcess (
1247 itemtype => p_wf_item_type_name,
1248 itemkey => l_itemkey,
1249 process => p_wf_process_name
1250 );
1251
1252 wf_engine.SetItemUserKey (
1253 itemtype => p_wf_item_type_name,
1254 itemkey => l_itemkey,
1255 userkey => task_details_rec.task_name
1256 );
1257
1258 wf_engine.SetItemAttrText (
1259 itemtype => p_wf_item_type_name,
1260 itemkey => l_itemkey,
1261 aname => 'TASK_NUMBER',
1262 avalue => task_details_rec.task_number
1263 );
1264
1265 wf_engine.SetItemAttrText (
1266 itemtype => p_wf_item_type_name,
1267 itemkey => l_itemkey,
1268 aname => 'TASK_DESC',
1269 avalue => task_details_rec.description
1270 );
1271
1272 open c_esc_status(task_details_rec.task_status_id);
1273 fetch c_esc_status into l_esc_status;
1274 close c_esc_status;
1275
1276 wf_engine.SetItemAttrText (
1277 itemtype => p_wf_item_type_name,
1278 itemkey => l_itemkey,
1279 aname => 'ESC_STATUS',
1280 avalue => l_esc_status
1281 );
1282
1283 open c_esc_level(task_details_rec.escalation_level);
1284 fetch c_esc_level into l_esc_level;
1285 close c_esc_level;
1286
1287 wf_engine.SetItemAttrText (
1288 itemtype => p_wf_item_type_name,
1289 itemkey => l_itemkey,
1290 aname => 'ESC_LEVEL',
1291 avalue => l_esc_level
1292 );
1293
1294 open c_cust_name;
1295 fetch c_cust_name into l_customer_name;
1296 close c_cust_name;
1297
1298 wf_engine.SetItemAttrText (
1299 itemtype => p_wf_item_type_name,
1300 itemkey => l_itemkey,
1301 aname => 'CUST_NAME',
1302 avalue => l_customer_name
1303 );
1304
1305 wf_engine.SetItemAttrDate (
1306 itemtype => p_wf_item_type_name,
1307 itemkey => l_itemkey,
1308 aname => 'DATE_OPENED',
1309 avalue => task_details_rec.date_opened
1310 );
1311
1312 wf_engine.SetItemAttrDate (
1313 itemtype => p_wf_item_type_name,
1314 itemkey => l_itemkey,
1315 aname => 'DATE_CHANGED',
1316 avalue => task_details_rec.date_changed
1317 );
1318
1319 -- get the name of the user who created the document
1320
1321 get_user_name(task_details_rec.create_id,
1322 l_user_name,
1323 l_temp_status);
1324
1325 wf_engine.SetItemAttrText (
1326 itemtype => p_wf_item_type_name,
1327 itemkey => l_itemkey,
1328 aname => 'CREATE_NAME',
1329 avalue => l_user_name
1330 );
1331
1332 -- get the name of the user who made the changes
1333
1334 get_user_name(task_details_rec.update_id,
1335 l_user_name,
1336 l_temp_status);
1337
1338 wf_engine.SetItemAttrText (
1339 itemtype => p_wf_item_type_name,
1340 itemkey => l_itemkey,
1341 aname => 'CHANGE_NAME',
1342 avalue => l_user_name
1343 );
1344
1345 wf_engine.SetItemAttrDate (
1346 itemtype => p_wf_item_type_name,
1347 itemkey => l_itemkey,
1348 aname => 'TARGET_DATE',
1349 avalue => task_details_rec.target_date
1350 );
1351
1352
1353 --Bug 241593 l_owner_name := get_resource_name('RS_EMPLOYEE', task_details_rec.owner_id);
1354
1355 l_owner_name := get_resource_name(task_details_rec.owner_code, task_details_rec.owner_id);
1356
1357 if l_owner_name is NULL then
1358
1359 if fnd_msg_pub.check_msg_level( fnd_msg_pub.g_msg_lvl_debug_medium ) then
1360 fnd_message.set_name ('JTF', 'JTF_EC_ERROR');
1361 fnd_msg_pub.Add;
1362 RAISE fnd_api.g_exc_error;
1363 end if;
1364
1365 end if;
1366
1367
1368 wf_engine.SetItemAttrText (
1369 itemtype => p_wf_item_type_name,
1370 itemkey => l_itemkey,
1371 aname => 'OWNER_NAME',
1372 avalue => l_owner_name
1373 );
1374
1375 wf_engine.SetItemAttrText (
1376 itemtype => p_wf_item_type_name,
1377 itemkey => l_itemkey,
1378 aname => 'OWNER_NAME_C',
1379 avalue => l_owner_name
1380 );
1381
1382 -------------------------------------------------------------------------
1383 -- Find out whether this is a single or multiple document escalation
1384 -------------------------------------------------------------------------
1385
1386 for c_esc_doc_r in c_esc_documents LOOP
1387 if c_esc_documents%ROWCOUNT = 1 then
1388 l_notif_type := 'S';
1389 end if;
1390 if c_esc_documents%ROWCOUNT = 2 then
1391 l_notif_type := 'M';
1392 exit;
1393 end if;
1394 end LOOP;
1395
1396 l_owner_name := NULL;
1397
1398 get_doc_details(p_task_id => p_task_id,
1399 x_doc_type => l_doc_type,
1400 x_doc_number => l_doc_number,
1401 x_doc_owner_name => l_owner_name,
1402 x_doc_details_t => l_doc_details_t,
1403 x_doc_details_h => l_doc_details_h,
1404 x_return_status => l_temp_status);
1405
1406 if (l_temp_status <> FND_API.G_RET_STS_SUCCESS) then
1407
1408 if fnd_msg_pub.check_msg_level( fnd_msg_pub.g_msg_lvl_debug_medium ) then
1409 fnd_message.set_name ('JTF', 'JTF_EC_ERROR');
1410 fnd_msg_pub.Add;
1411 RAISE fnd_api.g_exc_error;
1412 end if;
1413
1414 end if;
1415
1416
1417
1418 if (p_doc_created = 'Y') then -- new esc document is created
1419
1420 -- do not process changes if there are some requests
1421 l_owner_changed := 'N';
1422 l_level_changed := 'N';
1423 l_status_changed := 'N';
1424 l_target_date_changed := 'N';
1425 l_old_owner_id := FND_API.G_MISS_NUM;
1426
1427 if (l_notif_type = 'S') then
1428 l_event := 'ESC_DOC_CREATED_S';
1429 else -- multi document + no documents
1430 l_event := 'ESC_DOC_CREATED_M';
1431 end if;
1432
1433 else -- attribute(s) (status, level, owner, target date) has/have changed
1434
1435 if (l_notif_type = 'S') then -- single document
1436 l_event := 'ESC_DOC_CHANGED_S';
1437 else -- multi document + no documents
1438 l_event := 'ESC_DOC_CHANGED_M';
1439 end if;
1440
1441 end if;
1442
1443 wf_engine.SetItemAttrText (
1444 itemtype => p_wf_item_type_name,
1445 itemkey => l_itemkey,
1446 aname => 'EVENT',
1447 avalue => l_event
1448 );
1449
1450 -- Set the document owner attributes
1451
1452
1453 if (l_notif_type = 'S') then -- for single doc escalation
1454
1455 wf_engine.SetItemAttrText (
1456 itemtype => p_wf_item_type_name,
1457 itemkey => l_itemkey,
1458 aname => 'DOC_TYPE',
1459 avalue => l_doc_type
1460 );
1461
1462 wf_engine.SetItemAttrText (
1463 itemtype => p_wf_item_type_name,
1464 itemkey => l_itemkey,
1465 aname => 'DOC_NUMBER',
1466 avalue => l_doc_number
1467 );
1468
1469 wf_engine.SetItemAttrText (
1470 itemtype => p_wf_item_type_name,
1471 itemkey => l_itemkey,
1472 aname => 'DOC_OWNER',
1473 avalue => l_owner_name
1474 );
1475 else -- for multi doc escalation
1476 wf_engine.SetItemAttrText (
1477 itemtype => p_wf_item_type_name,
1478 itemkey => l_itemkey,
1479 aname => 'DOC_DETAILS_T',
1480 avalue => l_doc_details_t
1481 );
1482 wf_engine.SetItemAttrText (
1483 itemtype => p_wf_item_type_name,
1484 itemkey => l_itemkey,
1485 aname => 'DOC_DETAILS_H',
1486 avalue => l_doc_details_h
1487 );
1488 end if;
1489
1490 -------------------------------------------------------------------------
1491 -- Initialize the new line attributes needed for the notification layout
1492 -------------------------------------------------------------------------
1493
1494 init_new_lines(p_wf_item_type_name,l_itemkey);
1495
1496 -------------------------------------------------------------------------
1497 -- The Owner is Changed Event
1498 -------------------------------------------------------------------------
1499
1500 if p_owner_changed = 'Y' then
1501
1502 -- get previous owner details
1503
1504 l_owner_name := NULL;
1505
1506 --Bug 2415943 l_owner_name := get_resource_name('RS_EMPLOYEE', p_old_owner_id);
1507
1508 l_owner_name := get_resource_name(p_old_owner_type_code, p_old_owner_id);
1509
1510
1511 wf_engine.SetItemAttrText (
1512 itemtype => p_wf_item_type_name,
1513 itemkey => l_itemkey,
1514 aname => 'OWNER_NAME_OLD',
1515 avalue => l_owner_name
1516 );
1517 wf_engine.SetItemAttrText (
1518 itemtype => p_wf_item_type_name,
1519 itemkey => l_itemkey,
1520 aname => 'NLTO',
1521 avalue => l_new_line
1522 );
1523
1524
1525 elsif p_doc_created <> 'Y' then -- set the owner line of WF notification to NULL
1526
1527 l_old_owner_id := FND_API.G_MISS_NUM;
1528 hide_owner_line(p_wf_item_type_name,l_itemkey);
1529
1530 end if;
1531
1532 -------------------------------------------------------------------------
1533 -- Status is Changed Event
1534 -------------------------------------------------------------------------
1535
1536 if p_status_changed = 'Y' then
1537
1538 -- get previous status
1539
1540 open c_esc_status(p_old_status_id);
1541 fetch c_esc_status into l_esc_status;
1542
1543 if c_esc_status%NOTFOUND then
1544 close c_esc_status;
1545 if fnd_msg_pub.check_msg_level( fnd_msg_pub.g_msg_lvl_debug_medium ) then
1546 fnd_message.set_name ('JTF', 'JTF_EC_ERROR');
1547 fnd_msg_pub.Add;
1548 RAISE fnd_api.g_exc_error;
1549 end if;
1550 end if;
1551
1552 close c_esc_status;
1553
1554 wf_engine.SetItemAttrText (
1555 itemtype => p_wf_item_type_name,
1556 itemkey => l_itemkey,
1557 aname => 'ESC_STATUS_OLD',
1558 avalue => l_esc_status
1559 );
1560 wf_engine.SetItemAttrText (
1561 itemtype => p_wf_item_type_name,
1562 itemkey => l_itemkey,
1563 aname => 'NLTS',
1564 avalue => l_new_line
1565 );
1566 elsif p_doc_created <> 'Y' then -- set the staus line of WF notification to NULL
1567
1568 hide_status_line(p_wf_item_type_name,l_itemkey);
1569
1570 end if;
1571 -------------------------------------------------------------------------
1572 -- Level is Changed Event
1573 -------------------------------------------------------------------------
1574
1575 if p_level_changed = 'Y' then
1576
1577 -- get the previous level
1578 /*
1579 open c_esc_level(p_old_level);
1580 fetch c_esc_level into l_esc_level;
1581 if c_esc_level%NOTFOUND then
1582 close c_esc_level;
1583 if fnd_msg_pub.check_msg_level( fnd_msg_pub.g_msg_lvl_debug_medium ) then
1584 fnd_message.set_name ('JTF', 'JTF_EC_ERROR');
1585 fnd_msg_pub.Add;
1586 RAISE fnd_api.g_exc_error;
1587 end if;
1588 end if;
1589 close c_esc_level;
1590 */
1591 open c_esc_old_level(p_old_level);
1592 fetch c_esc_old_level into l_esc_level;
1593 if c_esc_old_level%NOTFOUND then
1594 close c_esc_old_level;
1595 if fnd_msg_pub.check_msg_level( fnd_msg_pub.g_msg_lvl_debug_medium ) then
1596 fnd_message.set_name ('JTF', 'JTF_EC_ERROR');
1597 fnd_msg_pub.Add;
1598 RAISE fnd_api.g_exc_error;
1599 end if;
1600 end if;
1601 close c_esc_old_level;
1602
1603 wf_engine.SetItemAttrText (
1604 itemtype => p_wf_item_type_name,
1605 itemkey => l_itemkey,
1606 aname => 'ESC_LEVEL_OLD',
1607 avalue => l_esc_level
1608 );
1609
1610 wf_engine.SetItemAttrText (
1611 itemtype => p_wf_item_type_name,
1612 itemkey => l_itemkey,
1613 aname => 'NLTL',
1614 avalue => l_new_line
1615 );
1616
1617 elsif p_doc_created <> 'Y' then -- set the level line of WF notification to NULL
1618
1619 hide_level_line(p_wf_item_type_name,l_itemkey);
1620
1621 end if;
1622
1623 -------------------------------------------------------------------------
1624 -- Target Date is Changed Event
1625 -------------------------------------------------------------------------
1626
1627 if p_target_date_changed = 'Y' then
1628
1629 wf_engine.SetItemAttrDate (
1630 itemtype => p_wf_item_type_name,
1631 itemkey => l_itemkey,
1632 aname => 'TARGET_DATE_OLD',
1633 avalue => p_old_target_date
1634 );
1635 wf_engine.SetItemAttrText (
1636 itemtype => p_wf_item_type_name,
1637 itemkey => l_itemkey,
1638 aname => 'NLTD',
1639 avalue => l_new_line
1640 );
1641
1642 elsif p_doc_created <> 'Y' then
1643
1644 hide_target_line(p_wf_item_type_name,l_itemkey);
1645
1646 end if;
1647
1648
1649 -------------------------------------------------------------------------
1650 -- Set the Notification List
1651 -------------------------------------------------------------------------
1652
1653 SetNotifList(
1654 p_task_id => p_task_id,
1655 p_owner_id => task_details_rec.owner_id,
1656 p_old_owner_id => l_old_owner_id,
1657 p_owner_type_code => task_details_rec.owner_code,
1658 p_old_owner_type_code => p_old_owner_type_code,
1659 x_row_number => l_counter,
1660 x_return_status => l_temp_status);
1661
1662 -------------------------------------------------------------------------
1663 -- Set the process counters
1664 -------------------------------------------------------------------------
1665
1666 l_counter := jtf_ec_workflow_pkg.NotifList.COUNT;
1667
1668 wf_engine.SetItemAttrNumber(
1669 itemtype => p_wf_item_type_name,
1670 itemkey => l_itemkey,
1671 aname => 'LIST_COUNTER',
1672 avalue => 1
1673 );
1674
1675 wf_engine.SetItemAttrNumber(
1676 itemtype => p_wf_item_type_name,
1677 itemkey => l_itemkey,
1678 aname => 'PERFORMER_LIMIT',
1679 avalue => l_counter
1680 );
1681
1682 WF_ENGINE.StartProcess
1683 (
1684 itemtype => p_wf_item_type_name,
1685 itemkey => l_itemkey
1686 );
1687
1688 update jtf_tasks_b
1689 set workflow_process_id = l_itemkey
1690 where task_id = p_task_id;
1691
1692 -- Standard check of p_commit.
1693 IF FND_API.To_Boolean( p_commit ) THEN
1694 COMMIT WORK;
1695 END IF;
1696
1697 x_notif_not_sent := g_notif_not_sent;
1698
1699 fnd_msg_pub.count_and_get
1700 ( p_count => x_msg_count,
1701 p_data => x_msg_data
1702 );
1703
1704 EXCEPTION
1705
1706 WHEN fnd_api.g_exc_error
1707 THEN
1708 ROLLBACK TO Start_Resc_Workflow;
1709 x_return_status := fnd_api.g_ret_sts_error;
1710 fnd_msg_pub.count_and_get (
1711 p_count => x_msg_count,
1712 p_data => x_msg_data
1713 );
1714
1715 WHEN fnd_api.g_exc_unexpected_error
1716 THEN
1717 ROLLBACK TO Start_Resc_Workflow;
1718
1719 -- get the WF error stack
1720 wf_core.get_error(l_errname, l_errmsg, l_errstack);
1721
1722 -- If no wf error look for a native Oracle error
1723
1724 if ((l_errname is null) and (sqlcode <> 0)) then
1725 l_errname := to_char(sqlcode);
1726 l_errmsg := sqlerrm;
1727 end if;
1728
1729 if (l_errname is not null) then
1730 fnd_message.set_name('FND', 'WF_ERROR');
1731 fnd_message.set_token('ERROR_MESSAGE', l_errmsg);
1732 fnd_message.set_token('ERROR_STACK', l_errstack);
1733 fnd_msg_pub.add;
1734 end if;
1735
1736 x_return_status := fnd_api.g_ret_sts_unexp_error;
1737 FND_MSG_PUB.Add_Exc_Msg
1738 (G_PKG_NAME,
1739 l_api_name
1740 );
1741 fnd_msg_pub.count_and_get (
1742 p_count => x_msg_count,
1743 p_data => x_msg_data
1744 );
1745
1746 WHEN OTHERS
1747 THEN
1748 ROLLBACK TO Start_Resc_Workflow;
1749 x_return_status := fnd_api.g_ret_sts_unexp_error;
1750 if FND_MSG_PUB.Check_Msg_Level
1751 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1752 then
1753 FND_MSG_PUB.Add_Exc_Msg
1754 (G_PKG_NAME,
1755 l_api_name
1756 );
1757 end if;
1758
1759 fnd_msg_pub.count_and_get (
1760 p_count => x_msg_count,
1761 p_data => x_msg_data
1762 );
1763 END Start_Resc_Workflow;
1764
1765 PROCEDURE Check_Event(
1766 itemtype IN VARCHAR2,
1767 itemkey IN VARCHAR2,
1768 actid IN NUMBER,
1769 funcmode IN VARCHAR2,
1770 resultout OUT NOCOPY VARCHAR2
1771 ) IS
1772
1773 l_resultout VARCHAR2(200);
1774
1775 BEGIN
1776 --
1777 -- RUN mode - normal process execution
1778 --
1779
1780 if (funcmode = 'RUN') then
1781 --
1782 -- Return process to run
1783 --
1784
1785 l_resultout :=
1786 wf_engine.GetItemAttrText (
1787 itemtype => itemtype,
1788 itemkey => itemkey,
1789 aname => 'EVENT'
1790 );
1791
1792 resultout := 'COMPLETE:' || l_resultout;
1793 return;
1794 end if;
1795
1796 --
1797 -- CANCEL mode - activity 'compensation'
1798 --
1799 if (funcmode = 'CANCEL') then
1800 --
1801 -- Return process to run
1802 --
1803 resultout := 'COMPLETE';
1804 return;
1805 end if;
1806
1807 --
1808 -- TIMEOUT mode
1809 --
1810 if (funcmode = 'TIMEOUT') then
1811 resultout := 'COMPLETE';
1812 return;
1813 end if;
1814 --
1815 exception
1816 when others then
1817
1818 wf_core.context (
1819 G_PKG_NAME,
1820 'Check_Event',
1821 itemtype,
1822 itemkey,
1823 to_char(actid),
1824 funcmode
1825 );
1826
1827 raise;
1828
1829 END Check_Event;
1830
1831 PROCEDURE Set_Notif_Message(
1832 itemtype IN VARCHAR2,
1833 itemkey IN VARCHAR2,
1834 actid IN NUMBER,
1835 funcmode IN VARCHAR2,
1836 resultout OUT NOCOPY VARCHAR2
1837 ) IS
1838
1839 l_event VARCHAR2(200);
1840
1841
1842 BEGIN
1843 --
1844 -- RUN mode - normal process execution
1845 --
1846 if (funcmode = 'RUN') then
1847 --
1848 -- Return process to run
1849 --
1850 l_event :=
1851 wf_engine.GetItemAttrText (
1852 itemtype => itemtype,
1853 itemkey => itemkey,
1854 aname => 'EVENT'
1855 );
1856
1857 if l_event = 'ESC_DOC_CREATED_S' then
1858 wf_engine.SetItemAttrText (
1859 itemtype => itemtype,
1860 itemkey => itemkey,
1861 aname => 'MESSAGE_NAME',
1862 avalue => 'MSG_ESC_DOC_CREATED_S'
1863 );
1864
1865 elsif l_event = 'ESC_DOC_CREATED_M' then
1866 wf_engine.SetItemAttrText (
1867 itemtype => itemtype,
1868 itemkey => itemkey,
1869 aname => 'MESSAGE_NAME',
1870 avalue => 'MSG_ESC_DOC_CREATED_M'
1871 );
1872
1873 elsif l_event = 'ESC_DOC_CHANGED_S' then
1874 wf_engine.SetItemAttrText (
1875 itemtype => itemtype,
1876 itemkey => itemkey,
1877 aname => 'MESSAGE_NAME',
1878 avalue => 'MSG_ESC_DOC_CHANGED_S'
1879 );
1880
1881 elsif l_event = 'ESC_DOC_CHANGED_M' then
1882 wf_engine.SetItemAttrText (
1883 itemtype => itemtype,
1884 itemkey => itemkey,
1885 aname => 'MESSAGE_NAME',
1886 avalue => 'MSG_ESC_DOC_CHANGED_M'
1887 );
1888 end if;
1889
1890 resultout := 'COMPLETE';
1891 return;
1892 end if;
1893
1894 --
1895 -- CANCEL mode - activity 'compensation'
1896 --
1897 if (funcmode = 'CANCEL') then
1898 --
1899 -- Return process to run
1900 --
1901 resultout := 'COMPLETE';
1902 return;
1903 end if;
1904
1905 --
1906 -- TIMEOUT mode
1907 --
1908 if (funcmode = 'TIMEOUT') then
1909 resultout := 'COMPLETE';
1910 return;
1911 end if;
1912 --
1913 exception
1914 when others then
1915
1916 wf_core.context (
1917 G_PKG_NAME,
1918 'Set_Notif_Message',
1919 itemtype,
1920 itemkey,
1921 to_char(actid),
1922 funcmode
1923 );
1924
1925 raise;
1926
1927 END Set_Notif_Message;
1928
1929
1930
1931 PROCEDURE Set_Notif_Performer(
1932 itemtype IN VARCHAR2,
1933 itemkey IN VARCHAR2,
1934 actid IN NUMBER,
1935 funcmode IN VARCHAR2,
1936 resultout OUT NOCOPY VARCHAR2
1937 ) IS
1938
1939 l_counter BINARY_INTEGER;
1940 l_role wf_roles.name%TYPE;
1941
1942 BEGIN
1943 --
1944 -- RUN mode - normal process execution
1945 --
1946 if (funcmode = 'RUN') then
1947 --
1948 -- Return process to run
1949 --
1950
1951 l_counter := wf_engine.GetItemAttrNumber(
1952 itemtype => itemtype,
1953 itemkey => itemkey,
1954 aname => 'LIST_COUNTER'
1955 );
1956
1957 l_role := jtf_ec_workflow_pkg.NotifList(l_counter).Name;
1958
1959 if l_role is not NULL then
1960
1961 wf_engine.SetItemAttrText(
1962 itemtype => itemtype,
1963 itemkey => itemkey,
1964 aname => 'MESSAGE_RECIPIENT',
1965 avalue => l_role
1966 );
1967 end if;
1968
1969 l_counter := l_counter + 1;
1970
1971 wf_engine.SetItemAttrNumber(
1972 itemtype => itemtype,
1973 itemkey => itemkey,
1974 aname => 'LIST_COUNTER',
1975 avalue => l_counter
1976 );
1977
1978 resultout := 'COMPLETE';
1979 return;
1980
1981 end if;
1982
1983 --
1984 -- CANCEL mode - activity 'compensation'
1985 --
1986 if (funcmode = 'CANCEL') then
1987 --
1988 -- Return process to run
1989 --
1990 resultout := 'COMPLETE';
1991 return;
1992 end if;
1993
1994 --
1995 -- TIMEOUT mode
1996 --
1997 if (funcmode = 'TIMEOUT') then
1998 resultout := 'COMPLETE';
1999 return;
2000 end if;
2001 --
2002 exception
2003 when others then
2004 wf_core.context (
2005 G_PKG_NAME,
2006 'Set_Notif_Performer',
2007 itemtype,
2008 itemkey,
2009 to_char(actid),
2010 funcmode
2011 );
2012
2013 raise;
2014
2015 END Set_Notif_Performer;
2016
2017 --Start of code for ER 7032664
2018
2019 PROCEDURE Raise_Esc_Create_Event(P_TASK_ID IN NUMBER)
2020 IS
2021 l_list WF_PARAMETER_LIST_T;
2022 l_key varchar2(240);
2023 l_event_name varchar2(240) := 'oracle.apps.jtf.cac.esc.createEscalation';
2024 l_task_id number := p_task_id;
2025
2026 BEGIN
2027 --Get the item key
2028 l_list := NULL;
2029 SELECT l_event_name ||'-'|| jtf_ec_wf_events_s.nextval INTO l_key FROM DUAL;
2030 wf_event.addparametertolist ('TASK_ID', to_char(l_task_id), l_list);
2031
2032 -- Raise the create task event
2033 wf_event.raise3(
2034 p_event_name => l_event_name,
2035 p_event_key => l_key,
2036 p_parameter_list => l_list,
2037 p_send_date => sysdate
2038 );
2039 l_list.DELETE;
2040
2041 COMMIT WORK;
2042 END Raise_Esc_Create_Event;
2043
2044 PROCEDURE Raise_Esc_Update_Event(
2045 task_id IN NUMBER,
2046 doc_created IN VARCHAR2,
2047 owner_changed IN VARCHAR2,
2048 owner_type_changed IN VARCHAR2,
2049 level_changed IN VARCHAR2,
2050 status_changed IN VARCHAR2,
2051 target_date_changed IN VARCHAR2,
2052 old_owner_id IN NUMBER,
2053 old_owner_type_code IN VARCHAR2,
2054 old_level IN VARCHAR2,
2055 old_status_id IN NUMBER,
2056 old_target_date IN DATE )
2057 IS
2058 l_esc_rec JTF_EC_WORKFLOW_PKG.esc_rec_type ;
2059 BEGIN
2060 l_esc_rec.task_id := task_id;
2061 l_esc_rec.doc_created := doc_created;
2062 l_esc_rec.owner_changed := owner_changed;
2063 l_esc_rec.owner_type_changed := owner_type_changed;
2064 l_esc_rec.level_changed := level_changed;
2065 l_esc_rec.status_changed := status_changed;
2066 l_esc_rec.target_date_changed := target_date_changed;
2067 l_esc_rec.old_owner_id := old_owner_id;
2068 l_esc_rec.old_owner_type_code := old_owner_type_code;
2069 l_esc_rec.old_level := old_level;
2070 l_esc_rec.old_status_id := old_status_id;
2071 l_esc_rec.old_target_date := old_target_date;
2072
2073 JTF_EC_WORKFLOW_PKG.Raise_Esc_Update_Event(p_esc_rec => l_esc_rec);
2074 END Raise_Esc_Update_Event;
2075
2076 PROCEDURE Raise_Esc_Update_Event(P_ESC_REC IN JTF_EC_WORKFLOW_PKG.esc_rec_type)
2077 IS
2078 l_list WF_PARAMETER_LIST_T;
2079 l_key varchar2(240);
2080 l_event_name varchar2(240) := 'oracle.apps.jtf.cac.esc.updateEscalation';
2081 l_esc_rec JTF_EC_WORKFLOW_PKG.esc_rec_type := p_esc_rec;
2082
2083 BEGIN
2084 --Get the item key
2085 l_list := NULL;
2086 SELECT l_event_name ||'-'|| jtf_ec_wf_events_s.nextval INTO l_key FROM DUAL;
2087
2088 wf_event.addparametertolist ('TASK_ID', to_char(l_esc_rec.task_id), l_list);
2089 wf_event.addparametertolist ('DOC_CREATED', l_esc_rec.doc_created, l_list);
2090 wf_event.addparametertolist ('OWNER_CHANGED', l_esc_rec.owner_changed, l_list);
2091 wf_event.addparametertolist ('OWNER_TYPE_CHANGED', l_esc_rec.owner_type_changed, l_list);
2092 wf_event.addparametertolist ('LEVEL_CHANGED', l_esc_rec.level_changed, l_list);
2093 wf_event.addparametertolist ('STATUS_CHANGED', l_esc_rec.status_changed, l_list);
2094 wf_event.addparametertolist ('TARGET_DATE_CHANGED', l_esc_rec.target_date_changed, l_list);
2095 wf_event.addparametertolist ('OLD_OWNER_ID', to_char(l_esc_rec.old_owner_id), l_list);
2096 wf_event.addparametertolist ('OLD_OWNER_TYPE_CODE', l_esc_rec.old_owner_type_code, l_list);
2097 wf_event.addparametertolist ('OLD_LEVEL', l_esc_rec.old_level, l_list);
2098 wf_event.addparametertolist ('OLD_STATUS_ID', to_char(l_esc_rec.old_status_id), l_list);
2099 wf_event.addparametertolist ('OLD_TARGET_DATE', to_char(l_esc_rec.old_target_date,'YYYY-MM-DD HH24:MI:SS'), l_list);
2100
2101 -- Raise the create task event
2102 wf_event.raise3( p_event_name => l_event_name,
2103 p_event_key => l_key,
2104 p_parameter_list => l_list,
2105 p_send_date => sysdate
2106 );
2107 l_list.DELETE;
2108
2109 COMMIT WORK;
2110 END Raise_Esc_Update_Event;
2111
2112
2113 FUNCTION create_esc_notif_subs(p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t)
2114 RETURN VARCHAR2 IS
2115 l_task_id jtf_tasks_b.task_id%TYPE;
2116 l_wf_process_id NUMBER;
2117 l_notif_not_sent VARCHAR2(1000);
2118 x_return_status VARCHAR2(200);
2119 x_msg_count NUMBER;
2120 x_msg_data VARCHAR2(1000);
2121
2122 BEGIN
2123 l_task_id := wf_event.getvalueforparameter('TASK_ID', p_event.parameter_list);
2124
2125 Start_Resc_Workflow(
2126 P_API_VERSION =>1.0,
2127 P_INIT_MSG_LIST =>'T',
2128 P_COMMIT =>'T',
2129 X_RETURN_STATUS =>x_return_status,
2130 X_MSG_COUNT =>x_msg_count,
2131 X_MSG_DATA =>x_msg_data,
2132 P_TASK_ID =>l_task_id,
2133 P_DOC_CREATED =>'Y',
2134 P_OWNER_CHANGED =>'N',
2135 P_OWNER_TYPE_CHANGED =>'N',
2136 P_LEVEL_CHANGED =>'N',
2137 P_STATUS_CHANGED =>'N',
2138 P_TARGET_DATE_CHANGED =>'N',
2139 P_OLD_OWNER_ID =>NULL,
2140 P_OLD_OWNER_TYPE_CODE =>NULL,
2141 P_OLD_LEVEL =>NULL,
2142 P_OLD_STATUS_ID =>NULL,
2143 P_OLD_TARGET_DATE =>NULL,
2144 x_notif_not_sent =>l_notif_not_sent,
2145 X_WF_PROCESS_ID =>l_wf_process_id);
2146
2147 IF NOT(x_return_status = fnd_api.g_ret_sts_success) THEN
2148 wf_core.CONTEXT('jtf_ec_workflow_pkg', 'create_esc_notif_subs', p_event.event_name, p_subscription_guid);
2149 wf_event.seterrorinfo(p_event, 'WARNING');
2150 RETURN 'WARNING';
2151 END IF;
2152
2153 RETURN 'SUCCESS';
2154 EXCEPTION
2155 WHEN OTHERS THEN
2156 wf_core.CONTEXT('jtf_ec_workflow_pkg', 'create_esc_notif_subs', p_event.event_name, p_subscription_guid);
2157 wf_event.seterrorinfo(p_event, 'WARNING');
2158 RETURN 'WARNING';
2159 END create_esc_notif_subs;
2160
2161
2162
2163 FUNCTION update_esc_notif_subs(p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t)
2164 RETURN VARCHAR2 IS
2165 l_wf_process_id NUMBER;
2166 l_notif_not_sent VARCHAR2(1000);
2167 x_return_status VARCHAR2(200);
2168 x_msg_count NUMBER;
2169 x_msg_data VARCHAR2(1000);
2170
2171 l_esc_rec JTF_EC_WORKFLOW_PKG.esc_rec_type;
2172 BEGIN
2173 l_esc_rec.task_id := wf_event.getvalueforparameter('TASK_ID', p_event.parameter_list);
2174 l_esc_rec.doc_created := wf_event.getvalueforparameter('DOC_CREATED', p_event.parameter_list);
2175 l_esc_rec.owner_changed := wf_event.getvalueforparameter('OWNER_CHANGED', p_event.parameter_list);
2176 l_esc_rec.owner_type_changed := wf_event.getvalueforparameter('OWNER_TYPE_CHANGED', p_event.parameter_list);
2177 l_esc_rec.level_changed := wf_event.getvalueforparameter('LEVEL_CHANGED', p_event.parameter_list);
2178 l_esc_rec.status_changed := wf_event.getvalueforparameter('STATUS_CHANGED', p_event.parameter_list);
2179 l_esc_rec.target_date_changed := wf_event.getvalueforparameter('TARGET_DATE_CHANGED', p_event.parameter_list);
2180 l_esc_rec.old_owner_id := wf_event.getvalueforparameter('OLD_OWNER_ID', p_event.parameter_list);
2181 l_esc_rec.old_owner_type_code := wf_event.getvalueforparameter('OLD_OWNER_TYPE_CODE', p_event.parameter_list);
2182 l_esc_rec.old_level := wf_event.getvalueforparameter('OLD_LEVEL', p_event.parameter_list);
2183 l_esc_rec.old_status_id := wf_event.getvalueforparameter('OLD_STATUS_ID', p_event.parameter_list);
2184 l_esc_rec.old_target_date := to_date(wf_event.getvalueforparameter('OLD_TARGET_DATE',p_event.parameter_list),'YYYY-MM-DD HH24:MI:SS');
2185
2186 Start_Resc_Workflow(
2187 P_API_VERSION =>1.0,
2188 P_INIT_MSG_LIST =>'T',
2189 P_COMMIT =>'T',
2190 X_RETURN_STATUS =>x_return_status,
2191 X_MSG_COUNT =>x_msg_count,
2192 X_MSG_DATA =>x_msg_data,
2193 P_TASK_ID =>l_esc_rec.task_id,
2194 P_DOC_CREATED =>l_esc_rec.doc_created,
2195 P_OWNER_CHANGED =>l_esc_rec.owner_changed,
2196 P_OWNER_TYPE_CHANGED =>l_esc_rec.owner_type_changed,
2197 P_LEVEL_CHANGED =>l_esc_rec.level_changed,
2198 P_STATUS_CHANGED =>l_esc_rec.status_changed,
2199 P_TARGET_DATE_CHANGED =>l_esc_rec.target_date_changed,
2200 P_OLD_OWNER_ID =>l_esc_rec.old_owner_id,
2201 P_OLD_OWNER_TYPE_CODE =>l_esc_rec.old_owner_type_code,
2202 P_OLD_LEVEL =>l_esc_rec.old_level,
2203 P_OLD_STATUS_ID =>l_esc_rec.old_status_id,
2204 P_OLD_TARGET_DATE =>l_esc_rec.old_target_date,
2205 x_notif_not_sent =>l_notif_not_sent,
2206 X_WF_PROCESS_ID =>l_wf_process_id);
2207
2208 IF NOT(x_return_status = fnd_api.g_ret_sts_success) THEN
2209 wf_core.CONTEXT('jtf_ec_workflow_pkg', 'update_esc_notif_subs', p_event.event_name, p_subscription_guid);
2210 wf_event.seterrorinfo(p_event, 'WARNING');
2211 RETURN 'WARNING';
2212 END IF;
2213
2214 RETURN 'SUCCESS';
2215 EXCEPTION
2216 WHEN OTHERS THEN
2217 wf_core.CONTEXT('jtf_ec_workflow_pkg', 'update_esc_notif_subs', p_event.event_name, p_subscription_guid);
2218 wf_event.seterrorinfo(p_event, 'WARNING');
2219 RETURN 'WARNING';
2220 END update_esc_notif_subs;
2221
2222 --End of code for ER 7032664
2223
2224 END JTF_EC_WORKFLOW_PKG;