[Home] [Help]
PACKAGE BODY: APPS.JTF_EC_UTIL
Source
1 PACKAGE BODY JTF_EC_UTIL as
2 /* $Header: jtfvecub.pls 115.8 2003/01/13 13:46:49 siyappan ship $ */
3
4 PROCEDURE Validate_Owner(
5 p_owner_id IN NUMBER,
6 p_owner_type IN VARCHAR2,
7 x_return_status OUT NOCOPY VARCHAR2) Is
8
9 l_api_name varchar2(30) := 'Validate_Owner';
10
11 --Bug 2723761
12 x CHAR;
13
14 CURSOR c_object_code
15 IS
16 SELECT 1
17 FROM jtf_objects_b
18 WHERE object_code = p_owner_type
19 AND trunc(NVL (end_date_active, SYSDATE)) >= trunc(SYSDATE)
20 AND trunc(NVL (start_date_active, SYSDATE)) <= trunc(SYSDATE)
21 AND (object_code IN
22 (SELECT object_code
23 FROM jtf_object_usages
24 WHERE object_user_code = 'RESOURCES'));
25
26 BEGIN
27
28 x_return_status := FND_API.G_RET_STS_SUCCESS;
29
30 if p_owner_id is NULL or p_owner_id = fnd_api.g_miss_num then
31 Add_Invalid_Argument_Msg(l_api_name, 'NULL','owner_id');
32 raise fnd_api.g_exc_error;
33 --Bug 2723761
34 end if;
35
36 -- elsif p_owner_type <> jtf_ec_pub.g_escalation_owner_type_code then
37
38 if p_owner_type is not NULL and p_owner_type <> fnd_api.g_miss_char then
39 OPEN c_object_code;
40 FETCH c_object_code INTO x;
41
42 IF c_object_code%NOTFOUND THEN
43 Add_Invalid_Argument_Msg(l_api_name, p_owner_type, 'owner_type');
44 raise fnd_api.g_exc_error;
45 END IF;
46 CLOSE c_object_code;
47 else
48 --end changes
49 Add_Invalid_Argument_Msg(l_api_name, 'NULL', 'owner_type');
50 raise fnd_api.g_exc_error;
51 end if;
52
53 EXCEPTION
54
55 WHEN fnd_api.g_exc_error
56 THEN
57 x_return_status := fnd_api.g_ret_sts_error;
58
59 WHEN OTHERS THEN
60
61 x_return_status := fnd_api.g_ret_sts_unexp_error;
62 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
63 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
64 fnd_msg_pub.ADD;
65
66 END Validate_Owner;
67
68 ----------------------------------------------------------------------------------------
69 --- Validate_Requester
70 ----------------------------------------------------------------------------------------
71
72 PROCEDURE Validate_Requester(p_escalation_id IN NUMBER,
73 x_return_status OUT NOCOPY VARCHAR2) Is
74
75 l_api_name varchar2(30) := 'Validate_Requester';
76 l_dummy varchar2(2);
77
78
79 BEGIN
80
81 x_return_status := fnd_api.g_ret_sts_success;
82
83 Select 'x' into l_dummy
84 From jtf_task_contacts
85 Where task_id = p_escalation_id
86 And escalation_requester_flag = 'Y';
87
88 Exception
89 When no_data_found then
90 fnd_message.set_name ('JTF', 'JTF_EC_REQ_API_NULL');
91 fnd_msg_pub.Add;
92 x_return_status := fnd_api.g_ret_sts_error;
93 FND_MSG_PUB.Add_Exc_Msg
94 (G_PKG_NAME,
95 l_api_name
96 );
97
98 When too_many_rows then
99 fnd_message.set_name ('JTF', 'JTF_TK_REQUESTER_FLAG');
100 fnd_msg_pub.Add;
101 x_return_status := fnd_api.g_ret_sts_error;
102 FND_MSG_PUB.Add_Exc_Msg
103 (G_PKG_NAME,
104 l_api_name
105 );
106 When others then
107 x_return_status := fnd_api.g_ret_sts_unexp_error;
108 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
109 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
110 fnd_msg_pub.ADD;
111
112 FND_MSG_PUB.Add_Exc_Msg
113 (G_PKG_NAME,
114 l_api_name
115 );
116
117 END Validate_Requester;
118
119
120
121 PROCEDURE Add_Invalid_Argument_Msg
122 ( p_token_api_name IN VARCHAR2,
123 p_token_value IN VARCHAR2,
124 p_token_parameter IN VARCHAR2
125 )
126
127 IS
128
129 BEGIN
130 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
131 FND_MESSAGE.Set_Name('JTF','JTF_EC_API_INV_PARAMETER' );
132 FND_MESSAGE.Set_Token('API_NAME', p_token_api_name );
133 FND_MESSAGE.Set_Token('VALUE', p_token_value );
134 FND_MESSAGE.Set_Token('PARAMETER',p_token_parameter);
135 FND_MSG_PUB.Add;
136 END IF;
137 END Add_Invalid_Argument_Msg;
138
139
140 ----------------------------------------------------------------------------------------
141 --- Validate_Requester
142 ----------------------------------------------------------------------------------------
143 PROCEDURE Validate_Esc_Status (
144 p_esc_status_id IN NUMBER,
145 p_esc_status_name IN VARCHAR2,
146 x_return_status OUT NOCOPY VARCHAR2,
147 x_esc_status_id OUT NOCOPY NUMBER
148 ) Is
149
150 l_api_name varchar2(61) := 'Validate_Esc_Status';
151
152 cursor c_esc_status_id (p_esc_status_id NUMBER) Is
153 SELECT task_status_id
154 FROM jtf_ec_statuses_vl
155 WHERE task_status_id = p_esc_status_id
156 AND NVL (start_date_active, sysdate) <= sysdate
157 AND NVL (end_date_active, sysdate) >= sysdate;
158
159 cursor c_esc_status_name (p_esc_status_name VARCHAR2) Is
160 SELECT task_status_id
161 FROM jtf_ec_statuses_vl
162 WHERE name = p_esc_status_name
163 AND NVL (start_date_active, sysdate) <= sysdate
164 AND NVL (end_date_active, sysdate) >= sysdate;
165
166
167
168 BEGIN
169
170 x_return_status := fnd_api.g_ret_sts_success;
171
172 if p_esc_status_id is not NULL and p_esc_status_id <> fnd_api.g_miss_num then
173 open c_esc_status_id(p_esc_status_id);
174 fetch c_esc_status_id into x_esc_status_id;
175 if c_esc_status_id%NOTFOUND then
176 close c_esc_status_id;
177 Add_Invalid_Argument_Msg(l_api_name, to_char(p_esc_status_id), 'esc_status_id');
178 raise fnd_api.g_exc_error;
179 end if;
180 close c_esc_status_id;
181
182 if p_esc_status_name <> fnd_api.g_miss_char then
183 jtf_ec_util.add_param_ignored_msg(l_api_name, 'status_name');
184 end if;
185
186 elsif p_esc_status_name is not NULL and p_esc_status_name <> fnd_api.g_miss_char then
187 open c_esc_status_name(p_esc_status_name);
188 fetch c_esc_status_name into x_esc_status_id;
189 if c_esc_status_name%NOTFOUND then
190 close c_esc_status_name;
191 Add_Invalid_Argument_Msg(l_api_name, p_esc_status_name, 'esc_status_name');
192 raise fnd_api.g_exc_error;
193 end if;
194 close c_esc_status_name;
195 elsif p_esc_status_name is NULL and p_esc_status_id is NULL then
196 Add_Invalid_Argument_Msg(l_api_name, 'NULL', 'esc_status_id');
197 Add_Invalid_Argument_Msg(l_api_name, 'NULL', 'esc_status_name');
198 raise fnd_api.g_exc_error;
199 else
200 Add_Missing_Param_Msg(l_api_name, 'esc_status_id');
201 Add_Missing_Param_Msg(l_api_name, 'esc_status_name');
202 raise fnd_api.g_exc_error;
203 end if;
204
205
206
207 EXCEPTION
208
209 WHEN fnd_api.g_exc_error
210 THEN
211 x_return_status := fnd_api.g_ret_sts_error;
212
213 WHEN OTHERS THEN
214
215 x_return_status := fnd_api.g_ret_sts_unexp_error;
216 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
217 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
218 fnd_msg_pub.ADD;
219
220 END Validate_Esc_Status;
221
222
223 ----------------------------------------------------------------------------------------
224 -- Validate Lookup
225 ----------------------------------------------------------------------------------------
226
227 FUNCTION Validate_Lookup(p_lookup_type IN VARCHAR2 ,
228 p_lookup_code IN VARCHAR2
229 )RETURN BOOLEAN Is
230 l_temp varchar2(1);
231
232 Cursor c_lookup Is
233 SELECT 'x'
234 FROM fnd_lookups
235 WHERE lookup_type = p_lookup_type
236 AND lookup_code = p_lookup_code
237 AND enabled_flag = 'Y'
238 AND nvl(start_date_active,sysdate) <= sysdate
239 AND nvl(end_date_active, sysdate) >= sysdate;
240
241 BEGIN
242
243 open c_lookup;
244 fetch c_lookup into l_temp;
245 if c_lookup%FOUND then
246 RETURN TRUE;
247 else
248 RETURN FALSE;
249 end if;
250
251 EXCEPTION
252 WHEN OTHERS THEN
253 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
254 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
255 fnd_msg_pub.ADD;
256 RETURN FALSE;
257 END Validate_Lookup;
258
259
260 ----------------------------------------------------------------------------------------
261 -- Add_Param_Ignored_Msg
262 ----------------------------------------------------------------------------------------
263
264 PROCEDURE Add_Param_Ignored_Msg
265 ( p_token_api_name VARCHAR2,
266 p_token_ignored_param VARCHAR2
267 )
268 IS
269 BEGIN
270 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
271 FND_MESSAGE.Set_Name('JTF', 'JTF_EC_API_IGN_PARAMETER');
272 FND_MESSAGE.Set_Token('API_NAME', p_token_api_name);
273 FND_MESSAGE.Set_Token('IGNORED_PARAM', p_token_ignored_param);
274 FND_MSG_PUB.Add;
275 END IF;
276 END Add_Param_Ignored_Msg;
277
278
279 ----------------------------------------------------------------------------------------
280 -- Add missing parameter procedure
281 ----------------------------------------------------------------------------------------
282
283 PROCEDURE Add_Missing_Param_Msg
284 ( p_token_api_name VARCHAR2,
285 p_token_miss_param VARCHAR2
286 ) Is
287
288 Begin
289
290 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
291 FND_MESSAGE.Set_Name('JTF', 'JTF_EC_API_MISS_PARAMETER');
292 FND_MESSAGE.Set_Token('API_NAME', p_token_api_name);
293 FND_MESSAGE.Set_Token('MISSING_PARAM', p_token_miss_param);
294 FND_MSG_PUB.Add;
295 END IF;
296
297 END Add_Missing_Param_Msg;
298
299 ----------------------------------------------------------------------------------------
300 -- Check_If_Escalated
301 ----------------------------------------------------------------------------------------
302
303 FUNCTION Check_If_Escalated (p_object_type_code IN VARCHAR2,
304 p_object_id IN NUMBER,
305 p_object_name IN VARCHAR2,
306 x_task_ref_id OUT NOCOPY NUMBER) RETURN BOOLEAN Is
307
308
309 cursor c_esc_doc_exists_id( P_OBJECT_TYPE_CODE VARCHAR2,
310 P_OBJECT_ID NUMBER) Is
311 Select r.task_reference_id
312 from jtf_tasks_b t,
313 jtf_task_references_b r,
314 jtf_ec_statuses_vl s
315 where r.reference_code = 'ESC'
316 and r.object_id = p_object_id
317 and r.object_type_code = p_object_type_code
318 and r.task_id = t.task_id
319 and t.task_type_id = 22
320 and t.task_status_id = s.task_status_id
321 and nvl(s.completed_flag,'N') ='N'
322 and nvl(s.cancelled_flag,'N') = 'N'
323 and nvl(s.closed_flag, 'N') = 'N';
324
325 cursor c_esc_doc_exists_name( P_OBJECT_TYPE_CODE VARCHAR2,
326 P_OBJECT_NAME VARCHAR2) Is
327 Select r.task_reference_id
328 from jtf_tasks_b t,
329 jtf_task_references_b r,
330 jtf_ec_statuses_vl s
331 where r.reference_code = 'ESC'
332 and r.object_name = p_object_name
333 and r.object_type_code = p_object_type_code
334 and t.task_type_id = 22
335 and r.task_id = t.task_id
336 and t.task_status_id = s.task_status_id
337 and nvl(s.completed_flag,'N') ='N'
338 and nvl(s.cancelled_flag,'N') = 'N'
339 and nvl(s.closed_flag, 'N') = 'N';
340
341
342 BEGIN
343
344
345 if p_object_id is not NULL then
346 open c_esc_doc_exists_id(p_object_type_code, p_object_id);
347 fetch c_esc_doc_exists_id into x_task_ref_id;
348 if c_esc_doc_exists_id%FOUND then
349 close c_esc_doc_exists_id;
350 RETURN TRUE;
351 else
352 close c_esc_doc_exists_id;
353 RETURN FALSE;
354 end if;
355 elsif p_object_name is not null then
356 open c_esc_doc_exists_name(p_object_type_code, p_object_name);
357 fetch c_esc_doc_exists_name into x_task_ref_id;
358 if c_esc_doc_exists_name%FOUND then
359 close c_esc_doc_exists_name;
360 RETURN TRUE;
361 else
362 close c_esc_doc_exists_name;
363 RETURN FALSE;
364 end if;
365 else
366 RETURN FALSE;
367 end if;
368
369 exception
370 when others then
371
372 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
373 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
374 fnd_msg_pub.ADD;
375
376 RETURN FALSE;
377
378 END Check_If_Escalated;
379
380 FUNCTION Reference_Duplicated (p_object_type_code IN VARCHAR2,
381 p_object_id IN NUMBER,
382 p_object_name IN VARCHAR2,
383 p_reference_code IN VARCHAR2,
384 p_escalation_id IN NUMBER) RETURN BOOLEAN Is
385
386
387 cursor c_ref_doc_exists_id( P_OBJECT_TYPE_CODE VARCHAR2,
388 P_OBJECT_ID NUMBER,
389 P_REFERENCE_CODE VARCHAR2,
390 P_ESCALATION_ID NUMBER) Is
391 SELECT 'x'
392 FROM jtf_task_references_b
393 WHERE object_id = p_object_id
394 AND object_type_code = p_object_type_code
395 AND task_id = p_escalation_id
396 AND reference_code = p_reference_code;
397
398
399 cursor c_ref_doc_exists_name( P_OBJECT_TYPE_CODE VARCHAR2,
400 P_OBJECT_NAME VARCHAR2,
401 P_REFERENCE_CODE VARCHAR2,
402 P_ESCALATION_ID NUMBER) Is
403 SELECT 'x'
404 FROM jtf_task_references_b
405 WHERE object_name = p_object_name
406 AND object_type_code = p_object_type_code
407 AND task_id = p_escalation_id
408 AND reference_code = p_reference_code;
409
410 l_dummy varchar2(2) := NULL;
411
412
413 BEGIN
414
415
416 if p_object_id is not NULL then
417 open c_ref_doc_exists_id(p_object_type_code, p_object_id,p_reference_code, p_escalation_id);
418 fetch c_ref_doc_exists_id into l_dummy;
419 if c_ref_doc_exists_id%FOUND then
420 close c_ref_doc_exists_id;
421 RETURN TRUE;
422 else
423 close c_ref_doc_exists_id;
424 RETURN FALSE;
425 end if;
426 elsif p_object_name is not null then
427 open c_ref_doc_exists_name(p_object_type_code, p_object_name,p_reference_code, p_escalation_id);
428 fetch c_ref_doc_exists_name into l_dummy;
429 if c_ref_doc_exists_name%FOUND then
430 close c_ref_doc_exists_name;
431 RETURN TRUE;
432 else
433 close c_ref_doc_exists_name;
434 RETURN FALSE;
435 end if;
436 else
437 RETURN FALSE;
438 end if;
439
440 exception
441 when others then
442
443 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
444 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
445 fnd_msg_pub.ADD;
446
447 RETURN FALSE;
448
449 END Reference_Duplicated;
450
451 FUNCTION Contact_Duplicated(p_contact_id IN NUMBER,
452 p_contact_type_code IN VARCHAR2,
453 p_escalation_id IN NUMBER) RETURN BOOLEAN IS
454
455 cursor c_contact_exists(p_contact_id NUMBER,
456 p_contact_type_code VARCHAR2,
457 p_escalation_id NUMBER) Is
458 Select 'x'
459 from jtf_task_contacts
460 where task_id = p_escalation_id
461 and contact_id = p_contact_id
462 and contact_type_code = p_contact_type_code;
463
464 l_dummy varchar2(2);
465
466 BEGIN
467
468 open c_contact_exists(p_contact_id, p_contact_type_code, p_escalation_id);
469 fetch c_contact_exists into l_dummy;
470
471 if c_contact_exists%FOUND then
472 RETURN TRUE;
473 else
474 RETURN FALSE;
475 end if;
476
477 exception
478 when others then
479
480 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
481 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
482 fnd_msg_pub.ADD;
483
484 RETURN FALSE;
485
486 END Contact_Duplicated;
487
488 PROCEDURE Validate_Desc_Flex
489 ( p_api_name IN VARCHAR2,
490 p_application_short_name IN VARCHAR2,
491 p_desc_flex_name IN VARCHAR2,
492 p_desc_segment1 IN VARCHAR2,
493 p_desc_segment2 IN VARCHAR2,
494 p_desc_segment3 IN VARCHAR2,
495 p_desc_segment4 IN VARCHAR2,
496 p_desc_segment5 IN VARCHAR2,
497 p_desc_segment6 IN VARCHAR2,
498 p_desc_segment7 IN VARCHAR2,
499 p_desc_segment8 IN VARCHAR2,
500 p_desc_segment9 IN VARCHAR2,
501 p_desc_segment10 IN VARCHAR2,
502 p_desc_segment11 IN VARCHAR2,
503 p_desc_segment12 IN VARCHAR2,
504 p_desc_segment13 IN VARCHAR2,
505 p_desc_segment14 IN VARCHAR2,
506 p_desc_segment15 IN VARCHAR2,
507 p_desc_context IN VARCHAR2,
508 p_resp_appl_id IN NUMBER := NULL,
509 p_resp_id IN NUMBER := NULL,
510 x_return_status OUT NOCOPY VARCHAR2
511 )
512 IS
513 l_error_message VARCHAR2(2000);
514
515 BEGIN
516 -- Initialize API return status to success
517 x_return_status := FND_API.G_RET_STS_SUCCESS;
518
519
520 IF ( p_desc_context || p_desc_segment1 || p_desc_segment2 ||
521 p_desc_segment3 || p_desc_segment4 || p_desc_segment5 ||
522 p_desc_segment6 || p_desc_segment7 || p_desc_segment8 ||
523 p_desc_segment9 || p_desc_segment10 || p_desc_segment11 ||
524 p_desc_segment12 || p_desc_segment13 || p_desc_segment14 ||
525 p_desc_segment15
526 ) IS NOT NULL THEN
527
528 FND_FLEX_DESCVAL.Set_Context_Value(p_desc_context);
529 FND_FLEX_DESCVAL.Set_Column_Value('ATTRIBUTE_1', p_desc_segment1);
530 FND_FLEX_DESCVAL.Set_Column_Value('ATTRIBUTE_2', p_desc_segment2);
531 FND_FLEX_DESCVAL.Set_Column_Value('ATTRIBUTE_3', p_desc_segment3);
532 FND_FLEX_DESCVAL.Set_Column_Value('ATTRIBUTE_4', p_desc_segment4);
533 FND_FLEX_DESCVAL.Set_Column_Value('ATTRIBUTE_5', p_desc_segment5);
534 FND_FLEX_DESCVAL.Set_Column_Value('ATTRIBUTE_6', p_desc_segment6);
535 FND_FLEX_DESCVAL.Set_Column_Value('ATTRIBUTE_7', p_desc_segment7);
536 FND_FLEX_DESCVAL.Set_Column_Value('ATTRIBUTE_8', p_desc_segment8);
537 FND_FLEX_DESCVAL.Set_Column_Value('ATTRIBUTE_9', p_desc_segment9);
538 FND_FLEX_DESCVAL.Set_Column_Value('ATTRIBUTE_10', p_desc_segment10);
539 FND_FLEX_DESCVAL.Set_Column_Value('ATTRIBUTE_11', p_desc_segment11);
540 FND_FLEX_DESCVAL.Set_Column_Value('ATTRIBUTE_12', p_desc_segment12);
541 FND_FLEX_DESCVAL.Set_Column_Value('ATTRIBUTE_13', p_desc_segment13);
542 FND_FLEX_DESCVAL.Set_Column_Value('ATTRIBUTE_14', p_desc_segment14);
543 FND_FLEX_DESCVAL.Set_Column_Value('ATTRIBUTE_15', p_desc_segment15);
544 IF NOT FND_FLEX_DESCVAL.Validate_Desccols
545 ( appl_short_name => p_application_short_name,
546 desc_flex_name => p_desc_flex_name,
547 resp_appl_id => p_resp_appl_id,
548 resp_id => p_resp_id
549 ) THEN
550 l_error_message := FND_FLEX_DESCVAL.Error_Message;
551 -- need to return a message
552 x_return_status := FND_API.G_RET_STS_ERROR;
553 END IF;
554 END IF;
555
556 END Validate_Desc_Flex;
557
558 ----------------------------------------------------------------------------------------
559 -- Validate Escalation Document
560 ----------------------------------------------------------------------------------------
561
562 PROCEDURE Validate_Esc_Document(p_esc_id IN NUMBER,
563 p_esc_number IN VARCHAR2,
564 x_esc_id OUT NOCOPY NUMBER,
565 x_return_status OUT NOCOPY VARCHAR2) Is
566
567 l_api_name VARCHAR2(30) := 'Valdate_Esc_Document';
568
569 cursor c_esc_id(p_escal_id NUMBER) Is
570 select task_id
571 from jtf_tasks_b
572 where task_id = p_escal_id
573 and task_type_id = 22;
574
575 cursor c_esc_number(p_esc_number VARCHAR2) Is
576 select task_id
577 from jtf_tasks_b
578 where task_number = p_esc_number
579 and task_type_id = 22;
580
581 l_esc_id jtf_tasks_b.task_id%TYPE;
582
583 BEGIN
584
585 -- Initialize API return status to success
586 x_return_status := FND_API.G_RET_STS_SUCCESS;
587
588 if p_esc_id is not NULL then
589 open c_esc_id(p_esc_id);
590 fetch c_esc_id into l_esc_id;
591 if c_esc_id%NOTFOUND then
592 close c_esc_id;
593 jtf_ec_util.Add_Invalid_Argument_Msg(l_api_name, p_esc_id, 'escalation_id');
594 raise fnd_api.g_exc_error;
595 end if;
596 close c_esc_id;
597 elsif p_esc_number is not NULL then
598 open c_esc_number(p_esc_number);
599 fetch c_esc_number into l_esc_id;
600 if c_esc_number%NOTFOUND then
601 close c_esc_number;
602 jtf_ec_util.Add_Invalid_Argument_Msg(l_api_name, p_esc_number, 'escalation_number');
603 raise fnd_api.g_exc_error;
604 end if;
605 close c_esc_number;
606 else jtf_ec_util.Add_Invalid_Argument_Msg(l_api_name, 'NULL', 'p_esc_id');
607 jtf_ec_util.Add_Invalid_Argument_Msg(l_api_name, 'NULL', 'p_esc_number');
608 end if;
609
610 x_esc_id := l_esc_id;
611
612 EXCEPTION
613
614 WHEN fnd_api.g_exc_error
615 THEN
616 x_return_status := fnd_api.g_ret_sts_error;
617
618 WHEN OTHERS THEN
619
620 x_return_status := fnd_api.g_ret_sts_unexp_error;
621 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
622 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
623 fnd_msg_pub.ADD;
624
625 END Validate_Esc_Document;
626
627 ----------------------------------------------------------------------------------------
628 -- Check whether the task can be closed. If it can sets the close_date to sysdate.
629 ----------------------------------------------------------------------------------------
630
631 Procedure Check_Completed_Status(p_status_id IN NUMBER,
632 p_esc_id IN NUMBER,
633 p_esc_level IN VARCHAR2,
634 x_closed_flag OUT NOCOPY VARCHAR2,
635 x_return_status OUT NOCOPY VARCHAR2) IS
636 l_api_name varchar2(30) := 'Check_Completed_Status';
637
638 cursor c_chk_completed (p_status_id NUMBER) is
639 select 'x'
640 from jtf_ec_statuses_vl
641 where task_status_id = p_status_id
642 and completed_flag = 'Y';
643
644
645 -- the escalation cannot be closed if there are open tasks for it with restrict_closure_flag = 'Y'
646
647 cursor c_chk_open_tasks(p_task_id NUMBER) Is
648 select 'x'
649 from jtf_tasks_b t,
650 jtf_task_statuses_vl s
651 where t.source_object_id = p_task_id
652 and t.source_object_type_code = 'ESC'
653 and t.restrict_closure_flag = 'Y'
654 and t.task_status_id = s.task_status_id
655 and nvl(s.cancelled_flag, 'N') = 'N'
656 and nvl(s.completed_flag, 'N') = 'N'
657 and nvl(s.closed_flag, 'N') = 'N';
658
659 cursor c_get_esc_level(p_task_id NUMBER) Is
660 Select escalation_level
661 from jtf_tasks_b
662 where task_id = p_task_id;
663
664
665 l_dummy varchar2(1);
666 l_esc_level varchar2(30) := p_esc_level;
667 l_close_deesc varchar2(1) :='x';
668
669
670 BEGIN
671
672 -- Initialize API return status to success
673 x_return_status := FND_API.G_RET_STS_SUCCESS;
674
675 fnd_profile.get('JTF_EC_CLOSE_WHEN_DEESCALATED', l_close_deesc);
676
677
678 if p_esc_level = fnd_api.g_miss_char then
679
680 Open c_get_esc_level(p_esc_id);
681 fetch c_get_esc_level into l_esc_level;
682 if c_get_esc_level%NOTFOUND then
683 close c_get_esc_level;
684 raise fnd_api.g_exc_error; -- need to give the reason
685 end if;
686 close c_get_esc_level;
687
688 end if;
689
690 x_closed_flag := 'N';
691
692 Open c_chk_completed(p_status_id);
693 fetch c_chk_completed into l_dummy;
694 if c_chk_completed%found then
695
696 x_closed_flag := 'Y';
697 close c_chk_completed;
698
699 open c_chk_open_tasks(p_esc_id) ;
700 fetch c_chk_open_tasks into l_dummy;
701 if c_chk_open_tasks%found then
702 close c_chk_open_tasks;
703 fnd_message.set_name('JTF','JTF_EC_RESTRICT_TASKS');
704 fnd_msg_pub.Add;
705 x_closed_flag := 'N';
706 raise fnd_api.g_exc_error;
707 else
708 close c_chk_open_tasks;
709 end if;
710
711
712 -- check whether the level is De-Escalated.
713
714 if l_close_deesc = 'Y'
715 and l_esc_level <> 'DE' then
716 fnd_message.set_name('JTF','JTF_EC_CLOSE_WHEN_DEESCALATED');
717 fnd_msg_pub.Add;
718 x_closed_flag := 'N';
719 raise fnd_api.g_exc_error;
720 end if;
721 else
722 close c_chk_completed;
723 end if;
724
725
726 EXCEPTION
727
728 WHEN fnd_api.g_exc_error
729 THEN
730 x_return_status := fnd_api.g_ret_sts_error;
731
732 WHEN OTHERS THEN
733
734 x_return_status := fnd_api.g_ret_sts_unexp_error;
735 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
736 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
737 fnd_msg_pub.ADD;
738
739 END Check_Completed_Status;
740
741 PROCEDURE Conv_Miss_Num(p_number IN OUT NOCOPY NUMBER) Is
742 BEGIN
743
744 if p_number = fnd_api.g_miss_num then
745 p_number := NULL;
746 end if;
747
748 END Conv_Miss_Num;
749
750 PROCEDURE Conv_Miss_Date(p_date IN OUT NOCOPY DATE) Is
751 BEGIN
752
753 if p_date = fnd_api.g_miss_date then
754 p_date := NULL;
755 end if;
756
757 END Conv_Miss_Date;
758
759 PROCEDURE Conv_Miss_Char(p_char IN OUT NOCOPY VARCHAR2) Is
760 BEGIN
761
762 if p_char = fnd_api.g_miss_char then
763 p_char := NULL;
764 end if;
765
766 END Conv_Miss_Char;
767
768 ----------------------------------------------------------------------------------------
769 -- Validate task_phone_id against the escalation_id
770 ----------------------------------------------------------------------------------------
771
772 PROCEDURE Validate_Task_Phone_Id(p_task_phone_id IN NUMBER,
773 p_escalation_id IN NUMBER,
774 x_return_status OUT NOCOPY VARCHAR2) Is
775
776 l_api_name varchar2(30) := 'Validate_Task_Phone_Id';
777
778 cursor c_check_phone_id(p_task_phone_id NUMBER,
779 p_escalation_id NUMBER) Is
780 select 'x'
781 from jtf_task_phones ph,
782 jtf_task_contacts c
783 where ph.task_phone_id = p_task_phone_id
784 and ph.task_contact_id = c.task_contact_id
785 and c.task_id = p_escalation_id;
786
787 l_dummy varchar2(1);
788
789 BEGIN
790
791 -- Initialize API return status to success
792 x_return_status := FND_API.G_RET_STS_SUCCESS;
793
794 open c_check_phone_id(p_task_phone_id,p_escalation_id);
795 fetch c_check_phone_id into l_dummy;
796 if c_check_phone_id%NOTFOUND then
797 close c_check_phone_id;
798 Add_Invalid_Argument_Msg(l_api_name, p_task_phone_id ,'task_phone_id');
799 raise fnd_api.g_exc_error;
800 else
801 close c_check_phone_id;
802 end if;
803
804
805 EXCEPTION
806
807 WHEN fnd_api.g_exc_error
808 THEN
809 x_return_status := fnd_api.g_ret_sts_error;
810
811 WHEN OTHERS THEN
812
813 x_return_status := fnd_api.g_ret_sts_unexp_error;
814 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
815 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
816 fnd_msg_pub.Add;
817
818 END Validate_Task_Phone_Id;
819
820 ----------------------------------------------------------------------------------------
821 -- Validate_Contact_id against the escalation_id
822 ----------------------------------------------------------------------------------------
823
824
825 PROCEDURE Validate_Contact_id(p_contact_id IN NUMBER,
826 p_contact_type_code IN VARCHAR2,
827 p_escalation_id IN NUMBER,
828 x_task_contact_id OUT NOCOPY NUMBER,
829 x_return_status OUT NOCOPY VARCHAR2) Is
830
831 l_api_name varchar2(30) := 'Validate_Contact_id';
832
833 cursor c_check_contact( p_contact_id NUMBER,
834 p_contact_type_code VARCHAR2,
835 p_escalation_id NUMBER) Is
836 select c.task_contact_id
837 from jtf_task_contacts c
838 where c.contact_id = p_contact_id
839 and c.task_id = p_escalation_id
840 and c.contact_type_code = p_contact_type_code;
841
842 BEGIN
843
844 -- Initialize API return status to success
845 x_return_status := FND_API.G_RET_STS_SUCCESS;
846
847 open c_check_contact(p_contact_id, p_contact_type_code, p_escalation_id);
848 fetch c_check_contact into x_task_contact_id;
849 if c_check_contact%NOTFOUND then
850 close c_check_contact;
851 Add_Invalid_Argument_Msg(l_api_name, p_contact_id ,'contact_id');
852 Add_Invalid_Argument_Msg(l_api_name, p_contact_type_code,'contact_type_code');
853 raise fnd_api.g_exc_error;
854 else
855 close c_check_contact;
856 end if;
857
858
859 EXCEPTION
860
861 WHEN fnd_api.g_exc_error
862 THEN
863 x_return_status := fnd_api.g_ret_sts_error;
864
865 WHEN OTHERS THEN
866
867 x_return_status := fnd_api.g_ret_sts_unexp_error;
868 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
869 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
870 fnd_msg_pub.Add;
871
872 END Validate_Contact_id;
873
874 ----------------------------------------------------------------------------------------
875 -- Validate task_phone_id against the escalation_id
876 ----------------------------------------------------------------------------------------
877
878 PROCEDURE Validate_Task_Contact_Id(p_task_contact_id IN NUMBER,
879 p_escalation_id IN NUMBER,
880 x_return_status OUT NOCOPY VARCHAR2) Is
881
882 l_api_name varchar2(30) := 'Validate_Task_Contact_Id';
883
884 cursor c_check_task_contact_id(p_task_contact_id NUMBER,
885 p_escalation_id NUMBER) Is
886 select 'x'
887 from jtf_task_contacts
888 where task_contact_id = p_task_contact_id
889 and task_id = p_escalation_id;
890
891 l_dummy varchar2(1);
892
893 BEGIN
894
895 -- Initialize API return status to success
896 x_return_status := FND_API.G_RET_STS_SUCCESS;
897
898 open c_check_task_contact_id(p_task_contact_id,p_escalation_id);
899 fetch c_check_task_contact_id into l_dummy;
900 if c_check_task_contact_id%NOTFOUND then
901 close c_check_task_contact_id;
902 Add_Invalid_Argument_Msg(l_api_name, p_task_contact_id ,'task_contact_id');
903 raise fnd_api.g_exc_error;
904 else
905 close c_check_task_contact_id;
906 end if;
907
908
909 EXCEPTION
910
911 WHEN fnd_api.g_exc_error
912 THEN
913 x_return_status := fnd_api.g_ret_sts_error;
914
915 WHEN OTHERS THEN
916
917 x_return_status := fnd_api.g_ret_sts_unexp_error;
918 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
919 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
920 fnd_msg_pub.Add;
921
922 END Validate_Task_Contact_Id;
923
924 ----------------------------------------------------------------------------------------
925 -- Validate task_reference_id against the escalation_id
926 ----------------------------------------------------------------------------------------
927
928 PROCEDURE Validate_Task_Reference_Id(p_task_reference_id IN NUMBER,
929 p_escalation_id IN NUMBER,
930 x_return_status OUT NOCOPY VARCHAR2) Is
931
932 l_api_name varchar2(30) := 'Validate_Task_Reference_Id';
933
934 cursor c_check_task_ref_id(p_task_reference_id NUMBER,
935 p_escalation_id NUMBER) Is
936 select 'x'
937 from jtf_task_references_vl
938 where task_reference_id = p_task_reference_id
939 and task_id = p_escalation_id;
940
941 l_dummy varchar2(1);
942
943 BEGIN
944
945 -- Initialize API return status to success
946 x_return_status := FND_API.G_RET_STS_SUCCESS;
947
948 open c_check_task_ref_id(p_task_reference_id,p_escalation_id);
949 fetch c_check_task_ref_id into l_dummy;
950 if c_check_task_ref_id%NOTFOUND then
951 close c_check_task_ref_id;
952 Add_Invalid_Argument_Msg(l_api_name, p_task_reference_id ,'task_reference_id');
953 raise fnd_api.g_exc_error;
954 else
955 close c_check_task_ref_id;
956 end if;
957
958
959 EXCEPTION
960
961 WHEN fnd_api.g_exc_error
962 THEN
963 x_return_status := fnd_api.g_ret_sts_error;
964
965 WHEN OTHERS THEN
966
967 x_return_status := fnd_api.g_ret_sts_unexp_error;
968 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
969 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
970 fnd_msg_pub.Add;
971
972 END Validate_Task_Reference_Id;
973
974
975 ----------------------------------------------------------------------------------------
976 -- Validate_Who_info
977 ----------------------------------------------------------------------------------------
978
979 PROCEDURE Validate_Who_Info (
980 p_api_name IN VARCHAR2,
981 p_user_id IN NUMBER,
982 p_login_id IN NUMBER,
983 x_return_status OUT NOCOPY VARCHAR2
984 ) Is
985
986 l_dummy VARCHAR2(1);
987
988 cursor c_check_user_id (p_user_id NUMBER) is
989 select 'x'
990 from fnd_user
991 where user_id = p_user_id
992 and nvl(start_date,sysdate) <= sysdate
993 and nvl(end_date, sysdate) >= sysdate;
994
995 cursor c_check_login_id (p_login_id NUMBER, p_user_id NUMBER) is
996 select 'x'
997 from fnd_logins
998 where login_id = p_login_id
999 and user_id = p_user_id;
1000
1001 BEGIN
1002
1003 -- Initialize Return Status to SUCCESS
1004 x_return_status := FND_API.G_RET_STS_SUCCESS;
1005
1006 open c_check_user_id(p_user_id);
1007 fetch c_check_user_id into l_dummy;
1008 if c_check_user_id%NOTFOUND then
1009 close c_check_user_id;
1010 Add_Invalid_Argument_Msg(p_api_name, p_user_id ,'user_id');
1011 raise fnd_api.g_exc_error;
1012 else
1013 close c_check_user_id;
1014 end if;
1015
1016 if p_login_id is not NULL then
1017 open c_check_login_id(p_login_id, p_user_id);
1018 fetch c_check_login_id into l_dummy;
1019 if c_check_login_id%NOTFOUND then
1020 close c_check_login_id;
1021 Add_Invalid_Argument_Msg(p_api_name, p_login_id ,'login_id');
1022 raise fnd_api.g_exc_error;
1023 else
1024 close c_check_login_id;
1025 end if;
1026
1027 end if;
1028 EXCEPTION
1029
1030 WHEN fnd_api.g_exc_error
1031 THEN
1032 x_return_status := fnd_api.g_ret_sts_error;
1033
1034 WHEN OTHERS THEN
1035
1036 x_return_status := fnd_api.g_ret_sts_unexp_error;
1037 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
1038 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
1039 fnd_msg_pub.Add;
1040
1041
1042 END Validate_Who_Info;
1043
1044 ----------------------------------------------------------------------------------------
1045 -- Validate note_id against the escalation_id
1046 ----------------------------------------------------------------------------------------
1047
1048 PROCEDURE Validate_Note_Id(p_note_id IN NUMBER,
1049 p_escalation_id IN NUMBER,
1050 x_return_status OUT NOCOPY VARCHAR2) Is
1051
1052 l_api_name varchar2(30) := 'Validate_Note_Id';
1053
1054
1055 cursor c_check_note_id(p_note_id NUMBER,
1056 p_escalation_id NUMBER) Is
1057 select 'x'
1058 from jtf_notes_b
1059 where jtf_note_id = p_note_id
1060 and source_object_id = p_escalation_id;
1061
1062
1063 l_dummy varchar2(1);
1064
1065 BEGIN
1066
1067 -- Initialize API return status to success
1068 x_return_status := FND_API.G_RET_STS_SUCCESS;
1069
1070 open c_check_note_id(p_note_id,p_escalation_id);
1071 fetch c_check_note_id into l_dummy;
1072 if c_check_note_id%NOTFOUND then
1073 close c_check_note_id;
1074 Add_Invalid_Argument_Msg(l_api_name, p_note_id ,'note_id');
1075 raise fnd_api.g_exc_error;
1076 else
1077 close c_check_note_id;
1078 end if;
1079
1080
1081 EXCEPTION
1082
1083 WHEN fnd_api.g_exc_error
1084 THEN
1085 x_return_status := fnd_api.g_ret_sts_error;
1086
1087 WHEN OTHERS THEN
1088
1089 x_return_status := fnd_api.g_ret_sts_unexp_error;
1090 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
1091 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
1092 fnd_msg_pub.Add;
1093
1094 END Validate_Note_Id;
1095
1096 FUNCTION Get_Requester_Name(p_escalation_id IN NUMBER) RETURN VARCHAR2 Is
1097
1098 cursor get_requester_details (p_esc_id NUMBER) Is
1099 SELECT contact_id, contact_type_code
1100 FROM jtf_task_contacts
1101 WHERE task_id = p_esc_id
1102 AND NVL(escalation_requester_flag,'N') = 'Y';
1103
1104
1105 cursor get_customer_name (p_contact_id NUMBER) Is
1106 SELECT subject_party_name
1107 FROM jtf_party_all_contacts_v
1108 WHERE p_contact_id IN (party_id, subject_party_id);
1109
1110 cursor get_emp_name (p_contact_id NUMBER) Is
1111 SELECT full_name
1112 FROM per_all_people_f
1113 WHERE person_id = p_contact_id
1114 AND SYSDATE >= NVL(effective_start_date,SYSDATE)
1115 AND SYSDATE <= NVL(effective_end_date,SYSDATE);
1116
1117
1118 l_contact_id NUMBER;
1119 l_contact_type_code VARCHAR2(30);
1120 l_requester_name PER_ALL_PEOPLE_F.FULL_NAME%type :=NULL; --Bug 2700953
1121
1122 Begin
1123
1124 open get_requester_details(p_escalation_id);
1125 fetch get_requester_details into l_contact_id, l_contact_type_code;
1126 close get_requester_details;
1127
1128 if l_contact_type_code is not NULL
1129 and l_contact_type_code = 'CUST' then
1130 open get_customer_name(l_contact_id);
1131 fetch get_customer_name into l_requester_name;
1132 close get_customer_name;
1133 elsif l_contact_type_code is not NULL
1134 and l_contact_type_code = 'EMP' then
1135 open get_emp_name(l_contact_id);
1136 fetch get_emp_name into l_requester_name;
1137 close get_emp_name;
1138 end if;
1139
1140 RETURN(l_requester_name);
1141
1142 EXCEPTION
1143 WHEN OTHERS THEN
1144
1145 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
1146 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
1147 fnd_msg_pub.ADD;
1148 RETURN NULL;
1149
1150 End Get_Requester_Name;
1151
1152 END JTF_EC_UTIL;
1153