[Home] [Help]
PACKAGE BODY: APPS.JTF_EC_PVT
Source
1 PACKAGE BODY jtf_ec_pvt AS
2 /* $Header: jtfecmab.pls 120.1.12020000.2 2012/07/25 14:53:08 aditysin ship $ */
3 g_user CONSTANT VARCHAR2(30) := fnd_global.user_id;
4 g_false CONSTANT VARCHAR2(30) := fnd_api.g_false;
5 g_true CONSTANT VARCHAR2(30) := fnd_api.g_true;
6
7 PROCEDURE create_escalation (
8 p_api_version IN NUMBER,
9 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
10 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
11 p_esc_id IN NUMBER DEFAULT NULL,
12 p_escalation_name IN VARCHAR2,
13 p_description IN VARCHAR2 DEFAULT NULL,
14 p_escalation_status_name IN VARCHAR2 DEFAULT NULL,
15 p_escalation_status_id IN NUMBER DEFAULT NULL,
16 p_escalation_priority_name IN VARCHAR2 DEFAULT NULL,
17 p_escalation_priority_id IN NUMBER DEFAULT NULL,
18 p_open_date IN DATE DEFAULT NULL,
19 p_close_date IN DATE DEFAULT NULL,
20 p_escalation_owner_type_code IN VARCHAR2 DEFAULT NULL,
21 p_escalation_owner_id IN NUMBER DEFAULT NULL,
22 p_owner_territory_id IN NUMBER DEFAULT NULL,
23 p_assigned_by_name IN VARCHAR2 DEFAULT NULL,
24 p_assigned_by_id IN NUMBER DEFAULT NULL,
25 p_customer_number IN VARCHAR2 DEFAULT NULL,
26 p_customer_id IN NUMBER DEFAULT NULL,
27 p_cust_account_number IN VARCHAR2 DEFAULT NULL,
28 p_cust_account_id IN NUMBER DEFAULT NULL,
29 p_address_id IN NUMBER DEFAULT NULL,
30 p_address_number IN VARCHAR2 DEFAULT NULL,
31 p_target_date IN DATE DEFAULT NULL,
32 p_reason_code IN VARCHAR2 DEFAULT NULL,
33 p_private_flag IN VARCHAR2 DEFAULT NULL,
34 p_publish_flag IN VARCHAR2 DEFAULT NULL,
35 p_workflow_process_id IN NUMBER DEFAULT NULL,
36 p_escalation_level IN VARCHAR2 DEFAULT NULL,
37 x_return_status OUT NOCOPY VARCHAR2,
38 x_msg_count OUT NOCOPY NUMBER,
39 x_msg_data OUT NOCOPY VARCHAR2,
40 x_escalation_id OUT NOCOPY NUMBER,
41 p_attribute1 IN VARCHAR2 DEFAULT null ,
42 p_attribute2 IN VARCHAR2 DEFAULT null ,
43 p_attribute3 IN VARCHAR2 DEFAULT null ,
44 p_attribute4 IN VARCHAR2 DEFAULT null ,
45 p_attribute5 IN VARCHAR2 DEFAULT null ,
46 p_attribute6 IN VARCHAR2 DEFAULT null ,
47 p_attribute7 IN VARCHAR2 DEFAULT null ,
48 p_attribute8 IN VARCHAR2 DEFAULT null ,
49 p_attribute9 IN VARCHAR2 DEFAULT null ,
50 p_attribute10 IN VARCHAR2 DEFAULT null ,
51 p_attribute11 IN VARCHAR2 DEFAULT null ,
52 p_attribute12 IN VARCHAR2 DEFAULT null ,
53 p_attribute13 IN VARCHAR2 DEFAULT null ,
54 p_attribute14 IN VARCHAR2 DEFAULT null ,
55 p_attribute15 IN VARCHAR2 DEFAULT null ,
56 p_attribute_category IN VARCHAR2 DEFAULT null
57 )
58 AS
59 l_api_version CONSTANT NUMBER := 1.0;
60 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_ESCALATION';
61 l_escalation_id jtf_tasks_b.task_id%TYPE;
62 l_escalation_number jtf_tasks_b.task_number%TYPE;
63 --Created for BES enh 2660883
64 l_esc_rec_type jtf_ec_pvt.Esc_Rec_type;
65 BEGIN
66
67 SAVEPOINT create_escalation_pvt;
68
69 x_return_status := fnd_api.g_ret_sts_success;
70
71 IF NOT fnd_api.compatible_api_call (
72 l_api_version,
73 p_api_version,
74 l_api_name,
75 g_pkg_name
76 )
77 THEN
78 RAISE fnd_api.g_exc_unexpected_error;
79 END IF;
80
81 IF fnd_api.to_boolean (p_init_msg_list)
82 THEN
83 fnd_msg_pub.initialize;
84 END IF;
85
86 IF p_escalation_name IS NULL
87 THEN
88 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_NAME');
89 fnd_msg_pub.add;
90 x_return_status := fnd_api.g_ret_sts_unexp_error;
91 RAISE fnd_api.g_exc_unexpected_error;
92 END IF;
93
94 jtf_tasks_pub.create_task (
95 p_api_version => 1.0,
96 p_init_msg_list => fnd_api.g_false,
97 p_commit => fnd_api.g_false,
98 p_task_id => p_esc_id,
99 p_task_name => p_escalation_name,
100 p_task_type_id => jtf_ec_pub.g_escalation_type_id,
101 p_description => p_description,
102 p_task_status_name => p_escalation_status_name,
103 p_task_status_id => p_escalation_status_id,
104 p_task_priority_name => p_escalation_priority_name,
105 p_task_priority_id => p_escalation_priority_id,
106 p_actual_start_date => p_open_date,
107 p_actual_end_date => p_close_date,
108 -- p_owner_type_code => jtf_ec_pub.g_escalation_owner_type_code,
109 p_owner_type_code => p_escalation_owner_type_code,
110 p_owner_id => p_escalation_owner_id,
111 p_owner_territory_id => p_owner_territory_id,
112 /* p_assigned_by_name => p_assigned_by_name ,
113 p_assigned_by_id => p_assigned_by_id ,*/
114 p_customer_number => p_customer_number,
115 p_customer_id => p_customer_id,
116 p_cust_account_number => p_cust_account_number,
117 p_cust_account_id => p_cust_account_id,
118 p_address_id => p_address_id,
119 p_address_number => p_address_number,
120 p_planned_end_date => p_target_date,
121 p_scheduled_end_date => p_target_date,
122 p_timezone_id => NULL,
123 p_timezone_name => NULL,
124 p_source_object_type_code => NULL,
125 p_source_object_id => NULL,
126 p_source_object_name => NULL,
127 p_duration => NULL,
128 p_duration_uom => NULL,
129 p_planned_effort => NULL,
130 p_planned_effort_uom => NULL,
131 p_actual_effort => NULL,
132 p_actual_effort_uom => NULL,
133 p_percentage_complete => NULL,
134 p_reason_code => p_reason_code,
135 p_private_flag => p_private_flag,
136 p_publish_flag => p_publish_flag,
137 p_restrict_closure_flag => NULL,
138 p_multi_booked_flag => NULL,
139 p_milestone_flag => NULL,
140 p_holiday_flag => NULL,
141 p_billable_flag => NULL,
142 p_bound_mode_code => NULL,
143 p_soft_bound_flag => NULL,
144 p_workflow_process_id => p_workflow_process_id,
145 p_notification_flag => NULL,
146 p_notification_period => NULL,
147 p_notification_period_uom => NULL,
148 p_parent_task_number => NULL,
149 p_parent_task_id => NULL,
150 p_alarm_start => NULL,
151 p_alarm_start_uom => NULL,
152 p_alarm_on => NULL,
153 p_alarm_count => NULL,
154 p_alarm_interval => NULL,
155 p_alarm_interval_uom => NULL,
156 p_palm_flag => NULL,
157 p_wince_flag => NULL,
158 p_laptop_flag => NULL,
159 p_device1_flag => NULL,
160 p_device2_flag => NULL,
161 p_device3_flag => NULL,
162 p_costs => NULL,
163 p_currency_code => NULL,
164 p_escalation_level => p_escalation_level,
165 x_return_status => x_return_status,
166 x_msg_count => x_msg_count,
167 x_msg_data => x_msg_data,
168 x_task_id => l_escalation_id,
169 p_attribute1 => p_attribute1,
170 p_attribute2 => p_attribute2,
171 p_attribute3 => p_attribute3,
172 p_attribute4 => p_attribute4,
173 p_attribute5 => p_attribute5,
174 p_attribute6 => p_attribute6,
175 p_attribute7 => p_attribute7,
176 p_attribute8 => p_attribute8,
177 p_attribute9 => p_attribute9,
178 p_attribute10 => p_attribute10,
179 p_attribute11 => p_attribute11,
180 p_attribute12 => p_attribute12,
181 p_attribute13 => p_attribute13,
182 p_attribute14 => p_attribute14,
183 p_attribute15 => p_attribute15,
184 p_attribute_category => p_attribute_category
185 );
186
187 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
188 THEN
189 x_return_status := fnd_api.g_ret_sts_unexp_error;
190 RAISE fnd_api.g_exc_unexpected_error;
191 END IF;
192
193 x_escalation_id := l_escalation_id;
194
195 BEGIN
196 SELECT task_number
197 INTO l_escalation_number
198 FROM jtf_tasks_vl
199 WHERE task_id = l_escalation_id;
200 EXCEPTION
201 WHEN NO_DATA_FOUND
202 THEN
203 x_return_status := fnd_api.g_ret_sts_unexp_error;
204 RAISE fnd_api.g_exc_unexpected_error;
205 END;
206
207 UPDATE jtf_tasks_b
208 SET source_object_type_code = jtf_ec_pub.g_escalation_code,
209 source_object_id = l_escalation_id,
210 source_object_name = l_escalation_number
211 WHERE task_id = l_escalation_id;
212
213 -------
214 -------
215 -------
216 IF fnd_api.to_boolean (p_commit)
217 THEN
218 COMMIT WORK;
219 END IF;
220
221 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
222
223 --Created for BES enh 2660883
224 begin
225
226 l_esc_rec_type.escalation_id := l_escalation_id;
227 l_esc_rec_type.escalation_level := p_escalation_level;
228
229 jtf_esc_wf_events_pvt.publish_create_esc
230 (p_esc_rec => l_esc_rec_type);
231
232 EXCEPTION when others then
233 null;
234 END;
235 --End BES enh 2660883
236
237 EXCEPTION
238 WHEN fnd_api.g_exc_unexpected_error
239 THEN
240 ROLLBACK TO create_escalation_pvt;
241 x_return_status := fnd_api.g_ret_sts_unexp_error;
242 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
243 WHEN NO_DATA_FOUND
244 THEN
245 ROLLBACK TO create_escalation_pvt;
246 x_return_status := fnd_api.g_ret_sts_unexp_error;
247 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
248 WHEN OTHERS
249 THEN
250 ROLLBACK TO create_escalation_pvt;
251 x_return_status := fnd_api.g_ret_sts_unexp_error;
252
253 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
254 THEN
255 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
256 END IF;
257
258 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
259 END;
260
261 PROCEDURE update_escalation (
262 p_api_version IN NUMBER,
263 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
264 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
265 p_object_version_number IN OUT NOCOPY NUMBER,
266 p_escalation_id IN NUMBER DEFAULT fnd_api.g_miss_num,
267 p_escalation_number IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
268 p_escalation_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
269 p_description IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
270 p_escalation_status_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
271 p_escalation_status_id IN NUMBER DEFAULT fnd_api.g_miss_num,
272 p_open_date IN DATE DEFAULT fnd_api.g_miss_date,
273 p_close_date IN DATE DEFAULT fnd_api.g_miss_date,
274 p_escalation_priority_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
275 p_escalation_priority_id IN NUMBER DEFAULT fnd_api.g_miss_num,
276 p_owner_id IN NUMBER DEFAULT fnd_api.g_miss_num,
277 p_escalation_owner_type_code IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
278 p_owner_territory_id IN NUMBER DEFAULT fnd_api.g_miss_num,
279 p_assigned_by_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
280 p_assigned_by_id IN NUMBER DEFAULT fnd_api.g_miss_num,
281 p_customer_number IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
282 p_customer_id IN NUMBER DEFAULT fnd_api.g_miss_num,
283 p_cust_account_number IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
284 p_cust_account_id IN NUMBER DEFAULT fnd_api.g_miss_num,
285 p_address_id IN NUMBER DEFAULT fnd_api.g_miss_num,
286 p_address_number IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
287 p_target_date IN DATE DEFAULT fnd_api.g_miss_date,
288 /* p_timezone_id IN NUMBER DEFAULT fnd_api.g_miss_num,
289 p_timezone_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,*/
290 p_reason_code IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
291 p_private_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
292 p_publish_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
293 p_workflow_process_id IN NUMBER DEFAULT fnd_api.g_miss_num,
294 p_escalation_level IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
295 x_return_status OUT NOCOPY VARCHAR2,
296 x_msg_count OUT NOCOPY NUMBER,
297 x_msg_data OUT NOCOPY VARCHAR2,
298 p_attribute1 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
299 p_attribute2 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
300 p_attribute3 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
301 p_attribute4 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
302 p_attribute5 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
303 p_attribute6 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
304 p_attribute7 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
305 p_attribute8 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
306 p_attribute9 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
307 p_attribute10 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
308 p_attribute11 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
309 p_attribute12 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
310 p_attribute13 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
311 p_attribute14 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
312 p_attribute15 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
313 p_attribute_category IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char
314 )
315 IS
316 l_api_version CONSTANT NUMBER := 1.0;
317 l_api_name CONSTANT VARCHAR2(30)
318 := 'UPDATE_ESCALATION';
319 l_escalation_id jtf_tasks_b.task_id%TYPE := p_escalation_id ;
320 l_escalation_number jtf_tasks_b.task_number%TYPE := p_escalation_number ;
321 l_description jtf_tasks_tl.description%TYPE := p_description;
322 l_escalation_name jtf_tasks_tl.task_name%TYPE
323 := p_escalation_name;
324 l_escalation_status_name jtf_task_statuses_tl.name%TYPE
325 := p_escalation_status_name;
326 l_escalation_status_id jtf_task_statuses_b.task_status_id%TYPE
327 := p_escalation_status_id;
328 l_escalation_priority_name jtf_task_priorities_tl.name%TYPE
329 := p_escalation_priority_name;
330 l_escalation_priority_id jtf_task_priorities_b.task_priority_id%TYPE
331 := p_escalation_priority_id;
332 l_assigned_by_name fnd_user.user_name%TYPE
333 := p_assigned_by_name;
334 l_assigned_by_id NUMBER
335 := p_assigned_by_id;
336 l_customer_id hz_parties.party_id%TYPE;
337 l_customer_number hz_parties.party_number%TYPE;
338 l_cust_account_id hz_cust_accounts.cust_account_id%TYPE;
339 l_cust_account_number hz_cust_accounts.account_number%TYPE;
340 l_address_id hz_party_sites.party_site_id%TYPE;
341 l_address_number hz_party_sites.party_site_number%TYPE;
342 l_owner_id jtf_tasks_b.owner_id%TYPE;
343 l_reason_code jtf_tasks_b.reason_code%TYPE;
344 l_private_flag jtf_tasks_b.private_flag%TYPE;
345 l_publish_flag jtf_tasks_b.publish_flag%TYPE;
346 l_workflow_process_id jtf_tasks_b.workflow_process_id%TYPE;
347 l_owner_type_code jtf_tasks_b.source_object_type_code%TYPE;
348
349 CURSOR c_escalation_update (l_escalation_id IN NUMBER)
350 IS
351 SELECT DECODE (
352 p_escalation_id,
353 fnd_api.g_miss_num,
354 task_id,
355 p_escalation_id
356 ) escalation_id,
357 DECODE (
358 p_escalation_number,
359 fnd_api.g_miss_char,
360 task_number,
361 p_escalation_number
362 ) escalation_number,
363 DECODE (
364 p_escalation_name,
365 fnd_api.g_miss_char,
366 task_name,
367 p_escalation_name
368 ) escalation_name,
369 DECODE (
370 p_description,
371 fnd_api.g_miss_char,
372 description,
373 p_description
374 ) description,
375 DECODE (
376 p_escalation_status_id,
377 fnd_api.g_miss_num,
378 task_status_id,
379 p_escalation_status_id
380 ) escalation_status_id,
381 DECODE (
382 p_escalation_priority_id,
383 fnd_api.g_miss_num,
384 task_priority_id,
385 p_escalation_priority_id
386 ) escalation_priority_id,
387 DECODE (
388 p_owner_id,
389 fnd_api.g_miss_num,
390 owner_id,
391 p_owner_id
392 ) owner_id,
393 DECODE (
394 p_owner_territory_id,
395 fnd_api.g_miss_num,
396 owner_territory_id,
397 p_owner_territory_id
398 ) owner_territory_id,
399 DECODE (
400 p_assigned_by_id,
401 fnd_api.g_miss_num,
402 assigned_by_id,
403 p_assigned_by_id
404 ) assigned_by_id,
405 DECODE (
406 p_customer_id,
407 fnd_api.g_miss_num,
408 customer_id,
409 p_customer_id
410 ) customer_id,
411 DECODE (
412 p_cust_account_id,
413 fnd_api.g_miss_num,
414 cust_account_id,
415 p_cust_account_id
416 ) cust_account_id,
417 DECODE (
418 p_address_id,
419 fnd_api.g_miss_num,
420 address_id,
421 p_address_id
422 ) address_id,
423 DECODE (
424 p_target_date,
425 fnd_api.g_miss_date,
426 planned_end_date,
427 p_target_date
428 ) target_date,
429 DECODE (
430 p_reason_code,
431 fnd_api.g_miss_char,
432 reason_code,
433 p_reason_code
434 ) reason_code,
435 DECODE (
436 p_private_flag,
437 fnd_api.g_miss_char,
438 private_flag,
439 p_private_flag
440 ) private_flag,
441 DECODE (
442 p_publish_flag,
443 fnd_api.g_miss_char,
444 publish_flag,
445 p_publish_flag
446 ) publish_flag,
447 DECODE (
448 p_workflow_process_id,
449 fnd_api.g_miss_num,
450 workflow_process_id,
451 p_workflow_process_id
452 ) workflow_process_id,
453 DECODE (
454 p_escalation_level,
455 fnd_api.g_miss_char,
456 escalation_level,
457 p_escalation_level
458 ) escalation_level,
459 DECODE (
460 p_open_date,
461 fnd_api.g_miss_date,
462 actual_start_date,
463 p_open_date
464 ) open_date,
465 DECODE (
466 p_close_date,
467 fnd_api.g_miss_date,
468 actual_end_date,
469 p_close_date
470 ) close_date
471 FROM jtf_tasks_vl
472 WHERE task_id =
473 l_escalation_id;
474
475 escalation_rec c_escalation_update%ROWTYPE;
476 --Created for BES enh 2660883
477 l_esc_rec_type jtf_ec_pvt.Esc_Rec_type;
478 l_task_audit_id jtf_task_audits_b.TASK_AUDIT_ID%TYPE;
479
480 cursor auditid_cur IS
481 select MAX(TASK_AUDIT_ID)
482 from JTF_TASK_AUDITS_B
483 where TASK_ID = l_escalation_id;
484
485 BEGIN
486
487 SAVEPOINT update_escalation_pvt;
488
489 x_return_status := fnd_api.g_ret_sts_success;
490
491 IF NOT fnd_api.compatible_api_call (
492 l_api_version,
493 p_api_version,
494 l_api_name,
495 g_pkg_name
496 )
497 THEN
498 RAISE fnd_api.g_exc_unexpected_error;
499 END IF;
500
501 IF fnd_api.to_boolean (p_init_msg_list)
502 THEN
503 fnd_msg_pub.initialize;
504 END IF;
505
506
507 -----
508 ----- Validate Escalation
509 -----
510 IF ( l_escalation_id = fnd_api.g_miss_num
511 AND l_escalation_number = fnd_api.g_miss_char)
512 THEN
513 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_TASK');
514 fnd_msg_pub.add;
515 x_return_status := fnd_api.g_ret_sts_unexp_error;
516 RAISE fnd_api.g_exc_unexpected_error;
517 ELSE
518 SELECT DECODE (l_escalation_id, fnd_api.g_miss_num, NULL, l_escalation_id)
519 INTO
520 l_escalation_id
521 FROM dual;
522 SELECT DECODE (
523 l_escalation_number,
524 fnd_api.g_miss_char, NULL,
525 l_escalation_number
526 )
527 INTO
528 l_escalation_number
529 FROM dual;
530 jtf_task_utl.validate_task (
531 p_task_id => l_escalation_id,
532 p_task_number => l_escalation_number,
533 x_task_id => l_escalation_id,
534 x_return_status => x_return_status
535 );
536
537 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
538 THEN
539 x_return_status := fnd_api.g_ret_sts_unexp_error;
540 RAISE fnd_api.g_exc_unexpected_error;
541 END IF;
542
543 IF l_escalation_id IS NULL
544 THEN
545 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_TASK_NUMBER');
546 fnd_message.set_token('P_TASK_NUMBER',l_escalation_number);
547 fnd_msg_pub.add;
548 x_return_status := fnd_api.g_ret_sts_unexp_error;
549 RAISE fnd_api.g_exc_unexpected_error;
550 END IF;
551 END IF;
552
553
554 -----
555 ----- Escalation Name
556 -----
557 IF l_escalation_name IS NULL
558 THEN
559 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_NAME');
560 fnd_msg_pub.add;
561 x_return_status := fnd_api.g_ret_sts_unexp_error;
562 RAISE fnd_api.g_exc_unexpected_error;
563 END IF;
564
565
566 -----
567 ----- Task Description
568 -----
569 l_description := escalation_rec.description;
570 ----
571 ---- Check escalation status.
572 ----
573 OPEN c_escalation_update (l_escalation_id);
574 FETCH c_escalation_update INTO escalation_rec;
575
576 IF c_escalation_update%NOTFOUND
577 THEN
578 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_TASK_ID');
579 fnd_message.set_token('P_TASK_ID', to_char(l_escalation_id));
580 fnd_msg_pub.add;
581 x_return_status := fnd_api.g_ret_sts_unexp_error;
582 RAISE fnd_api.g_exc_unexpected_error;
583 END IF;
584 CLOSE c_escalation_update;
585 -----
586 ----- Task Description
587 -----
588 l_description := escalation_rec.description;
589 ----
590
591
592
593 jtf_tasks_pub.update_task (
594 p_api_version => 1.0,
595 p_init_msg_list => fnd_api.g_false,
596 p_commit => fnd_api.g_false,
597 p_object_version_number => p_object_version_number,
598 p_task_id => l_escalation_id,
599 p_task_name => l_escalation_name,
600 p_task_type_id => 22,
601 p_description => l_description,
602 p_task_status_id => l_escalation_status_id,
603 p_task_priority_id => p_escalation_priority_id,
604 p_task_priority_name => p_escalation_priority_name,
605 -- p_owner_type_code => jtf_ec_pub.g_escalation_owner_type_code,
606 p_owner_type_code => p_escalation_owner_type_code,
607 p_owner_id => p_owner_id,
608 p_owner_territory_id => p_owner_territory_id ,
609 p_assigned_by_id => l_assigned_by_id,
610 p_customer_number => p_customer_number,
611 p_customer_id => p_customer_id,
612 p_cust_account_id => p_cust_account_id,
613 p_cust_account_number => p_cust_account_number,
614 p_address_number => p_address_number,
615 p_address_id => p_address_id,
616 p_planned_start_date => null ,
617 p_planned_end_date => p_target_date,
618 /* p_scheduled_start_date => p_scheduled_start_date,
619 p_scheduled_end_date => p_scheduled_end_date,*/
620 p_actual_start_date => p_open_date,
621 p_actual_end_date => p_close_date ,
622 --- p_timezone_id => l_timezone_id,
623 -- p_source_object_type_code => escalation_code,
624 -- p_source_object_id => l_source_object_id,
625 -- p_source_object_name => l_source_object_name,
626 -- p_duration => l_duration,
627 -- p_duration_uom => l_duration_uom,
628 /* p_planned_effort => l_planned_effort,
629 p_planned_effort_uom => l_planned_effort_uom,
630 p_actual_effort => l_actual_effort,
631 p_actual_effort_uom => l_actual_effort_uom,
632 p_percentage_complete => l_percentage_complete,
633 */
634 p_reason_code => p_reason_code,
635 p_private_flag => l_private_flag,
636 p_publish_flag => l_publish_flag,
637 /* p_restrict_closure_flag => l_restrict_closure_flag,
638 p_multi_booked_flag => l_multi_booked_flag,
639 p_milestone_flag => l_milestone_flag,
640 p_holiday_flag => l_holiday_flag,
641 p_billable_flag => l_billable_flag,
642 p_bound_mode_code => l_bound_mode_code,
643 p_soft_bound_flag => l_soft_bound_flag,
644 */
645 p_workflow_process_id => escalation_rec.workflow_process_id,
646 /* p_notification_flag => l_notification_flag,
647 p_notification_period => l_notification_period,
648 p_notification_period_uom => l_notification_period_uom,
649 */
650 /* p_parent_task_id => l_parent_task_id ,
651 p_alarm_start => l_alarm_start,
652 p_alarm_start_uom => l_alarm_start_uom,
653 p_alarm_on => l_alarm_on,
654 p_alarm_count => l_alarm_count,
655 p_alarm_fired_count => l_alarm_fired_count,
656 p_alarm_interval => l_alarm_interval,
657 p_alarm_interval_uom => l_alarm_interval_uom,
658 p_palm_flag => l_palm_flag,
659 p_wince_flag => l_wince_flag,
660 p_laptop_flag => l_laptop_flag,
661 p_device1_flag => l_device1_flag,
662 p_device2_flag => l_device2_flag,
663 p_device3_flag => l_device3_flag,
664 p_costs => l_costs,
665 p_currency_code => l_currency_code,
666 */
667 p_escalation_level => p_escalation_level,
668 x_return_status => x_return_status,
669 x_msg_count => x_msg_count,
670 x_msg_data => x_msg_data,
671 p_attribute1 => p_attribute1,
672 p_attribute2 => p_attribute2,
673 p_attribute3 => p_attribute3,
674 p_attribute4 => p_attribute4,
675 p_attribute5 => p_attribute5,
676 p_attribute6 => p_attribute6,
677 p_attribute7 => p_attribute7,
678 p_attribute8 => p_attribute8,
679 p_attribute9 => p_attribute9,
680 p_attribute10 => p_attribute10,
681 p_attribute11 => p_attribute11,
682 p_attribute12 => p_attribute12,
683 p_attribute13 => p_attribute13,
684 p_attribute14 => p_attribute14,
685 p_attribute15 => p_attribute15,
686 p_attribute_category => p_attribute_category
687 );
688
689 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
690
691 --Created for BES enh 2660883
692 begin
693
694 OPEN auditid_cur;
695 FETCH auditid_cur INTO l_task_audit_id;
696 CLOSE auditid_cur;
697
698 l_esc_rec_type.escalation_id := l_escalation_id;
699 l_esc_rec_type.task_audit_id := l_task_audit_id;
700
701 jtf_esc_wf_events_pvt.publish_update_esc
702 (p_esc_rec => l_esc_rec_type);
703
704 EXCEPTION when others then
705 null;
706 END;
707 --End BES enh 2660883
708
709 EXCEPTION
710 WHEN fnd_api.g_exc_unexpected_error
711 THEN
712 ROLLBACK TO update_escalation_pvt;
713 x_return_status := fnd_api.g_ret_sts_unexp_error;
714 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
715 WHEN NO_DATA_FOUND
716 THEN
717 ROLLBACK TO update_escalation_pvt;
718 x_return_status := fnd_api.g_ret_sts_unexp_error;
719 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
720 WHEN OTHERS
721 THEN
722 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
723 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
724 fnd_msg_pub.add;
725 ROLLBACK TO update_escalation_pvt;
726 x_return_status := fnd_api.g_ret_sts_unexp_error;
727 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
728 END;
729 /* PROCEDURE lock_escalation (
730 p_api_version IN NUMBER,
731 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
732 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
733 p_escalation_id IN NUMBER,
734 p_object_version_number IN NUMBER,
735 x_return_status OUT VARCHAR2,
736 x_msg_data OUT VARCHAR2,
737 x_msg_count OUT NUMBER
738 );
739 */
740
741 PROCEDURE delete_escalation (
742 p_api_version IN NUMBER,
743 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
744 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
745 p_object_version_number IN NUMBER ,
746 p_escalation_id IN NUMBER DEFAULT NULL,
747 p_escalation_number IN VARCHAR2 DEFAULT NULL,
748 x_return_status OUT NOCOPY VARCHAR2,
749 x_msg_count OUT NOCOPY NUMBER,
750 x_msg_data OUT NOCOPY VARCHAR2
751 ) is
752 l_api_version CONSTANT NUMBER := 1.0;
753 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TASK';
754
755 l_escalation_id jtf_tasks_b.task_id%type := p_escalation_id ;
756 l_escalation_number jtf_tasks_b.task_number%type := p_escalation_number ;
757 --Created for BES enh 2660883
758 l_esc_rec_type jtf_ec_pvt.Esc_Rec_type;
759
760 /* Bug # 3568448 */
761 /*Commenting out the below cursor definition since call to jtf_tasks_pvt.delete_task deletes the reference records */
762 /* CURSOR c_delete_references
763 IS
764 SELECT task_reference_id,object_version_number
765 FROM jtf_task_references_vl
766 WHERE task_id = l_escalation_id; */
767
768 -- Added for Bug # 3568448
769 l_esc_ref_rec jtf_ec_references_pvt.Esc_Ref_rec;
770
771 CURSOR c_ref_orig
772 IS
773 SELECT REFERENCE_CODE, OBJECT_TYPE_CODE, OBJECT_ID, TASK_ID
774 FROM JTF_TASK_REFERENCES_B
775 WHERE task_id = l_escalation_id;
776
777 rec_ref_orig c_ref_orig%ROWTYPE;
778
779 Type Ref_Rec_Data is table of jtf_ec_references_pvt.Esc_Ref_rec index by Binary_integer;
780 ref_recs Ref_Rec_Data;
781
782 l_cnt number := 0;
783 l_cnt1 number := 0;
784 -- End Add
785
786 begin
787 SAVEPOINT delete_escalation_pvt;
788
789 x_return_status := fnd_api.g_ret_sts_success;
790
791 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
792 THEN
793 RAISE fnd_api.g_exc_unexpected_error;
794 END IF;
795
796 IF fnd_api.to_boolean (p_init_msg_list)
797 THEN
798 fnd_msg_pub.initialize;
799 END IF;
800
801 IF ( l_escalation_id IS NULL
802 AND l_escalation_number IS NULL)
803 THEN
804 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_TASK');
805 fnd_msg_pub.add;
806 x_return_status := fnd_api.g_ret_sts_unexp_error;
807 RAISE fnd_api.g_exc_unexpected_error;
808 ELSE
809 jtf_task_utl.validate_task (
810 p_task_id => l_escalation_id,
811 p_task_number => l_escalation_number,
812 x_task_id => l_escalation_id,
813 x_return_status => x_return_status
814 );
815
816 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
817 THEN
818 x_return_status := fnd_api.g_ret_sts_unexp_error;
819 RAISE fnd_api.g_exc_unexpected_error;
820 END IF;
821 END IF;
822
823 -- Added for Bug # 3568448
824 /*-- This will fetch all the reference data that is required for triggering BES.
825 -- The code is added since, a call to jtf_tasks_pvt.delete_task will delete references and hence the
826 -- reference data will not be available after the call either to delete_escalation_reference or to
827 -- fire the Business Event.*/
828 for dr in c_ref_orig
829 loop
830 ref_recs(l_cnt).task_reference_id := l_escalation_id;
831 ref_recs(l_cnt).object_type_code := dr.object_type_code;
832 ref_recs(l_cnt).reference_code := dr.reference_code;
833 ref_recs(l_cnt).object_id := dr.object_id;
834 ref_recs(l_cnt).task_id := dr.task_id;
835 l_cnt := l_cnt + 1;
836 end loop;
837 -- End Add
838
839
840 jtf_tasks_pvt.delete_task (
841 p_api_version => 1.0,
842 p_init_msg_list => fnd_api.g_false,
843 p_commit => fnd_api.g_false,
844 p_object_version_number => p_object_version_number,
845 p_task_id => l_escalation_id,
846 p_delete_future_recurrences => fnd_api.g_false,
847 x_return_status => x_return_status,
848 x_msg_count => x_msg_count,
849 x_msg_data => x_msg_data
850 );
851
852 -- Code added to check x_return_status, since it was missing in the original code
853 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
854 THEN
855
856 x_return_status := fnd_api.g_ret_sts_unexp_error;
857 RAISE fnd_api.g_exc_unexpected_error;
858 END IF;
859 -- End Add
860
861 -- Added for Bug # 3568448
862 -- The below code will trigger the Business event for every reference that is deleted when deleting
863 -- an Escalation
864
865 Begin
866
867
868 if l_cnt > 0 then
869 while (l_cnt1 <= l_cnt)
870 loop
871 jtf_esc_wf_events_pvt.publish_delete_escRef
872 (p_esc_ref_rec => ref_recs(l_cnt1));
873 l_cnt1 := l_cnt1 + 1;
874 end loop;
875 end if;
876
877 EXCEPTION when others then
878 null;
879
880 End;
881
882 -- End Add
883
884
885 /* Bug # 3568448 */
886 /*Commenting out the below code since call to jtf_tasks_pvt.delete_task deletes the reference records also
887 -- delete references....
888
889 ---------------------------
890 FOR b IN c_delete_references
891 LOOP
892 jtf_ec_references_pvt.delete_references (
893 p_api_version => 1.0,
894 p_init_msg_list => fnd_api.g_false,
895 p_commit => fnd_api.g_false,
896 p_object_version_number => b.object_version_number,
897 p_escalation_reference_id => b.task_reference_id,
898 x_return_status => x_return_status,
899 x_msg_count => x_msg_count,
900 x_msg_data => x_msg_data
901 );
902
903 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
904 THEN
905
906 x_return_status := fnd_api.g_ret_sts_unexp_error;
907 RAISE fnd_api.g_exc_unexpected_error;
908 END IF;
909 END LOOP; */
910
911 IF fnd_api.to_boolean (p_commit)
912 THEN
913 COMMIT WORK;
914 END IF;
915
916 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
917
918 --Created for BES enh 2660883
919 begin
920
921 l_esc_rec_type.escalation_id := l_escalation_id;
922
923 jtf_esc_wf_events_pvt.publish_delete_esc
924 (p_esc_rec => l_esc_rec_type);
925
926 EXCEPTION when others then
927 null;
928 END;
929 --End BES enh 2660883
930
931
932 EXCEPTION
933 WHEN fnd_api.g_exc_unexpected_error
934 THEN
935 ROLLBACK TO delete_escalation_pvt;
936 x_return_status := fnd_api.g_ret_sts_unexp_error;
937 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
938 WHEN OTHERS
939 THEN
940 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
941 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
942 fnd_msg_pub.add ;
943 ROLLBACK TO delete_escalation_pvt;
944 x_return_status := fnd_api.g_ret_sts_unexp_error;
945 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
946 END;
947
948 PROCEDURE GET_ASSIGN_ESC_TERR_RESOURCES(
949 p_api_version IN NUMBER,
950 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
951 p_source_object_id IN NUMBER,
952 p_source_object_type IN VARCHAR2,
953 x_owner_type_code OUT NOCOPY VARCHAR2,
954 x_resource_id OUT NOCOPY NUMBER
955 )
956 IS
957 l_return VARCHAR2(10);
958 l_msg_data VARCHAR2(2000);
959 l_msg_data1 VARCHAR2(2000);
960 l_msg_count NUMBER;
961 l_recnum NUMBER;
962 l_tbl JTF_ASSIGN_PUB.Escalations_tbl_type;
963 l_res_tbl JTF_ASSIGN_PUB.AssignResources_tbl_type;
964
965 l_primary_contact_flag VARCHAR2(2000);
966 l_count NUMBER := 0;
967 BEGIN
968 l_tbl(1).source_object_id:= p_source_object_id ;
969 l_tbl(1).SOURCE_OBJECT_TYPE:= p_source_object_type;
970
971 JTF_ASSIGN_PUB.GET_ASSIGN_ESC_RESOURCES
972 (
973 p_api_version => 1.0,
974 p_init_msg_list => 'T' ,
975 p_resource_type => null ,
976 p_role => null ,
977 p_no_of_resources => null ,
978 p_auto_select_flag => null,
979 p_effort_duration => null ,
980 p_effort_uom => null ,
981 p_start_date => null ,
982 p_end_date => null ,
983 p_territory_flag => 'Y' ,
984 p_calendar_flag => 'N',
985 p_web_availability_flag => 'N' ,
986 p_esc_tbl => l_tbl,
987 p_business_process_id => null,
988 p_business_process_date => null,
989 x_assign_resources_tbl => l_res_tbl,
990 x_return_status =>l_return,
991 x_msg_count =>l_msg_count,
992 x_msg_data =>l_msg_data
993 );
994
995 l_recnum := l_res_tbl.first;
996 while l_recnum <= l_res_tbl.last
997 loop
998 l_primary_contact_flag := l_res_tbl(l_recnum).primary_contact_flag;
999 IF l_primary_contact_flag = 'Y' THEN
1000 x_owner_type_code := l_res_tbl(l_recnum).resource_type;
1001 x_resource_id := l_res_tbl(l_recnum).resource_id;
1002 l_count := l_count +1 ;
1003 END IF;
1004 EXIT WHEN l_count > 0;
1005 l_recnum := l_res_tbl.next(l_recnum);
1006 end loop;
1007
1008 END GET_ASSIGN_ESC_TERR_RESOURCES;
1009
1010 END;