[Home] [Help]
PACKAGE BODY: APPS.JTF_EC_PUB
Source
1 PACKAGE BODY JTF_EC_PUB as
2 /* $Header: jtfpecb.pls 115.11 2003/01/13 13:46:15 siyappan ship $ */
3 --/**==================================================================*
4 --| Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA |
5 --| All rights reserved. |
6 --+====================================================================+
7 -- Start of comments
8 -- API name : JTF_EC_PUB package body
9 -- Type : Public.
10 -- Function : Creates/Updates/Deletes an escalation document and related reference documents,
11 -- contacts and contact details (contact points).
12 -- Pre-reqs : None.
13 -- File : jtfpecb.pls
14 --
15 -- History : 11-SEP-00 tivanov Created
16 --
17 -- Standard parameters: Please review the package specification
18 -- for the list with parameters.
19 --
20 -- Version : 1.0
21 --
22 --------------------------------------------------------------------------------------------
23 --
24 -- End of comments
25
26 --==========================================================================================
27 -- Private Procedures
28 --==========================================================================================
29 PROCEDURE Create_Esc_Note(p_notes_rec IN Notes_Rec_Type,
30 p_source_object_id IN NUMBER,
31 p_source_object_code IN VARCHAR2,
32 p_user_id IN NUMBER,
33 p_login_id IN NUMBER,
34 x_msg_count OUT NOCOPY NUMBER,
35 x_msg_data OUT NOCOPY VARCHAR2,
36 x_note_id OUT NOCOPY NUMBER,
37 x_return_status OUT NOCOPY VARCHAR2) Is
38
39 l_api_name VARCHAR2(30) := 'Create_Esc_Note';
40 l_api_version NUMBER := 1.0;
41 l_notes_rec Notes_Rec_Type := p_notes_rec;
42 l_user_id NUMBER := p_user_id;
43 l_login_id NUMBER := p_login_id;
44 l_return_status VARCHAR2(1) := 'x';
45 l_note_id jtf_notes_b.jtf_note_id%TYPE;
46 l_note_context_id NUMBER;
47
48 BEGIN
49
50 SAVEPOINT Create_Esc_Note;
51
52 -- Initialize API return status to success
53
54 x_return_status := FND_API.G_RET_STS_SUCCESS;
55
56 -------------------------------------------------------------------------------------------
57 -- Convert the missing fields to NULLs
58 -------------------------------------------------------------------------------------------
59 jtf_ec_util.Conv_Miss_Char(l_notes_rec.note);
60 jtf_ec_util.Conv_Miss_Char(l_notes_rec.note_detail);
61 jtf_ec_util.Conv_Miss_Char(l_notes_rec.note_type);
62 jtf_ec_util.Conv_Miss_Char(l_notes_rec.note_context_type_01);
63 jtf_ec_util.Conv_Miss_Num(l_notes_rec.note_context_type_id_01);
64 jtf_ec_util.Conv_Miss_Char(l_notes_rec.note_context_type_02);
65 jtf_ec_util.Conv_Miss_Num(l_notes_rec.note_context_type_id_02);
66 jtf_ec_util.Conv_Miss_Char(l_notes_rec.note_context_type_03);
67 jtf_ec_util.Conv_Miss_Num(l_notes_rec.note_context_type_id_03);
68
69 if l_notes_rec.note_status = fnd_api.g_miss_char then
70 l_notes_rec.note_status := 'I';
71 end if;
72
73 if (l_notes_rec.note IS NOT NULL) then
74
75 jtf_notes_pub.create_note(
76 p_api_version => l_api_version,
77 p_init_msg_list => fnd_api.g_false,
78 p_commit => fnd_api.g_false,
79 x_return_status => l_return_status,
80 x_msg_count => x_msg_count,
81 x_msg_data => x_msg_data,
82 p_source_object_id => p_source_object_id,
83 p_source_object_code => p_source_object_code,
84 p_notes => l_notes_rec.note,
85 p_notes_detail => l_notes_rec.note_detail,
86 p_note_type => l_notes_rec.note_type,
87 p_note_status => l_notes_rec.note_status,
88 p_entered_by => l_user_id,
89 p_entered_date => SYSDATE,
90 p_created_by => l_user_id,
91 p_creation_date => SYSDATE,
92 p_last_updated_by => l_user_id,
93 p_last_update_date => SYSDATE,
94 p_last_update_login => l_login_id,
95 x_jtf_note_id => l_note_id
96 );
97
98 x_note_id := l_note_id;
99
100 if (l_return_status = fnd_api.g_ret_sts_error) then
101 raise fnd_api.g_exc_error;
102 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
103 raise fnd_api.g_exc_unexpected_error;
104 end if;
105
106 end if;
107
108 if (l_notes_rec.note_context_type_01 is NOT NULL)
109 AND
110 (l_notes_rec.note_context_type_id_01 is NOT NULL)
111 then
112
113 jtf_notes_pub.create_note_context(
114 x_return_status => l_return_status,
115 p_creation_date => SYSDATE,
116 p_last_updated_by => l_user_id,
117 p_last_update_date => SYSDATE,
118 p_jtf_note_id => l_note_id,
119 p_note_context_type => l_notes_rec.note_context_type_01,
120 p_note_context_type_id => l_notes_rec.note_context_type_id_01,
121 p_last_update_login => l_login_id,
122 x_note_context_id => l_note_context_id
123 );
124
125 if (l_return_status = fnd_api.g_ret_sts_error) then
126 raise fnd_api.g_exc_error;
127 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
128 raise fnd_api.g_exc_unexpected_error;
129 end if;
130
131 end if;
132
133 if (l_notes_rec.note_context_type_02 is NOT NULL)
134 AND
135 (l_notes_rec.note_context_type_id_02 is NOT NULL)
136 then
137
138 jtf_notes_pub.create_note_context(
139 x_return_status => l_return_status,
140 p_creation_date => SYSDATE,
141 p_last_updated_by => l_user_id,
142 p_last_update_date => SYSDATE,
143 p_jtf_note_id => l_note_id,
144 p_note_context_type => l_notes_rec.note_context_type_02,
145 p_note_context_type_id => l_notes_rec.note_context_type_id_02,
146 p_last_update_login => l_login_id,
147 x_note_context_id => l_note_context_id
148 );
149
150 if (l_return_status = fnd_api.g_ret_sts_error) then
151 raise fnd_api.g_exc_error;
152 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
153 raise fnd_api.g_exc_unexpected_error;
154 end if;
155
156 end if;
157
158 if (l_notes_rec.note_context_type_03 IS NOT NULL)
159 AND
160 (l_notes_rec.note_context_type_id_03 IS NOT NULL)
161 then
162
163 jtf_notes_pub.create_note_context(
164 x_return_status => l_return_status,
165 p_creation_date => SYSDATE,
166 p_last_updated_by => l_user_id,
167 p_last_update_date => SYSDATE,
168 p_jtf_note_id => l_note_id,
169 p_note_context_type => l_notes_rec.note_context_type_03,
170 p_note_context_type_id => l_notes_rec.note_context_type_id_03,
171 p_last_update_login => l_login_id,
172 x_note_context_id => l_note_context_id
173 );
174
175 if (l_return_status = fnd_api.g_ret_sts_error) then
176 raise fnd_api.g_exc_error;
177 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
178 raise fnd_api.g_exc_unexpected_error;
179 end if;
180
181 end if;
182
183 EXCEPTION
184
185 WHEN fnd_api.g_exc_error
186 THEN
187 ROLLBACK TO Create_Esc_Note;
188 x_return_status := fnd_api.g_ret_sts_error;
189 fnd_msg_pub.count_and_get (
190 p_count => x_msg_count,
191 p_data => x_msg_data
192 );
193
194 WHEN fnd_api.g_exc_unexpected_error
195 THEN
196
197 ROLLBACK TO Create_Esc_Note;
198 x_return_status := fnd_api.g_ret_sts_unexp_error;
199 fnd_msg_pub.count_and_get (
200 p_count => x_msg_count,
201 p_data => x_msg_data
202 );
203
204 WHEN OTHERS
205 THEN
206 ROLLBACK TO Create_Esc_Note;
207 x_return_status := fnd_api.g_ret_sts_unexp_error;
208
209 if FND_MSG_PUB.Check_Msg_Level
210 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
211 then
212 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
213 end if;
214
215 fnd_msg_pub.count_and_get ( p_count => x_msg_count,
216 p_data => x_msg_data);
217
218 END Create_Esc_Note;
219
220 PROCEDURE Create_Esc_Contacts( p_esc_contacts_rec IN Esc_Contacts_Rec_Type,
221 -- p_cont_points IN Esc_Cont_Points_Tbl_Type,
222 p_esc_id IN NUMBER,
223 p_esc_number IN VARCHAR2,
224 x_msg_count OUT NOCOPY NUMBER,
225 x_msg_data OUT NOCOPY VARCHAR2,
226 x_esc_contact_id OUT NOCOPY NUMBER,
227 x_return_status OUT NOCOPY VARCHAR2) Is
228
229 l_api_version NUMBER := 1.0;
230 l_esc_contacts Esc_Contacts_Rec_Type := p_esc_contacts_rec;
231 -- l_cont_points Esc_Cont_Points_Tbl_Type := p_cont_points;
232 l_escalation_id jtf_tasks_b.task_id%TYPE := p_esc_id;
233 l_escalation_number jtf_tasks_b.task_number%TYPE := p_esc_number;
234 l_return_status varchar2(1) := 'x';
235 l_api_name varchar2(30) := 'Create_Esc_Contacts';
236 l_task_phone_id jtf_task_phones.task_phone_id%TYPE := NULL;
237 l_escalation_contact_id jtf_task_references_b.task_reference_id%TYPE;
238
239 BEGIN
240
241 SAVEPOINT Create_Esc_Contacts;
242
243 -- Initialize API return status to success
244
245 x_return_status := FND_API.G_RET_STS_SUCCESS;
246
247 -------------------------------------------------------------------------------------------
248 -- Convert the missing fields to NULLs
249 -------------------------------------------------------------------------------------------
250 jtf_ec_util.Conv_Miss_Num(l_esc_contacts.contact_id);
251 -- jtf_ec_util.Conv_Miss_Num(l_esc_contacts.task_contact_id);
252 -- jtf_ec_util.Conv_Miss_Num(l_esc_contacts.object_version_number);
253 jtf_ec_util.Conv_Miss_Char(l_esc_contacts.contact_type_code);
254 jtf_ec_util.Conv_Miss_Char(l_esc_contacts.escalation_requester_flag);
255 jtf_ec_util.Conv_Miss_Char(l_esc_contacts.escalation_notify_flag);
256 jtf_ec_util.Conv_Miss_Char(l_esc_contacts.attribute1);
257 jtf_ec_util.Conv_Miss_Char(l_esc_contacts.attribute2);
258 jtf_ec_util.Conv_Miss_Char(l_esc_contacts.attribute3);
259 jtf_ec_util.Conv_Miss_Char(l_esc_contacts.attribute4);
260 jtf_ec_util.Conv_Miss_Char(l_esc_contacts.attribute5);
261 jtf_ec_util.Conv_Miss_Char(l_esc_contacts.attribute6);
262 jtf_ec_util.Conv_Miss_Char(l_esc_contacts.attribute7);
263 jtf_ec_util.Conv_Miss_Char(l_esc_contacts.attribute8);
264 jtf_ec_util.Conv_Miss_Char(l_esc_contacts.attribute9);
265 jtf_ec_util.Conv_Miss_Char(l_esc_contacts.attribute10);
266 jtf_ec_util.Conv_Miss_Char(l_esc_contacts.attribute11);
267 jtf_ec_util.Conv_Miss_Char(l_esc_contacts.attribute12);
268 jtf_ec_util.Conv_Miss_Char(l_esc_contacts.attribute13);
269 jtf_ec_util.Conv_Miss_Char(l_esc_contacts.attribute14);
270 jtf_ec_util.Conv_Miss_Char(l_esc_contacts.attribute15);
271 jtf_ec_util.Conv_Miss_Char(l_esc_contacts.attribute_category);
272
273 if l_esc_contacts.escalation_notify_flag is NULL then
274 l_esc_contacts.escalation_notify_flag := 'N';
275 end if;
276
277 if l_esc_contacts.escalation_requester_flag is NULL then
278 l_esc_contacts.escalation_requester_flag := 'N';
279 end if;
280
281 -- check whether this is a duplicate contact. Skip it if it is.
282
283 if jtf_ec_util.Contact_Duplicated(l_esc_contacts.contact_id,
284 l_esc_contacts.contact_type_code,
285 l_escalation_id) = FALSE then
286
287 JTF_EC_CONTACTS_PVT.CREATE_ESCALATION_CONTACTS(
288 P_API_VERSION => l_api_version,
289 P_INIT_MSG_LIST => fnd_api.g_false,
290 P_COMMIT => fnd_api.g_false,
291 P_ESCALATION_ID => l_escalation_id,
292 P_ESCALATION_NUMBER => l_escalation_number,
293 P_CONTACT_ID => l_esc_contacts.contact_id,
294 P_CONTACT_TYPE_CODE => l_esc_contacts.contact_type_code,
295 P_ESCALATION_NOTIFY_FLAG => l_esc_contacts.escalation_notify_flag,
296 P_ESCALATION_REQUESTER_FLAG => l_esc_contacts.escalation_requester_flag,
297 X_ESCALATION_CONTACT_ID => l_escalation_contact_id,
298 X_RETURN_STATUS => l_return_status,
299 X_MSG_DATA => x_msg_data,
300 X_MSG_COUNT => x_msg_count,
301 p_attribute1 =>l_esc_contacts.attribute1,
302 p_attribute2 =>l_esc_contacts.attribute2,
303 p_attribute3 =>l_esc_contacts.attribute3,
304 p_attribute4 =>l_esc_contacts.attribute4,
305 p_attribute5 =>l_esc_contacts.attribute5,
306 p_attribute6 =>l_esc_contacts.attribute6,
307 p_attribute7 =>l_esc_contacts.attribute7,
308 p_attribute8 =>l_esc_contacts.attribute8,
309 p_attribute9 =>l_esc_contacts.attribute9,
310 p_attribute10 =>l_esc_contacts.attribute10,
311 p_attribute11 =>l_esc_contacts.attribute11,
312 p_attribute12 =>l_esc_contacts.attribute12,
313 p_attribute13 =>l_esc_contacts.attribute13,
314 p_attribute14 =>l_esc_contacts.attribute14,
315 p_attribute15 =>l_esc_contacts.attribute15,
316 p_attribute_category =>l_esc_contacts.attribute_category
317 );
318
319 if (l_return_status = fnd_api.g_ret_sts_error) then
320 raise fnd_api.g_exc_error;
321 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
322 raise fnd_api.g_exc_unexpected_error;
323 end if;
324
325 else
326
327 fnd_message.set_name('JTF', 'JTF_API_ALL_DUPLICATE_VALUE');
328 fnd_message.set_token('API_NAME', l_api_name);
329 fnd_message.set_token('DUPLICATE_VAL_PARAM', 'Contact ID: ' || to_char(l_esc_contacts.contact_id));
330 fnd_msg_pub.add;
331 raise fnd_api.g_exc_error;
332
333 end if;
334
335 EXCEPTION
336
337 WHEN fnd_api.g_exc_error
338 THEN
339 ROLLBACK TO Create_Esc_Contacts;
340 x_return_status := fnd_api.g_ret_sts_error;
341 fnd_msg_pub.count_and_get (
342 p_count => x_msg_count,
343 p_data => x_msg_data
344 );
345
346 WHEN fnd_api.g_exc_unexpected_error
347 THEN
348
349 ROLLBACK TO Create_Esc_Contacts;
350 x_return_status := fnd_api.g_ret_sts_unexp_error;
351 fnd_msg_pub.count_and_get (
352 p_count => x_msg_count,
353 p_data => x_msg_data
354 );
355
356 WHEN OTHERS
357 THEN
358 ROLLBACK TO Create_Esc_Contacts;
359 x_return_status := fnd_api.g_ret_sts_unexp_error;
360 if FND_MSG_PUB.Check_Msg_Level
361 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
362 then
363 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
364 end if;
365
366 fnd_msg_pub.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
367
368 END Create_Esc_Contacts;
369
370 -------------------------------------------------------------------------------------------------------
371 -- Create_Esc_Reference - private procedure
372 -------------------------------------------------------------------------------------------------------
373
374 PROCEDURE Create_Esc_Reference( p_reference_documents_rec IN Esc_Ref_Docs_Rec_Type,
375 p_esc_id IN NUMBER,
376 p_esc_number IN VARCHAR2,
377 x_msg_count OUT NOCOPY NUMBER,
378 x_msg_data OUT NOCOPY VARCHAR2,
379 x_esc_reference_id OUT NOCOPY NUMBER,
380 x_return_status OUT NOCOPY VARCHAR2) Is
381
382 l_reference_documents Esc_Ref_Docs_Rec_Type := p_reference_documents_rec;
383 l_escalation_id jtf_tasks_b.task_id%TYPE := p_esc_id;
384 l_escalation_number jtf_tasks_b.task_number%TYPE := p_esc_number;
385 l_return_status varchar2(1) := 'x';
386 l_task_ref_id jtf_task_references_b.task_reference_id%TYPE := NULL; --esc id where ref doc is escalated
387 l_api_name varchar2(30) := 'Create_Esc_Reference';
388 l_escalation_reference_id jtf_task_references_b.task_reference_id%TYPE;
389
390 BEGIN
391
392 SAVEPOINT Create_Esc_Reference;
393
394 -- Initialize API return status to success
395
396 x_return_status := FND_API.G_RET_STS_SUCCESS;
397
398 l_return_status := 'x';
399
400 jtf_ec_util.Conv_Miss_Char(l_reference_documents.object_type_code);
401 jtf_ec_util.Conv_Miss_Char(l_reference_documents.object_name);
402 jtf_ec_util.Conv_Miss_Num(l_reference_documents.object_id);
403 jtf_ec_util.Conv_Miss_Char(l_reference_documents.reference_code);
404 jtf_ec_util.Conv_Miss_Char(l_reference_documents.attribute1);
405 jtf_ec_util.Conv_Miss_Char(l_reference_documents.attribute2);
406 jtf_ec_util.Conv_Miss_Char(l_reference_documents.attribute3);
407 jtf_ec_util.Conv_Miss_Char(l_reference_documents.attribute4);
408 jtf_ec_util.Conv_Miss_Char(l_reference_documents.attribute5);
409 jtf_ec_util.Conv_Miss_Char(l_reference_documents.attribute6);
410 jtf_ec_util.Conv_Miss_Char(l_reference_documents.attribute7);
411 jtf_ec_util.Conv_Miss_Char(l_reference_documents.attribute8);
412 jtf_ec_util.Conv_Miss_Char(l_reference_documents.attribute9);
413 jtf_ec_util.Conv_Miss_Char(l_reference_documents.attribute10);
414 jtf_ec_util.Conv_Miss_Char(l_reference_documents.attribute11);
415 jtf_ec_util.Conv_Miss_Char(l_reference_documents.attribute12);
416 jtf_ec_util.Conv_Miss_Char(l_reference_documents.attribute13);
417 jtf_ec_util.Conv_Miss_Char(l_reference_documents.attribute14);
418 jtf_ec_util.Conv_Miss_Char(l_reference_documents.attribute15);
419 jtf_ec_util.Conv_Miss_Char(l_reference_documents.attribute_category);
420
421 if (l_reference_documents.object_id is not NULL or
422 l_reference_documents.object_name is not NULL)
423 and l_reference_documents.object_type_code is not NULL
424 then
425 if jtf_ec_util.Validate_Lookup('JTF_TASK_REFERENCE_CODES',
426 l_reference_documents.reference_code) = FALSE
427 then
428 jtf_ec_util.Add_Invalid_Argument_Msg(l_api_name, l_reference_documents.reference_code, 'reference_code');
429 raise fnd_api.g_exc_error;
430 end if;
431
432 -- check whether the same document is passed twice. If yes - skip it;
433
434 if jtf_ec_util.Reference_Duplicated(l_reference_documents.object_type_code,
435 l_reference_documents.object_id,
436 l_reference_documents.object_name,
437 l_reference_documents.reference_code,
438 l_escalation_id) = FALSE then
439
440 if l_reference_documents.reference_code = 'ESC' then
441
442 if jtf_ec_util.check_if_escalated(l_reference_documents.object_type_code,
443 l_reference_documents.object_id,
444 l_reference_documents.object_name,
445 l_task_ref_id) = TRUE then
446
447 fnd_message.set_name ('JTF', 'JTF_TK_ESC_DOC_EXIST');
448 fnd_message.set_token('OBJECT_TYPE',l_reference_documents.object_type_code);
449 fnd_message.set_token('OBJECT_NUMBER',l_reference_documents.object_name);
450 fnd_msg_pub.Add;
451 raise fnd_api.g_exc_error;
452 end if;
453 end if;
454
455 JTF_EC_REFERENCES_PVT.CREATE_REFERENCES(
456 P_API_VERSION => 1.0,
457 P_INIT_MSG_LIST => fnd_api.g_true,
458 P_COMMIT => fnd_api.g_false,
459 P_ESCALATION_ID => l_escalation_id,
460 P_ESCALATION_NUMBER => l_escalation_number,
461 P_OBJECT_TYPE_CODE => l_reference_documents.object_type_code,
462 P_OBJECT_NAME => l_reference_documents.object_name,
463 P_OBJECT_ID => l_reference_documents.object_id,
464 P_OBJECT_DETAILS => NULL,
465 P_REFERENCE_CODE => l_reference_documents.reference_code,
466 P_USAGE => NULL, -- l_reference_documents.usage,
467 X_RETURN_STATUS => l_return_status,
468 X_MSG_DATA => x_msg_data,
469 X_MSG_COUNT => x_msg_count,
470 X_ESCALATION_REFERENCE_ID=>l_escalation_reference_id,
471 p_attribute1 => l_reference_documents.attribute1,
472 p_attribute2 => l_reference_documents.attribute2,
473 p_attribute3 => l_reference_documents.attribute3,
474 p_attribute4 => l_reference_documents.attribute4,
475 p_attribute5 => l_reference_documents.attribute5,
476 p_attribute6 => l_reference_documents.attribute6,
477 p_attribute7 => l_reference_documents.attribute7,
478 p_attribute8 => l_reference_documents.attribute8,
479 p_attribute9 => l_reference_documents.attribute9,
480 p_attribute10 => l_reference_documents.attribute10,
481 p_attribute11 => l_reference_documents.attribute11,
482 p_attribute12 => l_reference_documents.attribute12,
483 p_attribute13 => l_reference_documents.attribute13,
484 p_attribute14 => l_reference_documents.attribute14,
485 p_attribute15 => l_reference_documents.attribute15,
486 p_attribute_category => l_reference_documents.attribute_category);
487
488 if (l_return_status = fnd_api.g_ret_sts_error) then
489 raise fnd_api.g_exc_error;
490 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
491 raise fnd_api.g_exc_unexpected_error;
492 end if;
493 end if;
494 else
495
496 -- in case the object_id/name or object_code are not passed
497
498 jtf_ec_util.Add_Invalid_Argument_Msg(l_api_name, 'NULL', 'object_id');
499 jtf_ec_util.Add_Invalid_Argument_Msg(l_api_name, 'NULL', 'object_name');
500 jtf_ec_util.Add_Invalid_Argument_Msg(l_api_name, 'NULL', 'object_type_code');
501 raise fnd_api.g_exc_error;
502
503 end if;
504
505 EXCEPTION
506
507 WHEN fnd_api.g_exc_error
508 THEN
509 ROLLBACK TO Create_Esc_Reference;
510 x_return_status := fnd_api.g_ret_sts_error;
511 fnd_msg_pub.count_and_get (
512 p_count => x_msg_count,
513 p_data => x_msg_data
514 );
515
516 WHEN fnd_api.g_exc_unexpected_error
517 THEN
518
519 ROLLBACK TO Create_Esc_Reference;
520 x_return_status := fnd_api.g_ret_sts_unexp_error;
521 fnd_msg_pub.count_and_get ( p_count => x_msg_count,
522 p_data => x_msg_data);
523
524 WHEN OTHERS
525 THEN
526 ROLLBACK TO Create_Esc_Reference;
527 x_return_status := fnd_api.g_ret_sts_unexp_error;
528
529 if FND_MSG_PUB.Check_Msg_Level
530 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
531 then
532 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
533 end if;
534
535 fnd_msg_pub.count_and_get ( p_count => x_msg_count,
536 p_data => x_msg_data);
537 END Create_Esc_Reference;
538
539 PROCEDURE CREATE_ESCALATION (
540 p_api_version IN NUMBER,
541 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
542 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
543 x_return_status OUT NOCOPY VARCHAR2,
544 x_msg_count OUT NOCOPY NUMBER,
545 x_msg_data OUT NOCOPY VARCHAR2,
546 p_resp_appl_id IN NUMBER,
547 p_resp_id IN NUMBER,
548 p_user_id IN NUMBER,
549 p_login_id IN NUMBER,
550 p_esc_id IN jtf_tasks_b.task_id%TYPE :=NULL,
551 -- p_esc_number IN jtf_tasks_b.task_number%TYPE :=NULL,
552 p_esc_record IN Esc_Rec_Type,
553 p_reference_documents IN Esc_Ref_Docs_Tbl_Type,
554 p_esc_contacts IN Esc_Contacts_Tbl_Type,
555 p_cont_points IN Esc_Cont_Points_Tbl_Type,
556 p_notes IN Notes_Tbl_Type,
557 x_esc_id OUT NOCOPY NUMBER,
558 x_esc_number OUT NOCOPY NUMBER,
559 x_workflow_process_id OUT NOCOPY VARCHAR2) Is
560
561 l_api_version CONSTANT NUMBER := 1.0;
562 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_ESCALATION';
563 l_esc_record Esc_Rec_Type := p_esc_record;
564 l_reference_documents Esc_Ref_Docs_Tbl_Type := p_reference_documents;
565 l_esc_contacts Esc_Contacts_Tbl_Type := p_esc_contacts;
566 l_cont_points Esc_Cont_Points_Tbl_Type := p_cont_points;
567 l_escalation_id jtf_tasks_b.task_id%TYPE := p_esc_id;
568 l_escalation_number jtf_tasks_b.task_number%TYPE;
569 l_return_status varchar2(1) := 'x';
570 l_owner_id jtf_tasks_b.owner_id%TYPE:=NULL;
571 l_req_count NUMBER := 0;
572 l_escalation_contact_id jtf_task_contacts.task_contact_id%TYPE;
573 l_escalation_reference_id jtf_task_references_b.task_reference_id%TYPE;
574 l_temp VARCHAR2(10);
575 l_esc_status_id jtf_tasks_b.task_status_id%TYPE;
576 l_task_phone_id jtf_task_phones.task_phone_id%TYPE := NULL;
577 l_user_id NUMBER := p_user_id;
578 l_resp_appl_id NUMBER := p_resp_appl_id;
579 l_resp_id NUMBER := p_resp_id;
580 l_login_id NUMBER := p_login_id;
581 l_notes Notes_Tbl_Type := p_notes;
582 l_note_id NUMBER;
583 l_note_context_id NUMBER;
584 l_notif_not_sent VARCHAR2(2000);
585 l_wf_process_id NUMBER;
586
587 cursor esc_owner(p_resource_id NUMBER) is
588 SELECT 'x'
589 from jtf_rs_emp_dtls_vl
590 where resource_id = p_resource_id
591 and (sysdate between nvl(start_date_active, sysdate)
592 and nvl(end_date_active, sysdate));
593
594 cursor task_number(p_task_id NUMBER) is
595 SELECT task_number
596 FROM jtf_tasks_b
597 WHERE task_id = p_task_id;
598
599 BEGIN
600
601 SAVEPOINT Create_Escalation;
602
603 -- Standard call to check for call compatibility.
604
605 IF NOT FND_API.Compatible_API_Call ( l_api_version,
606 p_api_version,
607 l_api_name,
608 G_PKG_NAME)
609 THEN
610 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
611 END IF;
612
613 -- Check p_init_msg_list
614
615 IF FND_API.To_Boolean( p_init_msg_list ) THEN
616 FND_MSG_PUB.initialize;
617 END IF;
618
619 -- Initialize API return status to success
620
621 x_return_status := FND_API.G_RET_STS_SUCCESS;
622
623 ----------------------------------------------------------------------------------------------------
624 -- standard customer user hook pre-processing
625 ----------------------------------------------------------------------------------------------------
626 if ( jtf_usr_hks.ok_to_execute( g_pkg_name, l_api_name, 'B', 'C' ) ) then
627 jtf_ec_cuhk.Create_Escalation_Pre(
628 p_esc_id => l_escalation_id,
629 p_esc_record => l_esc_record,
630 p_reference_documents => l_reference_documents,
631 p_esc_contacts => l_esc_contacts,
632 p_cont_points => l_cont_points,
633 p_notes => l_notes,
634 x_return_status => l_return_status,
635 x_msg_count => x_msg_count,
636 x_msg_data => x_msg_data);
637
638 if l_return_status = fnd_api.g_ret_sts_error then
639 raise fnd_api.g_exc_error;
640 elsif l_return_status = fnd_api.g_ret_sts_unexp_error then
641 raise fnd_api.g_exc_unexpected_error;
642 end if;
643
644 end if;
645 ----------------------------------------------------------------------------------------------------
646 -- standard verticle industry user hook pre-processing
647 ----------------------------------------------------------------------------------------------------
648 if ( jtf_usr_hks.ok_to_execute( g_pkg_name, l_api_name, 'B', 'V' ) ) then
649 jtf_ec_vuhk.Create_Escalation_Pre(
650 p_esc_id => l_escalation_id,
651 p_esc_record => l_esc_record,
652 p_reference_documents => l_reference_documents,
653 p_esc_contacts => l_esc_contacts,
654 p_cont_points => l_cont_points,
655 p_notes => l_notes,
656 x_return_status => l_return_status,
657 x_msg_count => x_msg_count,
658 x_msg_data => x_msg_data);
659
660 if l_return_status = fnd_api.g_ret_sts_error then
661 raise fnd_api.g_exc_error;
662 elsif l_return_status = fnd_api.g_ret_sts_unexp_error then
663 raise fnd_api.g_exc_unexpected_error;
664 end if;
665
666 end if;
667
668 -------------------------------------------------------------------------
669 -- parameters validation
670 -------------------------------------------------------------------------
671 -- validate p_user
672 -------------------------------------------------------------------------
673
674 if l_user_id is NOT NULL then
675 jtf_ec_util.Validate_Who_Info( l_api_name, l_user_id,
676 l_login_id, l_return_status);
677 if (l_return_status = fnd_api.g_ret_sts_error) then
678 raise fnd_api.g_exc_error;
679 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
680 raise fnd_api.g_exc_unexpected_error;
681 end if;
682 else
683 jtf_ec_util.Add_Invalid_Argument_Msg(l_api_name, 'NULL' ,'user_id');
684 raise fnd_api.g_exc_error;
685 end if;
686
687 if l_esc_record.esc_name is NULL or l_esc_record.esc_name = fnd_api.g_miss_char then
688 l_esc_record.esc_name := g_escalation_name;
689 end if;
690
691 if l_esc_record.esc_open_date is NULL or l_esc_record.esc_open_date = fnd_api.g_miss_date then
692 l_esc_record.esc_open_date := sysdate;
693 end if;
694
695 -------------------------------------------------------------------------
696 -- validate owner -- check whether the owner is passed and if it is of correct type
697 -- the rest of the validation is in the tasks api
698 -------------------------------------------------------------------------
699
700 l_return_status := 'x';
701
702 if l_esc_record.esc_owner_id is NULL or l_esc_record.esc_owner_id = fnd_api.g_miss_num then
703 l_esc_record.esc_owner_id := FND_PROFILE.Value_Specific('JTF_EC_DEFAULT_OWNER',
704 l_user_id, l_resp_id, l_resp_appl_id);
705 end if;
706
707 --Bug 2723761
708 --jtf_ec_util.validate_owner(l_esc_record.esc_owner_id, g_escalation_owner_type_code, l_return_status);
709 if l_esc_record.esc_owner_type_code is NULL OR
710 l_esc_record.esc_owner_type_code = fnd_api.g_miss_char then
711 l_esc_record.esc_owner_type_code := g_escalation_owner_type_code;
712 end if;
713
714 jtf_ec_util.validate_owner( l_esc_record.esc_owner_id,
715 l_esc_record.esc_owner_type_code,
716 l_return_status);
717 if l_return_status <> fnd_api.g_ret_sts_success then
718 raise fnd_api.g_exc_error;
719 end if;
720
721 -------------------------------------------------------------------------
722 -- validate status
723 -- if the status is not passed it will be defaulted from a profile option
724 -------------------------------------------------------------------------
725
726 if (l_esc_record.status_id = fnd_api.g_miss_num or l_esc_record.status_id is NULL)
727 and (l_esc_record.status_name = fnd_api.g_miss_char or l_esc_record.status_name is NULL)
728 then
729 l_esc_record.status_id := FND_PROFILE.Value_Specific('JTF_EC_DEFAULT_STATUS',
730 l_user_id, l_resp_id, l_resp_appl_id);
731 end if;
732
733 l_return_status := 'x';
734
735 jtf_ec_util.Validate_Esc_Status(p_esc_status_id => l_esc_record.status_id,
736 p_esc_status_name => l_esc_record.status_name,
737 x_return_status => l_return_status,
738 x_esc_status_id => l_esc_status_id);
739
740 if l_return_status <> fnd_api.g_ret_sts_success then
741 raise fnd_api.g_exc_error;
742 end if;
743
744 -------------------------------------------------------------------------
745 -- validate escalation reason
746 -- if the reason code is not passed it will be defaulted from a profile option
747 -------------------------------------------------------------------------
748
749 if (l_esc_record.reason_code = fnd_api.g_miss_char or l_esc_record.reason_code is NULL)
750 then
751 l_esc_record.reason_code := FND_PROFILE.Value_Specific('JTF_EC_DEFAULT_REASON_CODE',
752 l_user_id,
753 l_resp_id,
754 l_resp_appl_id);
755 end if;
756
757 if jtf_ec_util.Validate_Lookup('JTF_TASK_REASON_CODES',
758 l_esc_record.reason_code) = FALSE
759 then
760 jtf_ec_util.Add_Invalid_Argument_Msg(l_api_name, l_esc_record.reason_code, 'reason_code');
761 raise fnd_api.g_exc_error;
762 end if;
763
764 -------------------------------------------------------------------------
765 -- validate escalation level
766 -- if the level is not passed it will be defaulted from a profile option
767 -------------------------------------------------------------------------
768
769 if (l_esc_record.escalation_level = fnd_api.g_miss_char or l_esc_record.escalation_level is NULL)
770 then
771 l_esc_record.escalation_level := FND_PROFILE.Value_Specific('JTF_EC_DEFAULT_ESCALATION_LEVEL',
772 l_user_id, l_resp_id,
773 l_resp_appl_id);
774 end if;
775
776 if jtf_ec_util.Validate_Lookup('JTF_TASK_ESC_LEVEL',
777 l_esc_record.escalation_level ) = FALSE
778 then
779 jtf_ec_util.Add_Invalid_Argument_Msg(l_api_name, l_esc_record.escalation_level, 'escalation_level');
780 raise fnd_api.g_exc_error;
781 end if;
782
783 -------------------------------------------------------------------------
784 -- Create escalation header
785 -------------------------------------------------------------------------
786
787 -------------------------------------------------------------------------
788 -- convert the missing fields to NULL
789 -------------------------------------------------------------------------
790
791 jtf_ec_util.Conv_Miss_Char(l_esc_record.esc_name);
792 jtf_ec_util.Conv_Miss_Char(l_esc_record.esc_description);
793 jtf_ec_util.Conv_Miss_Char(l_esc_record.status_name);
794 jtf_ec_util.Conv_Miss_Num(l_esc_record.status_id);
795 jtf_ec_util.Conv_Miss_Date(l_esc_record.esc_open_date);
796 jtf_ec_util.Conv_Miss_Num(l_esc_record.esc_owner_id);
797 jtf_ec_util.Conv_Miss_Char(l_esc_record.esc_owner_type_code); --bug 2723761
798 jtf_ec_util.Conv_Miss_Num(l_esc_record.customer_id);
799 jtf_ec_util.Conv_Miss_Char(l_esc_record.customer_number);
800 jtf_ec_util.Conv_Miss_Num(l_esc_record.cust_account_id);
801 jtf_ec_util.Conv_Miss_Char(l_esc_record.cust_account_number);
802 jtf_ec_util.Conv_Miss_Num(l_esc_record.cust_address_id);
803 jtf_ec_util.Conv_Miss_Char(l_esc_record.cust_address_number);
804 jtf_ec_util.Conv_Miss_Date(l_esc_record.esc_target_date);
805 jtf_ec_util.Conv_Miss_Char(l_esc_record.reason_code);
806 jtf_ec_util.Conv_Miss_Char(l_esc_record.escalation_level);
807 jtf_ec_util.Conv_Miss_Char(l_esc_record.attribute1);
808 jtf_ec_util.Conv_Miss_Char(l_esc_record.attribute2);
809 jtf_ec_util.Conv_Miss_Char(l_esc_record.attribute3);
810 jtf_ec_util.Conv_Miss_Char(l_esc_record.attribute4);
811 jtf_ec_util.Conv_Miss_Char(l_esc_record.attribute5);
812 jtf_ec_util.Conv_Miss_Char(l_esc_record.attribute6);
813 jtf_ec_util.Conv_Miss_Char(l_esc_record.attribute7);
814 jtf_ec_util.Conv_Miss_Char(l_esc_record.attribute8);
815 jtf_ec_util.Conv_Miss_Char(l_esc_record.attribute9);
816 jtf_ec_util.Conv_Miss_Char(l_esc_record.attribute10);
817 jtf_ec_util.Conv_Miss_Char(l_esc_record.attribute11);
818 jtf_ec_util.Conv_Miss_Char(l_esc_record.attribute12);
819 jtf_ec_util.Conv_Miss_Char(l_esc_record.attribute13);
820 jtf_ec_util.Conv_Miss_Char(l_esc_record.attribute14);
821 jtf_ec_util.Conv_Miss_Char(l_esc_record.attribute15);
822 jtf_ec_util.Conv_Miss_Char(l_esc_record.attribute_category);
823
824 -- Bug 2650521
825 JTF_EC_PVT.CREATE_ESCALATION (
826 P_API_VERSION => l_api_version,
827 P_INIT_MSG_LIST => fnd_api.g_false,
828 P_COMMIT => fnd_api.g_false,
829 p_esc_id => l_escalation_id,
830 P_ESCALATION_NAME => l_esc_record.esc_name,
831 P_DESCRIPTION => l_esc_record.esc_description,
832 P_ESCALATION_STATUS_NAME => l_esc_record.status_name,
833 P_ESCALATION_STATUS_ID =>l_esc_status_id ,
834 P_ESCALATION_PRIORITY_NAME =>NULL,
835 P_ESCALATION_PRIORITY_ID =>NULL,
836 P_OPEN_DATE =>l_esc_record.esc_open_date,
837 P_CLOSE_DATE =>NULL,
838 P_ESCALATION_OWNER_ID =>l_esc_record.esc_owner_id,
839 P_ESCALATION_OWNER_TYPE_CODE => l_esc_record.esc_owner_type_code, --bug 2723761
840 -- P_OWNER_TERRITORY_ID =>l_esc_record.esc_territory_id,
841 P_ASSIGNED_BY_NAME =>NULL,
842 P_ASSIGNED_BY_ID =>NULL,
843 P_CUSTOMER_NUMBER =>l_esc_record.customer_number,
844 P_CUSTOMER_ID =>l_esc_record.customer_id,
845 P_CUST_ACCOUNT_NUMBER =>l_esc_record.cust_account_number,
846 P_CUST_ACCOUNT_ID =>l_esc_record.cust_account_id,
847 P_ADDRESS_ID =>l_esc_record.cust_address_id,
848 P_ADDRESS_NUMBER =>l_esc_record.cust_address_number,
849 P_TARGET_DATE =>l_esc_record.esc_target_date,
850 P_REASON_CODE =>l_esc_record.reason_code,
851 P_PRIVATE_FLAG =>NULL, --l_esc_record.private_flag,
852 P_PUBLISH_FLAG =>NULL, --l_esc_record.publish_flag,
853 P_WORKFLOW_PROCESS_ID =>NULL,
854 P_ESCALATION_LEVEL =>l_esc_record.escalation_level,
855 X_RETURN_STATUS =>l_return_status,
856 X_MSG_COUNT =>x_msg_count,
857 X_MSG_DATA =>x_msg_data,
858 X_ESCALATION_ID =>l_escalation_id,
859 p_attribute1 =>l_esc_record.attribute1,
860 p_attribute2 =>l_esc_record.attribute2,
861 p_attribute3 =>l_esc_record.attribute3,
862 p_attribute4 =>l_esc_record.attribute4,
863 p_attribute5 =>l_esc_record.attribute5,
864 p_attribute6 =>l_esc_record.attribute6,
865 p_attribute7 =>l_esc_record.attribute7,
866 p_attribute8 =>l_esc_record.attribute8,
867 p_attribute9 =>l_esc_record.attribute9,
868 p_attribute10 =>l_esc_record.attribute10,
869 p_attribute11 =>l_esc_record.attribute11,
870 p_attribute12 =>l_esc_record.attribute12,
871 p_attribute13 =>l_esc_record.attribute13,
872 p_attribute14 =>l_esc_record.attribute14,
873 p_attribute15 =>l_esc_record.attribute15,
874 p_attribute_category =>l_esc_record.attribute_category
875 );
876
877 if (l_return_status = fnd_api.g_ret_sts_error) then
878 raise fnd_api.g_exc_error;
879 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
880 raise fnd_api.g_exc_unexpected_error;
881 end if;
882
883 open task_number(l_escalation_id);
884 fetch task_number into l_escalation_number;
885 if task_number%NOTFOUND then
886 close task_number;
887 RAISE fnd_api.g_exc_unexpected_error;
888 end if;
889 close task_number;
890
891 for j in 1..l_esc_contacts.COUNT loop
892
893 l_return_status := 'x';
894
895 if l_esc_contacts(j).action_code = 'I' then
896
897 Create_Esc_Contacts(p_esc_contacts_rec => l_esc_contacts(j),
898 -- p_cont_points => l_cont_points,
899 p_esc_id => l_escalation_id,
900 p_esc_number => l_escalation_number,
901 x_msg_count => x_msg_count,
902 x_msg_data => x_msg_data,
903 x_esc_contact_id => l_escalation_contact_id,
904 x_return_status => l_return_status);
905
906 if (l_return_status = fnd_api.g_ret_sts_error) then
907 raise fnd_api.g_exc_error;
908 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
909 raise fnd_api.g_exc_unexpected_error;
910 end if;
911
912 end if;
913
914 end loop;
915
916 -------------------------------------------------------------------------
917 -- validate Requester
918 -------------------------------------------------------------------------
919
920 l_return_status := 'x';
921
922 jtf_ec_util.Validate_Requester(l_escalation_id, l_return_status);
923
924 if l_return_status <> fnd_api.g_ret_sts_success then
925 raise fnd_api.g_exc_error;
926 end if;
927
928 -------------------------------------------------------------------------
929 -- ** Insert the contact points
930 -------------------------------------------------------------------------
931
932 for j in 1..l_cont_points.COUNT loop
933
934 if l_cont_points(j).action_code = 'I' then
935
936 l_return_status := 'x';
937
938 -- check whether this is a valid contact for this escalation. Also get the task contact id.
939
940 jtf_ec_util.Validate_Contact_id(l_cont_points(j).contact_id,
941 l_cont_points(j).contact_type_code,
942 l_escalation_id,
943 l_escalation_contact_id,
944 l_return_status);
945
946 if (l_return_status = fnd_api.g_ret_sts_error) then
947 raise fnd_api.g_exc_error;
948 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
949 raise fnd_api.g_exc_unexpected_error;
950 end if;
951
952 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute1);
953 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute2);
954 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute3);
955 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute4);
956 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute5);
957 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute6);
958 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute7);
959 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute8);
960 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute9);
961 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute10);
962 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute11);
963 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute12);
964 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute13);
965 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute14);
966 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute15);
967 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute_category);
968
969 -- insert the phone_id (contact_point) for this contact
970
971 Jtf_Task_Phones_Pub.create_task_phones (
972 p_api_version => l_api_version,
973 p_init_msg_list => fnd_api.g_false,
974 p_commit => fnd_api.g_false,
975 p_task_contact_id => l_escalation_contact_id,
976 p_phone_id => l_cont_points(j).contact_point_id,
977 x_task_phone_id => l_task_phone_id,
978 x_return_status => l_return_status,
979 x_msg_data => x_msg_data,
980 x_msg_count => x_msg_count,
981 p_attribute1 => l_cont_points(j).attribute1,
982 p_attribute2 => l_cont_points(j).attribute2,
983 p_attribute3 => l_cont_points(j).attribute3,
984 p_attribute4 => l_cont_points(j).attribute4,
985 p_attribute5 => l_cont_points(j).attribute5,
986 p_attribute6 => l_cont_points(j).attribute6,
987 p_attribute7 => l_cont_points(j).attribute7,
988 p_attribute8 => l_cont_points(j).attribute8,
989 p_attribute9 => l_cont_points(j).attribute9,
990 p_attribute10 => l_cont_points(j).attribute10,
991 p_attribute11 => l_cont_points(j).attribute11,
992 p_attribute12 => l_cont_points(j).attribute12,
993 p_attribute13 => l_cont_points(j).attribute13,
994 p_attribute14 => l_cont_points(j).attribute14,
995 p_attribute15 => l_cont_points(j).attribute15,
996 p_attribute_category => l_cont_points(j).attribute_category
997 );
998
999 if (l_return_status = fnd_api.g_ret_sts_error) then
1000 raise fnd_api.g_exc_error;
1001 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1002 raise fnd_api.g_exc_unexpected_error;
1003 end if;
1004
1005 end if;
1006
1007 end loop;
1008
1009 -------------------------------------------------------------------------
1010 -- Insert the Reference Documents
1011 -------------------------------------------------------------------------
1012
1013 for j in 1..l_reference_documents.COUNT loop
1014
1015 ---------------------------------------------------------------------------------
1016 -- check reference code
1017 -- if the reference code is not passed it will be defaulted from a profile option
1018 ----------------------------------------------------------------------------------
1019
1020 if l_reference_documents(j).action_code = 'I' then
1021
1022 if (l_reference_documents(j).reference_code = fnd_api.g_miss_char
1023 OR l_reference_documents(j).reference_code is NULL)
1024 then
1025 l_reference_documents(j).reference_code := FND_PROFILE.Value_Specific('JTF_EC_DEFAULT_REF_TYPE',
1026 l_user_id, l_resp_id, l_resp_appl_id);
1027 end if;
1028
1029 Create_Esc_Reference( l_reference_documents(j),
1030 l_escalation_id,
1031 l_escalation_number,
1032 x_msg_count,
1033 x_msg_data,
1034 l_escalation_reference_id,
1035 l_return_status);
1036
1037 if (l_return_status = fnd_api.g_ret_sts_error) then
1038 raise fnd_api.g_exc_error;
1039 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1040 raise fnd_api.g_exc_unexpected_error;
1041 end if;
1042
1043 end if;
1044
1045 end loop;
1046
1047 -------------------------------------------------------------------------------------------
1048 -- Create Notes
1049 -------------------------------------------------------------------------------------------
1050
1051 for j in 1..l_notes.COUNT loop
1052
1053 l_return_status := 'x';
1054
1055 Create_Esc_Note( l_notes(j),
1056 l_escalation_id,
1057 g_escalation_code,
1058 l_user_id,
1059 l_login_id,
1060 x_msg_count,
1061 x_msg_data,
1062 l_note_id,
1063 l_return_status);
1064
1065 if (l_return_status = fnd_api.g_ret_sts_error) then
1066 raise fnd_api.g_exc_error;
1067 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1068 raise fnd_api.g_exc_unexpected_error;
1069 end if;
1070
1071 end loop;
1072
1073 -------------------------------------------------------------------------------------
1074 -- Send Notifications. Call to Workflow. JTFEC WF item.
1075 -------------------------------------------------------------------------------------
1076
1077 JTF_EC_WORKFLOW_PKG.Start_Resc_Workflow(
1078 P_API_VERSION =>l_api_version,
1079 P_INIT_MSG_LIST =>fnd_api.g_false,
1080 P_COMMIT =>fnd_api.g_false,
1081 X_RETURN_STATUS =>l_return_status,
1082 X_MSG_COUNT =>x_msg_count,
1083 X_MSG_DATA =>x_msg_data,
1084 P_TASK_ID =>l_escalation_id,
1085 P_DOC_CREATED =>'Y',
1086 x_notif_not_sent => l_notif_not_sent,
1087 X_WF_PROCESS_ID =>l_wf_process_id);
1088
1089 if (l_return_status = fnd_api.g_ret_sts_error) then
1090 raise fnd_api.g_exc_error;
1091 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1092 raise fnd_api.g_exc_unexpected_error;
1093 end if;
1094
1095 -- Insert the wf_process_id
1096
1097 update jtf_tasks_b
1098 set workflow_process_id = l_wf_process_id
1099 where task_id = l_escalation_id;
1100
1101 ----------------------------------------------------------------------------------------------------
1102 -- standard verticle industry user hook post-processing
1103 ----------------------------------------------------------------------------------------------------
1104 if ( jtf_usr_hks.ok_to_execute( g_pkg_name, l_api_name, 'A', 'V' ) ) then
1105 jtf_ec_vuhk.Create_Escalation_Post(
1106 p_esc_id => l_escalation_id,
1107 p_esc_record => l_esc_record,
1108 p_reference_documents => l_reference_documents,
1109 p_esc_contacts => l_esc_contacts,
1110 p_cont_points => l_cont_points,
1111 p_notes => l_notes,
1112 x_return_status => l_return_status,
1113 x_msg_count => x_msg_count,
1114 x_msg_data => x_msg_data);
1115
1116 if l_return_status = fnd_api.g_ret_sts_error then
1117 raise fnd_api.g_exc_error;
1118 elsif l_return_status = fnd_api.g_ret_sts_unexp_error then
1119 raise fnd_api.g_exc_unexpected_error;
1120 end if;
1121
1122 end if;
1123
1124 ----------------------------------------------------------------------------------------------------
1125 -- standard customer user hook post-processing
1126 ----------------------------------------------------------------------------------------------------
1127 if ( jtf_usr_hks.ok_to_execute( g_pkg_name, l_api_name, 'A', 'C' ) ) then
1128 jtf_ec_cuhk.Create_Escalation_Post(
1129 p_esc_id => l_escalation_id,
1130 p_esc_record => l_esc_record,
1131 p_reference_documents => l_reference_documents,
1132 p_esc_contacts => l_esc_contacts,
1133 p_cont_points => l_cont_points,
1134 p_notes => l_notes,
1135 x_return_status => l_return_status,
1136 x_msg_count => x_msg_count,
1137 x_msg_data => x_msg_data);
1138
1139 if l_return_status = fnd_api.g_ret_sts_error then
1140 raise fnd_api.g_exc_error;
1141 elsif l_return_status = fnd_api.g_ret_sts_unexp_error then
1142 raise fnd_api.g_exc_unexpected_error;
1143 end if;
1144
1145 end if;
1146
1147 -- Standard check of p_commit.
1148 x_esc_id := l_escalation_id;
1149 x_esc_number := l_escalation_number;
1150 x_workflow_process_id := l_wf_process_id;
1151
1152 IF FND_API.To_Boolean( p_commit ) THEN
1153 COMMIT WORK;
1154 END IF;
1155
1156 fnd_msg_pub.count_and_get
1157 ( p_count => x_msg_count,
1158 p_data => x_msg_data
1159 );
1160
1161 EXCEPTION
1162
1163 WHEN fnd_api.g_exc_error
1164 THEN
1165 ROLLBACK TO Create_Escalation;
1166 x_return_status := fnd_api.g_ret_sts_error;
1167
1168 fnd_msg_pub.count_and_get (
1169 p_count => x_msg_count,
1170 p_data => x_msg_data
1171 );
1172
1173 WHEN fnd_api.g_exc_unexpected_error
1174 THEN
1175
1176 ROLLBACK TO Create_Escalation;
1177 x_return_status := fnd_api.g_ret_sts_unexp_error;
1178 fnd_msg_pub.count_and_get ( p_count => x_msg_count,
1179 p_data => x_msg_data);
1180
1181 WHEN OTHERS
1182 THEN
1183 ROLLBACK TO Create_Escalation;
1184 x_return_status := fnd_api.g_ret_sts_unexp_error;
1185
1186 if FND_MSG_PUB.Check_Msg_Level
1187 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1188 then
1189 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1190 end if;
1191
1192 fnd_msg_pub.count_and_get ( p_count => x_msg_count,
1193 p_data => x_msg_data);
1194 END CREATE_ESCALATION;
1195
1196 PROCEDURE UPDATE_ESCALATION (
1197 p_api_version IN NUMBER,
1198 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
1199 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
1200 x_return_status OUT NOCOPY VARCHAR2,
1201 x_msg_count OUT NOCOPY NUMBER,
1202 x_msg_data OUT NOCOPY VARCHAR2,
1203 p_resp_appl_id IN NUMBER := NULL,
1204 p_resp_id IN NUMBER := NULL,
1205 p_user_id IN NUMBER := NULL, -- used for last updated by
1206 p_login_id IN NUMBER := NULL,
1207 p_esc_id IN jtf_tasks_b.task_id%TYPE :=NULL,
1208 p_esc_number IN jtf_tasks_b.task_number%TYPE :=NULL,
1209 p_object_version IN NUMBER,
1210 p_esc_record IN Esc_Rec_Type,
1211 p_reference_documents IN Esc_Ref_Docs_Tbl_Type,
1212 p_esc_contacts IN Esc_Contacts_Tbl_Type,
1213 p_cont_points IN Esc_Cont_Points_Tbl_Type,
1214 p_notes IN Notes_Tbl_Type,
1215 x_object_version_number OUT NOCOPY NUMBER,
1216 x_workflow_process_id OUT NOCOPY VARCHAR2) Is
1217
1218 l_api_version CONSTANT NUMBER := 1.0;
1219 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ESCALATION';
1220 l_esc_record Esc_Rec_Type := p_esc_record;
1221 l_reference_documents Esc_Ref_Docs_Tbl_Type := p_reference_documents;
1222 l_esc_contacts Esc_Contacts_Tbl_Type := p_esc_contacts;
1223 l_cont_points Esc_Cont_Points_Tbl_Type := p_cont_points;
1224 l_notes Notes_Tbl_Type := p_notes;
1225 l_note_id jtf_notes_b.jtf_note_id%TYPE;
1226 l_escalation_id jtf_tasks_b.task_id%TYPE := NULL;
1227 l_escalation_number jtf_tasks_b.task_number%TYPE := NULL;
1228 l_esc_status_id jtf_tasks_b.task_status_id%TYPE;
1229 l_object_version_number jtf_tasks_b.object_version_number%TYPE := p_object_version;
1230 l_user_id NUMBER := p_user_id;
1231 l_resp_appl_id NUMBER := p_resp_appl_id;
1232 l_resp_id NUMBER := p_resp_id;
1233 l_login_id NUMBER := p_login_id;
1234 l_return_status varchar2(1) := 'x';
1235 l_closed_flag varchar2(1) := 'N';
1236 l_esc_close_date jtf_tasks_b.actual_end_date%TYPE;
1237 l_esc_task_id jtf_tasks_b.task_id%TYPE := NULL; --esc id where ref doc is escalated
1238 l_task_ref_id jtf_task_references_b.task_reference_id%TYPE;
1239 l_escalation_reference_id jtf_task_references_b.task_reference_id%TYPE;
1240 l_escalation_contact_id jtf_task_references_b.task_reference_id%TYPE;
1241 l_task_phone_id jtf_task_phones.task_phone_id%TYPE := NULL;
1242 l_owner_changed VARCHAR2(1) := 'N';
1243 l_level_changed VARCHAR2(1) := 'N';
1244 l_status_changed VARCHAR2(1) := 'N';
1245 l_target_date_changed VARCHAR2(1) := 'N';
1246 l_old_owner_id jtf_tasks_b.owner_id%TYPE;
1247 l_old_status_id jtf_tasks_b.task_status_id%TYPE;
1248 l_old_escalation_level jtf_tasks_b.escalation_level%TYPE;
1249 l_old_target_date jtf_tasks_b.planned_end_date%TYPE;
1250 l_notif_not_sent VARCHAR2(2000);
1251 l_wf_process_id NUMBER;
1252 l_object_name jtf_task_references_b.object_name%TYPE;
1253 l_object_id jtf_task_references_b.object_id%TYPE;
1254 l_object_type_code jtf_task_references_b.object_type_code%TYPE;
1255
1256 cursor c_cont_phone_id (p_task_contact_id NUMBER) is
1257 select task_phone_id, object_version_number
1258 from jtf_task_phones
1259 where task_contact_id = p_task_contact_id;
1260
1261 cursor c_get_old_esc_data(p_escalation_id NUMBER) Is
1262 select owner_id, task_status_id, escalation_level, planned_end_date
1263 from jtf_tasks_b
1264 where task_id = p_escalation_id;
1265
1266 cursor c_get_ref_details(p_task_ref_id NUMBER) Is
1267 Select object_type_code,
1268 object_id,
1269 object_name
1270 from jtf_task_references_b
1271 where task_reference_id = p_task_ref_id;
1272
1273 BEGIN
1274
1275 SAVEPOINT Update_Escalation;
1276
1277 -- Standard call to check for call compatibility.
1278
1279 IF NOT FND_API.Compatible_API_Call ( l_api_version, p_api_version,
1280 l_api_name, G_PKG_NAME)
1281 THEN
1282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1283 END IF;
1284
1285 -- Check p_init_msg_list
1286
1287 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1288 FND_MSG_PUB.initialize;
1289 END IF;
1290
1291 -- Initialize API return status to success
1292
1293 x_return_status := FND_API.G_RET_STS_SUCCESS;
1294
1295 --------------------------------------------------------------------------------------------
1296 -- standard customer user hook pre-processing
1297 --------------------------------------------------------------------------------------------
1298 if ( jtf_usr_hks.ok_to_execute( g_pkg_name, l_api_name, 'B', 'C' ) ) then
1299 jtf_ec_cuhk.Update_Escalation_Pre(
1300 p_esc_id => l_escalation_id,
1301 p_esc_number => l_escalation_number,
1302 p_object_version => l_object_version_number,
1303 p_esc_record => l_esc_record,
1304 p_reference_documents => l_reference_documents,
1305 p_esc_contacts => l_esc_contacts,
1306 p_cont_points => l_cont_points,
1307 p_notes => l_notes,
1308 x_return_status => l_return_status,
1309 x_msg_count => x_msg_count,
1310 x_msg_data => x_msg_data);
1311
1312 if l_return_status = fnd_api.g_ret_sts_error then
1313 raise fnd_api.g_exc_error;
1314 elsif l_return_status = fnd_api.g_ret_sts_unexp_error then
1315 raise fnd_api.g_exc_unexpected_error;
1316 end if;
1317
1318 end if;
1319 --------------------------------------------------------------------------------------------
1320 -- standard verticle industry user hook pre-processing
1321 --------------------------------------------------------------------------------------------
1322 if ( jtf_usr_hks.ok_to_execute( g_pkg_name, l_api_name, 'B', 'V' ) ) then
1323
1324 jtf_ec_vuhk.Update_Escalation_Pre(
1325 p_esc_id => l_escalation_id,
1326 p_esc_number => l_escalation_number,
1327 p_object_version => l_object_version_number,
1328 p_esc_record => l_esc_record,
1329 p_reference_documents => l_reference_documents,
1330 p_esc_contacts => l_esc_contacts,
1331 p_cont_points => l_cont_points,
1332 p_notes => l_notes,
1333 x_return_status => l_return_status,
1334 x_msg_count => x_msg_count,
1335 x_msg_data => x_msg_data);
1336
1337 if l_return_status = fnd_api.g_ret_sts_error then
1338 raise fnd_api.g_exc_error;
1339 elsif l_return_status = fnd_api.g_ret_sts_unexp_error then
1340 raise fnd_api.g_exc_unexpected_error;
1341 end if;
1342
1343 end if;
1344
1345 -------------------------------------------------------------------------
1346 -- parameters validation
1347 -------------------------------------------------------------------------
1348 -- validate p_user
1349 -------------------------------------------------------------------------
1350
1351 if l_user_id is NOT NULL then
1352 jtf_ec_util.Validate_Who_Info( l_api_name, l_user_id,
1353 l_login_id, l_return_status);
1354 if (l_return_status = fnd_api.g_ret_sts_error) then
1355 raise fnd_api.g_exc_error;
1356 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1357 raise fnd_api.g_exc_unexpected_error;
1358 end if;
1359 else
1360 jtf_ec_util.Add_Invalid_Argument_Msg(l_api_name, 'NULL' ,'user_id');
1361 raise fnd_api.g_exc_error;
1362 end if;
1363
1364 -------------------------------------------------------------------------
1365 -- Validate Escalation Task
1366 -- Performs also number to id conversion
1367 -------------------------------------------------------------------------
1368
1369 if p_esc_id <> fnd_api.g_miss_num then
1370 l_escalation_id := p_esc_id;
1371 if p_esc_number <> fnd_api.g_miss_char then
1372 jtf_ec_util.add_param_ignored_msg(l_api_name, 'p_esc_number');
1373 end if;
1374 elsif p_esc_number <> fnd_api.g_miss_char then
1375 l_escalation_number := p_esc_number;
1376 else jtf_ec_util.add_missing_param_msg(l_api_name, 'p_esc_name');
1377 end if;
1378
1379 jtf_ec_util.Validate_Esc_Document(l_escalation_id, l_escalation_number,
1380 l_esc_task_id, -- converted number to id
1381 l_return_status);
1382
1383 if (l_return_status = fnd_api.g_ret_sts_error) then
1384 raise fnd_api.g_exc_error;
1385 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1386 raise fnd_api.g_exc_unexpected_error;
1387 end if;
1388
1389 l_escalation_id := l_esc_task_id;
1390
1391 -------------------------------------------------------------------------
1392 -- parameters validation
1393 --
1394 -- check whether some required parameters are set to NULL
1395 -------------------------------------------------------------------------
1396 -- validate status
1397 -------------------------------------------------------------------------
1398 l_esc_status_id := l_esc_record.status_id;
1399
1400 if (l_esc_record.status_id is NULL)
1401 and (l_esc_record.status_name is NULL)
1402 then
1403 jtf_ec_util.Add_Invalid_Argument_Msg(l_api_name, 'NULL', 'status_id');
1404 jtf_ec_util.Add_Invalid_Argument_Msg(l_api_name, 'NULL', 'status_name');
1405 raise fnd_api.g_exc_error;
1406 end if;
1407
1408 l_return_status := 'x';
1409
1410 if l_esc_record.status_id <> fnd_api.g_miss_num
1411 or l_esc_record.status_name <> fnd_api.g_miss_char then
1412
1413 jtf_ec_util.Validate_Esc_Status(p_esc_status_id => l_esc_record.status_id,
1414 p_esc_status_name => l_esc_record.status_name,
1415 x_return_status => l_return_status,
1416 x_esc_status_id => l_esc_status_id);
1417
1418 if l_return_status <> fnd_api.g_ret_sts_success then
1419 raise fnd_api.g_exc_error;
1420 end if;
1421
1422 end if;
1423
1424 ----------------------------------------------------------------------------------------
1425 -- validate level. It is required parameter
1426 ----------------------------------------------------------------------------------------
1427
1428 if l_esc_record.escalation_level is NULL then
1429 jtf_ec_util.Add_Invalid_Argument_Msg(l_api_name, l_esc_record.escalation_level, 'escalation_level');
1430 raise fnd_api.g_exc_error;
1431 elsif l_esc_record.escalation_level <> fnd_api.g_miss_char then
1432
1433 if jtf_ec_util.Validate_Lookup('JTF_TASK_ESC_LEVEL',
1434 l_esc_record.escalation_level ) = FALSE
1435 then
1436 jtf_ec_util.Add_Invalid_Argument_Msg(l_api_name, l_esc_record.escalation_level, 'escalation_level');
1437 raise fnd_api.g_exc_error;
1438 end if;
1439
1440 end if;
1441
1442 ----------------------------------------------------------------------------------------
1443 -- Set the close date to sysdate if it is not passed and the Status is updated to 'Closed'
1444 ----------------------------------------------------------------------------------------
1445
1446 jtf_ec_util.Check_Completed_Status(l_esc_status_id, l_escalation_id,
1447 l_esc_record.escalation_level,
1448 l_closed_flag, l_return_status);
1449
1450 if (l_return_status = fnd_api.g_ret_sts_error) then
1451 raise fnd_api.g_exc_error;
1452 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1453 raise fnd_api.g_exc_unexpected_error;
1454 elsif l_closed_flag = 'Y' then
1455 l_esc_close_date := sysdate;
1456 end if;
1457
1458 --Bug 2723761
1459 -------------------------------------------------------------------------
1460 -- validate Escalation Owner
1461 -------------------------------------------------------------------------
1462 if l_esc_record.esc_owner_type_code is NULL OR
1463 l_esc_record.esc_owner_type_code = fnd_api.g_miss_char then
1464 l_esc_record.esc_owner_type_code := g_escalation_owner_type_code;
1465 end if;
1466
1467 jtf_ec_util.validate_owner( l_esc_record.esc_owner_id,
1468 l_esc_record.esc_owner_type_code,
1469 l_return_status);
1470 if l_return_status <> fnd_api.g_ret_sts_success then
1471 raise fnd_api.g_exc_error;
1472 end if;
1473 --End
1474
1475 ----------------------------------------------------------------------------------------
1476 -- validate reason code
1477 ----------------------------------------------------------------------------------------
1478
1479 if (l_esc_record.reason_code <> fnd_api.g_miss_char)
1480 then
1481 if jtf_ec_util.Validate_Lookup('JTF_TASK_REASON_CODES',
1482 l_esc_record.reason_code) = FALSE
1483 then
1484 jtf_ec_util.Add_Invalid_Argument_Msg(l_api_name, l_esc_record.reason_code, 'reason_code');
1485 raise fnd_api.g_exc_error;
1486 end if;
1487 end if;
1488
1489 open c_get_old_esc_data(l_escalation_id);
1490 fetch c_get_old_esc_data into l_old_owner_id,
1491 l_old_status_id, l_old_escalation_level,
1492 l_old_target_date;
1493 close c_get_old_esc_data;
1494
1495 -------------------------------------------------------------------------
1496 -- Update escalation header
1497 -------------------------------------------------------------------------
1498
1499 --bug 2723761
1500 if NOT (l_esc_record.esc_name = FND_API.G_MISS_CHAR
1501 and l_esc_record.esc_description = FND_API.G_MISS_CHAR
1502 and l_esc_record.status_id = FND_API.G_MISS_NUM
1503 and l_esc_record.status_name = FND_API.G_MISS_CHAR
1504 and l_esc_record.esc_owner_id = FND_API.G_MISS_NUM
1505 and l_esc_record.esc_owner_type_code = FND_API.G_MISS_CHAR
1506 and l_esc_record.cust_account_id = FND_API.G_MISS_NUM
1507 and l_esc_record.cust_account_number = FND_API.G_MISS_CHAR
1508 and l_esc_record.cust_address_id = FND_API.G_MISS_NUM
1509 and l_esc_record.cust_address_number = FND_API.G_MISS_CHAR
1510 and l_esc_record.esc_target_date = FND_API.G_MISS_DATE
1511 and l_esc_record.reason_code = FND_API.G_MISS_CHAR
1512 and l_esc_record.escalation_level = FND_API.G_MISS_CHAR
1513 and l_esc_record.attribute1 = FND_API.G_MISS_CHAR
1514 and l_esc_record.attribute2 = FND_API.G_MISS_CHAR
1515 and l_esc_record.attribute3 = FND_API.G_MISS_CHAR
1516 and l_esc_record.attribute4 = FND_API.G_MISS_CHAR
1517 and l_esc_record.attribute5 = FND_API.G_MISS_CHAR
1518 and l_esc_record.attribute6 = FND_API.G_MISS_CHAR
1519 and l_esc_record.attribute7 = FND_API.G_MISS_CHAR
1520 and l_esc_record.attribute8 = FND_API.G_MISS_CHAR
1521 and l_esc_record.attribute9 = FND_API.G_MISS_CHAR
1522 and l_esc_record.attribute10 = FND_API.G_MISS_CHAR
1523 and l_esc_record.attribute11 = FND_API.G_MISS_CHAR
1524 and l_esc_record.attribute12 = FND_API.G_MISS_CHAR
1525 and l_esc_record.attribute13 = FND_API.G_MISS_CHAR
1526 and l_esc_record.attribute14 = FND_API.G_MISS_CHAR
1527 and l_esc_record.attribute15 = FND_API.G_MISS_CHAR
1528 and l_esc_record.attribute_category = FND_API.G_MISS_CHAR) then
1529
1530 JTF_EC_PVT.UPDATE_ESCALATION (
1531 P_API_VERSION => l_api_version,
1532 P_INIT_MSG_LIST => fnd_api.g_false,
1533 P_COMMIT => fnd_api.g_false,
1534 P_OBJECT_VERSION_NUMBER => l_object_version_number,
1535 P_ESCALATION_ID => l_escalation_id,
1536 P_ESCALATION_NUMBER => l_escalation_number,
1537 P_ESCALATION_NAME => l_esc_record.esc_name,
1538 P_DESCRIPTION => l_esc_record.esc_description,
1539 P_ESCALATION_STATUS_NAME => l_esc_record.status_name,
1540 P_ESCALATION_STATUS_ID => l_esc_status_id,
1541 -- P_OPEN_DATE => l_esc_record.esc_open_date, -- cannot be updated
1542 P_CLOSE_DATE => l_esc_close_date,
1543 P_ESCALATION_PRIORITY_NAME => NULL,
1544 P_ESCALATION_PRIORITY_ID => NULL,
1545 P_OWNER_ID => l_esc_record.esc_owner_id,
1546 P_ESCALATION_OWNER_TYPE_CODE => l_esc_record.esc_owner_type_code, --bug 2723761
1547 -- P_OWNER_TERRITORY_ID => l_esc_record.esc_territory_id, -- not updatable in the form
1548 P_ASSIGNED_BY_NAME => NULL,
1549 P_ASSIGNED_BY_ID => NULL,
1550 -- P_CUSTOMER_NUMBER => l_esc_record.customer_number, -- not updatable in the form
1551 -- P_CUSTOMER_ID => l_esc_record.customer_id,
1552 P_CUST_ACCOUNT_NUMBER => l_esc_record.cust_account_number,
1553 P_CUST_ACCOUNT_ID => l_esc_record.cust_account_id,
1554 P_ADDRESS_ID => l_esc_record.cust_address_id,
1555 P_ADDRESS_NUMBER => l_esc_record.cust_address_number,
1556 P_TARGET_DATE => l_esc_record.esc_target_date,
1557 P_REASON_CODE => l_esc_record.reason_code,
1558 P_PRIVATE_FLAG => NULL,
1559 P_PUBLISH_FLAG => NULL,
1560 P_WORKFLOW_PROCESS_ID => NULL,
1561 P_ESCALATION_LEVEL => l_esc_record.escalation_level,
1562 X_RETURN_STATUS => l_return_status,
1563 X_MSG_COUNT => x_msg_count,
1564 X_MSG_DATA => x_msg_data,
1565 p_attribute1 => l_esc_record.attribute1,
1566 p_attribute2 => l_esc_record.attribute2,
1567 p_attribute3 => l_esc_record.attribute3,
1568 p_attribute4 => l_esc_record.attribute4,
1569 p_attribute5 => l_esc_record.attribute5,
1570 p_attribute6 => l_esc_record.attribute6,
1571 p_attribute7 => l_esc_record.attribute7,
1572 p_attribute8 => l_esc_record.attribute8,
1573 p_attribute9 => l_esc_record.attribute9,
1574 p_attribute10 => l_esc_record.attribute10,
1575 p_attribute11 => l_esc_record.attribute11,
1576 p_attribute12 => l_esc_record.attribute12,
1577 p_attribute13 => l_esc_record.attribute13,
1578 p_attribute14 => l_esc_record.attribute14,
1579 p_attribute15 => l_esc_record.attribute15,
1580 p_attribute_category => l_esc_record.attribute_category
1581 );
1582
1583 if (l_return_status = fnd_api.g_ret_sts_error) then
1584 raise fnd_api.g_exc_error;
1585 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1586 raise fnd_api.g_exc_unexpected_error;
1587 end if;
1588
1589 end if;
1590
1591 --------------------------------------------------------------------------------------------------
1592 -- Update, Insert, Delete (depends on the action code) the reference documents.
1593 -- action_code = 'I' - inserts a new reference document.
1594 -- action_code = 'U' - updates the reference document. Just the reference type could be updated
1595 -- action_code = 'D' - removes a reference document
1596 --------------------------------------------------------------------------------------------------
1597
1598 for j in 1..l_reference_documents.COUNT loop
1599
1600 if l_reference_documents(j).action_code = 'D' then
1601
1602 l_return_status := 'x';
1603
1604 -- validate the reference document against the escalation document id.
1605
1606 jtf_ec_util.Validate_Task_Reference_Id( l_reference_documents(j).reference_id,
1607 l_escalation_id, l_return_status);
1608
1609 if (l_return_status = fnd_api.g_ret_sts_error) then
1610 raise fnd_api.g_exc_error;
1611 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1612 raise fnd_api.g_exc_unexpected_error;
1613 end if;
1614
1615 JTF_EC_REFERENCES_PVT.DELETE_REFERENCES(
1616 P_API_VERSION => l_api_version,
1617 P_INIT_MSG_LIST => fnd_api.g_false,
1618 P_COMMIT => fnd_api.g_false,
1619 P_OBJECT_VERSION_NUMBER => l_reference_documents(j).object_version_number,
1620 P_ESCALATION_REFERENCE_ID => l_reference_documents(j).reference_id,
1621 X_RETURN_STATUS =>l_return_status,
1622 X_MSG_COUNT =>x_msg_count,
1623 X_MSG_DATA =>x_msg_data);
1624
1625 if (l_return_status = fnd_api.g_ret_sts_error) then
1626 raise fnd_api.g_exc_error;
1627 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1628 raise fnd_api.g_exc_unexpected_error;
1629 end if;
1630 elsif l_reference_documents(j).action_code = 'U' then
1631
1632 l_return_status := 'x';
1633
1634 -- validate the reference document against the escalation document id.
1635
1636 jtf_ec_util.Validate_Task_Reference_Id( l_reference_documents(j).reference_id,
1637 l_escalation_id,
1638 l_return_status);
1639
1640 if (l_return_status = fnd_api.g_ret_sts_error) then
1641 raise fnd_api.g_exc_error;
1642 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1643 raise fnd_api.g_exc_unexpected_error;
1644 end if;
1645
1646 if l_reference_documents(j).reference_code = 'ESC' then
1647
1648 open c_get_ref_details(l_reference_documents(j).reference_id);
1649 fetch c_get_ref_details into l_object_type_code, l_object_id, l_object_name;
1650 close c_get_ref_details;
1651
1652 if l_reference_documents(j).object_type_code <> fnd_api.g_miss_char then
1653 l_object_type_code := l_reference_documents(j).object_type_code;
1654 end if;
1655
1656 if l_reference_documents(j).object_id <> fnd_api.g_miss_num then
1657 l_object_id := l_reference_documents(j).object_id;
1658 end if;
1659
1660 if l_reference_documents(j).object_name <> fnd_api.g_miss_char then
1661 l_object_name := l_reference_documents(j).object_name;
1662 end if;
1663
1664 if jtf_ec_util.check_if_escalated( l_object_type_code,
1665 l_object_id,
1666 l_object_name,
1667 l_task_ref_id) = TRUE then
1668
1669 -- make sure that this is not this document
1670
1671 if l_task_ref_id <> l_reference_documents(j).reference_id then
1672 fnd_message.set_name ('JTF', 'JTF_TK_ESC_DOC_EXIST');
1673 fnd_message.set_token('OBJECT_TYPE',l_object_type_code);
1674 fnd_message.set_token('OBJECT_NUMBER',l_object_name);
1675 fnd_msg_pub.Add;
1676 raise fnd_api.g_exc_error;
1677 end if;
1678 end if;
1679
1680 end if;
1681
1682 if jtf_ec_util.Reference_Duplicated(l_reference_documents(j).object_type_code,
1683 l_reference_documents(j).object_id,
1684 l_reference_documents(j).object_name,
1685 l_reference_documents(j).reference_code,
1686 l_escalation_id) = FALSE then
1687
1688 JTF_EC_REFERENCES_PVT.UPDATE_REFERENCES (
1689 P_API_VERSION => l_api_version,
1690 P_INIT_MSG_LIST => fnd_api.g_false,
1691 P_COMMIT => fnd_api.g_false,
1692 P_OBJECT_VERSION_NUMBER => l_reference_documents(j).object_version_number,
1693 P_ESCALATION_REFERENCE_ID => l_reference_documents(j).reference_id,
1694 P_OBJECT_TYPE_CODE => l_reference_documents(j).object_type_code,
1695 P_OBJECT_NAME => l_reference_documents(j).object_name,
1696 P_OBJECT_ID => l_reference_documents(j).object_id,
1697 P_OBJECT_DETAILS => NULL,
1698 P_REFERENCE_CODE => l_reference_documents(j).reference_code,
1699 -- P_USAGE => NULL, -- l_reference_documents(j).usage,
1700 X_RETURN_STATUS => l_return_status,
1701 X_MSG_DATA => x_msg_data,
1702 X_MSG_COUNT => x_msg_count,
1703 p_attribute1 => l_reference_documents(j).attribute1,
1704 p_attribute2 => l_reference_documents(j).attribute2,
1705 p_attribute3 => l_reference_documents(j).attribute3,
1706 p_attribute4 => l_reference_documents(j).attribute4,
1707 p_attribute5 => l_reference_documents(j).attribute5,
1708 p_attribute6 => l_reference_documents(j).attribute6,
1709 p_attribute7 => l_reference_documents(j).attribute7,
1710 p_attribute8 => l_reference_documents(j).attribute8,
1711 p_attribute9 => l_reference_documents(j).attribute9,
1712 p_attribute10 => l_reference_documents(j).attribute10,
1713 p_attribute11 => l_reference_documents(j).attribute11,
1714 p_attribute12 => l_reference_documents(j).attribute12,
1715 p_attribute13 => l_reference_documents(j).attribute13,
1716 p_attribute14 => l_reference_documents(j).attribute14,
1717 p_attribute15 => l_reference_documents(j).attribute15,
1718 p_attribute_category => l_reference_documents(j).attribute_category);
1719
1720 if (l_return_status = fnd_api.g_ret_sts_error) then
1721 raise fnd_api.g_exc_error;
1722 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1723 raise fnd_api.g_exc_unexpected_error;
1724 end if;
1725
1726 end if;
1727
1728 elsif l_reference_documents(j).action_code = 'I' then
1729
1730 l_return_status := 'x';
1731
1732 ---------------------------------------------------------------------------------
1733 -- check reference code
1734 -- if the reference code is not passed it will be defaulted from a profile option
1735 ----------------------------------------------------------------------------------
1736
1737 if (l_reference_documents(j).reference_code = fnd_api.g_miss_char
1738 OR l_reference_documents(j).reference_code is NULL)
1739 then
1740 l_reference_documents(j).reference_code := FND_PROFILE.Value_Specific('JTF_EC_DEFAULT_REF_TYPE',
1741 l_user_id,
1742 l_resp_id,
1743 l_resp_appl_id);
1744 end if;
1745
1746 Create_Esc_Reference( l_reference_documents(j),
1747 l_escalation_id,
1748 l_escalation_number,
1749 x_msg_count,
1750 x_msg_data,
1751 l_escalation_reference_id,
1752 l_return_status);
1753
1754 if (l_return_status = fnd_api.g_ret_sts_error) then
1755 raise fnd_api.g_exc_error;
1756 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1757 raise fnd_api.g_exc_unexpected_error;
1758 end if;
1759
1760 end if;
1761
1762 end loop;
1763
1764 -------------------------------------------------------------------------------
1765 -- Delete contact points. The Inserts and Updates are done after the contacts
1766 -- insert since the task_contact_id is a FK in the Task_phones_table
1767 -------------------------------------------------------------------------------
1768
1769 for j in 1..l_cont_points.COUNT loop
1770
1771 if l_cont_points(j).action_code = 'D' then
1772
1773 -- validate the task_phone_id - must be for this escalation document
1774
1775 l_return_status := 'x';
1776
1777 jtf_ec_util.validate_task_phone_id(l_cont_points(j).task_phone_id,
1778 l_escalation_id,
1779 l_return_status);
1780
1781 if (l_return_status = fnd_api.g_ret_sts_error) then
1782 raise fnd_api.g_exc_error;
1783 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1784 raise fnd_api.g_exc_unexpected_error;
1785 end if;
1786
1787 Jtf_Task_Phones_Pub.delete_task_phones (
1788 p_api_version => l_api_version,
1789 p_init_msg_list => fnd_api.g_false,
1790 p_commit => fnd_api.g_false,
1791 p_object_version_number => l_cont_points(j).object_version_number,
1792 p_task_phone_id => l_cont_points(j).task_phone_id,
1793 x_return_status => l_return_status,
1794 x_msg_data => x_msg_data,
1795 x_msg_count => x_msg_count
1796 );
1797
1798 if (l_return_status = fnd_api.g_ret_sts_error) then
1799 raise fnd_api.g_exc_error;
1800 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1801 raise fnd_api.g_exc_unexpected_error;
1802 end if;
1803
1804 elsif l_cont_points(j).action_code = 'U' then
1805
1806 l_return_status := 'x';
1807
1808 -- validate the task_phone_id - must be for this escalation document
1809
1810 jtf_ec_util.validate_task_phone_id(l_cont_points(j).task_phone_id,
1811 l_escalation_id,
1812 l_return_status);
1813
1814 if (l_return_status = fnd_api.g_ret_sts_error) then
1815 raise fnd_api.g_exc_error;
1816 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1817 raise fnd_api.g_exc_unexpected_error;
1818 end if;
1819
1820 l_return_status := 'x';
1821
1822 Jtf_Task_Phones_Pub.update_task_phones(
1823 p_api_version => l_api_version,
1824 p_init_msg_list => fnd_api.g_false,
1825 p_commit => fnd_api.g_false,
1826 p_object_version_number => l_cont_points(j).object_version_number,
1827 p_task_phone_id => l_cont_points(j).task_phone_id,
1828 p_phone_id => l_cont_points(j).contact_point_id,
1829 x_return_status => l_return_status,
1830 x_msg_data => x_msg_data,
1831 x_msg_count => x_msg_count,
1832 p_attribute1 => l_cont_points(j).attribute1,
1833 p_attribute2 => l_cont_points(j).attribute1,
1834 p_attribute3 => l_cont_points(j).attribute1,
1835 p_attribute4 => l_cont_points(j).attribute1,
1836 p_attribute5 => l_cont_points(j).attribute1,
1837 p_attribute6 => l_cont_points(j).attribute1,
1838 p_attribute7 => l_cont_points(j).attribute1,
1839 p_attribute8 => l_cont_points(j).attribute1,
1840 p_attribute9 => l_cont_points(j).attribute1,
1841 p_attribute10 => l_cont_points(j).attribute1,
1842 p_attribute11 => l_cont_points(j).attribute1,
1843 p_attribute12 => l_cont_points(j).attribute1,
1844 p_attribute13 => l_cont_points(j).attribute1,
1845 p_attribute14 => l_cont_points(j).attribute1,
1846 p_attribute15 => l_cont_points(j).attribute1,
1847 p_attribute_category => l_cont_points(j).attribute_category
1848 );
1849
1850 if (l_return_status = fnd_api.g_ret_sts_error) then
1851 raise fnd_api.g_exc_error;
1852 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1853 raise fnd_api.g_exc_unexpected_error;
1854 end if;
1855 end if;
1856 end loop;
1857
1858 --------------------------------------------------------------------------------------------------
1859 -- Update, Insert, Delete (depends on the action code) the contacts.
1860 -- action_code = 'I' - inserts a new contact.
1861 -- action_code = 'U' - updates an existing contact. Just the 'Notify' and 'Requester' flags could be updated.
1862 -- action_code = 'D' - removes a contact. Also the corresponding contact details if any are removed.
1863 --------------------------------------------------------------------------------------------------
1864
1865 for j in 1..l_esc_contacts.COUNT loop
1866
1867 -- deletes an existing contact. Removes the corresponding contact points from jtf_task_phones.
1868
1869 if l_esc_contacts(j).action_code = 'D' then
1870
1871 l_return_status := 'x';
1872
1873 -- validate the task_contact_id against the escalation_id
1874
1875 jtf_ec_util.Validate_Task_Contact_Id( l_esc_contacts(j).task_contact_id,
1876 l_escalation_id,
1877 l_return_status);
1878
1879 if (l_return_status = fnd_api.g_ret_sts_error) then
1880 raise fnd_api.g_exc_error;
1881 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1882 raise fnd_api.g_exc_unexpected_error;
1883 end if;
1884
1885 JTF_EC_CONTACTS_PVT.DELETE_ESCALATION_CONTACTS(
1886 P_API_VERSION => l_api_version,
1887 P_INIT_MSG_LIST => fnd_api.g_false,
1888 P_COMMIT => fnd_api.g_false,
1889 P_OBJECT_VERSION_NUMBER => l_esc_contacts(j).object_version_number,
1890 P_ESCALATION_CONTACT_ID => l_esc_contacts(j).task_contact_id,
1891 X_RETURN_STATUS => l_return_status,
1892 X_MSG_COUNT => x_msg_count,
1893 X_MSG_DATA => x_msg_data);
1894
1895 if (l_return_status = fnd_api.g_ret_sts_error) then
1896 raise fnd_api.g_exc_error;
1897 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1898 raise fnd_api.g_exc_unexpected_error;
1899 end if;
1900
1901 -- delete the contact points
1902
1903 for c in c_cont_phone_id(l_esc_contacts(j).task_contact_id) loop
1904
1905 Jtf_Task_Phones_Pub.delete_task_phones (
1906 p_api_version => l_api_version,
1907 p_init_msg_list => fnd_api.g_false,
1908 p_commit => fnd_api.g_false,
1909 p_object_version_number => c.object_version_number,
1910 p_task_phone_id => c.task_phone_id,
1911 x_return_status => l_return_status,
1912 x_msg_data => x_msg_data,
1913 x_msg_count => x_msg_count
1914 );
1915
1916 if (l_return_status = fnd_api.g_ret_sts_error) then
1917 raise fnd_api.g_exc_error;
1918 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1919 raise fnd_api.g_exc_unexpected_error;
1920 end if;
1921
1922 end loop;
1923
1924 elsif l_esc_contacts(j).action_code = 'U' then
1925
1926 l_return_status := 'x';
1927
1928 -- currently just the escalation_notify_flag and the escalation_requester_flag
1929 -- can be updated. Once the contact is created the contact_type_code and
1930 -- the contact_id cannot be changed.
1931
1932 l_esc_contacts(j).contact_id := FND_API.G_MISS_NUM;
1933 l_esc_contacts(j).contact_type_code := FND_API.G_MISS_CHAR;
1934
1935 if l_esc_contacts(j).escalation_notify_flag is NULL then
1936 l_esc_contacts(j).escalation_notify_flag := 'N';
1937 end if;
1938
1939 if l_esc_contacts(j).escalation_requester_flag is NULL then
1940 l_esc_contacts(j).escalation_requester_flag := 'N';
1941 end if;
1942
1943 -- validate the task_contact_id against the escalation_id
1944
1945 jtf_ec_util.Validate_Task_Contact_Id( l_esc_contacts(j).task_contact_id,
1946 l_escalation_id,
1947 l_return_status);
1948
1949 if (l_return_status = fnd_api.g_ret_sts_error) then
1950 raise fnd_api.g_exc_error;
1951 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1952 raise fnd_api.g_exc_unexpected_error;
1953 end if;
1954
1955 JTF_EC_CONTACTS_PVT.UPDATE_ESCALATION_CONTACTS (
1956 P_API_VERSION => l_api_version,
1957 P_INIT_MSG_LIST => fnd_api.g_false,
1958 P_COMMIT => fnd_api.g_false,
1959 P_OBJECT_VERSION_NUMBER => l_esc_contacts(j).object_version_number,
1960 P_ESCALATION_CONTACT_ID => l_esc_contacts(j).task_contact_id,
1961 P_CONTACT_ID => l_esc_contacts(j).contact_id,
1962 P_CONTACT_TYPE_CODE => l_esc_contacts(j).contact_type_code,
1963 P_ESCALATION_NOTIFY_FLAG => l_esc_contacts(j).escalation_notify_flag,
1964 P_ESCALATION_REQUESTER_FLAG => l_esc_contacts(j).escalation_requester_flag,
1965 X_RETURN_STATUS => l_return_status,
1966 X_MSG_DATA => x_msg_data,
1967 X_MSG_COUNT => x_msg_count,
1968 p_attribute1 => l_esc_contacts(j).attribute1,
1969 p_attribute2 => l_esc_contacts(j).attribute2,
1970 p_attribute3 => l_esc_contacts(j).attribute3,
1971 p_attribute4 => l_esc_contacts(j).attribute4,
1972 p_attribute5 => l_esc_contacts(j).attribute5,
1973 p_attribute6 => l_esc_contacts(j).attribute6,
1974 p_attribute7 => l_esc_contacts(j).attribute7,
1975 p_attribute8 => l_esc_contacts(j).attribute8,
1976 p_attribute9 => l_esc_contacts(j).attribute9,
1977 p_attribute10 => l_esc_contacts(j).attribute10,
1978 p_attribute11 => l_esc_contacts(j).attribute11,
1979 p_attribute12 => l_esc_contacts(j).attribute12,
1980 p_attribute13 => l_esc_contacts(j).attribute13,
1981 p_attribute14 => l_esc_contacts(j).attribute14,
1982 p_attribute15 => l_esc_contacts(j).attribute15,
1983 p_attribute_category => l_esc_contacts(j).attribute_category
1984 );
1985
1986 if (l_return_status = fnd_api.g_ret_sts_error) then
1987 raise fnd_api.g_exc_error;
1988 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1989 raise fnd_api.g_exc_unexpected_error;
1990 end if;
1991
1992 elsif l_esc_contacts(j).action_code = 'I' then
1993
1994 l_return_status := 'x';
1995
1996 Create_Esc_Contacts(p_esc_contacts_rec => l_esc_contacts(j),
1997 -- p_cont_points => l_cont_points,
1998 p_esc_id => l_escalation_id,
1999 p_esc_number => l_escalation_number,
2000 x_msg_count => x_msg_count,
2001 x_msg_data => x_msg_data,
2002 x_esc_contact_id => l_escalation_contact_id,
2003 x_return_status => l_return_status);
2004
2005 if (l_return_status = fnd_api.g_ret_sts_error) then
2006 raise fnd_api.g_exc_error;
2007 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
2008 raise fnd_api.g_exc_unexpected_error;
2009 end if;
2010
2011 end if;
2012
2013 end loop;
2014
2015 -------------------------------------------------------------------------------
2016 -- validate Requester. The escalation Document must have only one requester.
2017 -------------------------------------------------------------------------------
2018
2019 l_return_status := 'x';
2020
2021 jtf_ec_util.Validate_Requester(l_escalation_id,
2022 l_return_status);
2023
2024 if l_return_status <> fnd_api.g_ret_sts_success then
2025 raise fnd_api.g_exc_error;
2026 end if;
2027
2028 -------------------------------------------------------------------------------
2029 -- Update , Insert contact points. The Deletes are done before the contacts
2030 -- deletes to avoid the deletion of the same record.
2031 -------------------------------------------------------------------------------
2032
2033 for j in 1..l_cont_points.COUNT loop
2034
2035 if l_cont_points(j).action_code = 'I' then
2036
2037 l_return_status := 'x';
2038
2039 -- check whether this is a valid contact for this escalation. Also get the task contact id.
2040
2041 jtf_ec_util.Validate_Contact_id(l_cont_points(j).contact_id,
2042 l_cont_points(j).contact_type_code,
2043 l_escalation_id,
2044 l_escalation_contact_id,
2045 l_return_status);
2046
2047 if (l_return_status = fnd_api.g_ret_sts_error) then
2048 raise fnd_api.g_exc_error;
2049 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
2050 raise fnd_api.g_exc_unexpected_error;
2051 end if;
2052
2053 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute1);
2054 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute2);
2055 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute3);
2056 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute4);
2057 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute5);
2058 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute6);
2059 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute7);
2060 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute8);
2061 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute9);
2062 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute10);
2063 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute11);
2064 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute12);
2065 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute13);
2066 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute14);
2067 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute15);
2068 jtf_ec_util.Conv_Miss_Char(l_cont_points(j).attribute_category);
2069
2070 -- create a phone_id for this contact
2071
2072 Jtf_Task_Phones_Pub.create_task_phones (
2073 p_api_version => l_api_version,
2074 p_init_msg_list => fnd_api.g_false,
2075 p_commit => fnd_api.g_false,
2076 p_task_contact_id => l_escalation_contact_id,
2077 p_phone_id => l_cont_points(j).contact_point_id,
2078 x_task_phone_id => l_task_phone_id,
2079 x_return_status => l_return_status,
2080 x_msg_data => x_msg_data,
2081 x_msg_count => x_msg_count,
2082 p_attribute1 => l_cont_points(j).attribute1,
2083 p_attribute2 => l_cont_points(j).attribute2,
2084 p_attribute3 => l_cont_points(j).attribute3,
2085 p_attribute4 => l_cont_points(j).attribute4,
2086 p_attribute5 => l_cont_points(j).attribute5,
2087 p_attribute6 => l_cont_points(j).attribute6,
2088 p_attribute7 => l_cont_points(j).attribute7,
2089 p_attribute8 => l_cont_points(j).attribute8,
2090 p_attribute9 => l_cont_points(j).attribute9,
2091 p_attribute10 => l_cont_points(j).attribute10,
2092 p_attribute11 => l_cont_points(j).attribute11,
2093 p_attribute12 => l_cont_points(j).attribute12,
2094 p_attribute13 => l_cont_points(j).attribute13,
2095 p_attribute14 => l_cont_points(j).attribute14,
2096 p_attribute15 => l_cont_points(j).attribute15,
2097 p_attribute_category => l_cont_points(j).attribute_category
2098 );
2099
2100 if (l_return_status = fnd_api.g_ret_sts_error) then
2101 raise fnd_api.g_exc_error;
2102 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
2103 raise fnd_api.g_exc_unexpected_error;
2104 end if;
2105
2106 end if;
2107
2108 end loop;
2109
2110 -------------------------------------------------------------------------------------------------------------
2111 -- Insert, Update Notes
2112 -------------------------------------------------------------------------------------------------------------
2113
2114 for j in 1..l_notes.COUNT loop
2115
2116 if l_notes(j).action_code = 'I' then
2117
2118 l_return_status := 'x';
2119
2120 Create_Esc_Note(l_notes(j),
2121 l_escalation_id,
2122 g_escalation_code,
2123 l_user_id,
2124 l_login_id,
2125 x_msg_count,
2126 x_msg_data,
2127 l_note_id,
2128 l_return_status);
2129
2130 if (l_return_status = fnd_api.g_ret_sts_error) then
2131 raise fnd_api.g_exc_error;
2132 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
2133 raise fnd_api.g_exc_unexpected_error;
2134 end if;
2135
2136 elsif l_notes(j).action_code = 'U' then
2137
2138 l_return_status := 'x';
2139
2140 -- check whether the note belongs to this escalation document
2141
2142 jtf_ec_util.Validate_Note_Id(l_notes(j).note_id,
2143 l_escalation_id,
2144 l_return_status);
2145
2146 if (l_return_status = fnd_api.g_ret_sts_error) then
2147 raise fnd_api.g_exc_error;
2148 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
2149 raise fnd_api.g_exc_unexpected_error;
2150 end if;
2151
2152 jtf_notes_pub.Update_note(
2153 p_api_version => l_api_version,
2154 p_init_msg_list => fnd_api.g_false,
2155 p_commit => fnd_api.g_false,
2156 x_return_status => l_return_status,
2157 x_msg_count => x_msg_count,
2158 x_msg_data => x_msg_data,
2159 p_jtf_note_id => l_notes(j).note_id,
2160 p_notes => l_notes(j).note,
2161 p_notes_detail => l_notes(j).note_detail,
2162 p_note_type => l_notes(j).note_type,
2163 p_note_status => l_notes(j).note_status,
2164 p_entered_by => l_user_id,
2165 p_last_updated_by => l_user_id,
2166 p_last_update_date => SYSDATE
2167 );
2168
2169 if (l_return_status = fnd_api.g_ret_sts_error) then
2170 raise fnd_api.g_exc_error;
2171 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
2172 raise fnd_api.g_exc_unexpected_error;
2173 end if;
2174
2175 end if;
2176
2177 end loop;
2178 -------------------------------------------------------------------------------------------------------------
2179 -- Workflow
2180 -------------------------------------------------------------------------------------------------------------
2181
2182 if l_old_owner_id <> l_esc_record.esc_owner_id
2183 and l_esc_record.esc_owner_id <> fnd_api.g_miss_num
2184 then
2185 l_owner_changed := 'Y';
2186 end if;
2187
2188 if l_old_status_id <> l_esc_status_id
2189 and l_esc_status_id <> fnd_api.g_miss_num
2190 then
2191 l_status_changed := 'Y';
2192 end if;
2193
2194 if l_old_escalation_level <> l_esc_record.escalation_level
2195 and l_esc_record.escalation_level <> fnd_api.g_miss_char
2196 then
2197 l_level_changed := 'Y';
2198 end if;
2199
2200 if nvl(l_old_target_date, to_date('01/01/1000','DD/MM/YYYY')) <> nvl(l_esc_record.esc_target_date, to_date('01/01/1000','DD/MM/YYYY'))
2201 and nvl(l_esc_record.esc_target_date, to_date('01/01/1000','DD/MM/YYYY')) <> fnd_api.g_miss_date
2202 then
2203 l_target_date_changed := 'Y';
2204 end if;
2205
2206 -- send notifications if there are changes
2207
2208 l_return_status := 'x';
2209
2210 if (l_owner_changed='Y') OR (l_level_changed='Y') OR
2211 (l_status_changed='Y') OR (l_target_date_changed='Y') then
2212
2213 JTF_EC_WORKFLOW_PKG.Start_Resc_Workflow(
2214 P_API_VERSION => l_api_version,
2215 P_INIT_MSG_LIST => fnd_api.g_false,
2216 P_COMMIT => fnd_api.g_false,
2217 X_RETURN_STATUS => l_return_status,
2218 X_MSG_COUNT => x_msg_count,
2219 X_MSG_DATA => x_msg_data,
2220 P_TASK_ID => l_escalation_id,
2221 P_DOC_CREATED => 'N',
2222 P_OWNER_CHANGED => l_owner_changed,
2223 P_LEVEL_CHANGED => l_level_changed,
2224 P_STATUS_CHANGED => l_status_changed,
2225 P_TARGET_DATE_CHANGED => l_target_date_changed,
2226 P_OLD_OWNER_ID => l_old_owner_id,
2227 P_OLD_LEVEL => l_old_escalation_level,
2228 P_OLD_STATUS_ID => l_old_status_id,
2229 P_OLD_TARGET_DATE => l_old_target_date,
2230 x_notif_not_sent => l_notif_not_sent,
2231 X_WF_PROCESS_ID => l_wf_process_id
2232 );
2233
2234 if (l_return_status = fnd_api.g_ret_sts_error) then
2235 raise fnd_api.g_exc_error;
2236 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
2237 raise fnd_api.g_exc_unexpected_error;
2238 end if;
2239
2240 end if;
2241
2242 -- Update the wf_process_id
2243
2244 update jtf_tasks_b
2245 set workflow_process_id = l_wf_process_id
2246 where task_id = l_escalation_id;
2247
2248 --------------------------------------------------------------------------------------------
2249 -- standard verticle industry user hook post-processing
2250 --------------------------------------------------------------------------------------------
2251 if ( jtf_usr_hks.ok_to_execute( g_pkg_name, l_api_name, 'A', 'V' ) ) then
2252
2253 jtf_ec_vuhk.Update_Escalation_Post(
2254 p_esc_id => l_escalation_id,
2255 p_esc_number => l_escalation_number,
2256 p_object_version => l_object_version_number,
2257 p_esc_record => l_esc_record,
2258 p_reference_documents => l_reference_documents,
2259 p_esc_contacts => l_esc_contacts,
2260 p_cont_points => l_cont_points,
2261 p_notes => l_notes,
2262 x_return_status => l_return_status,
2263 x_msg_count => x_msg_count,
2264 x_msg_data => x_msg_data);
2265
2266 if l_return_status = fnd_api.g_ret_sts_error then
2267 raise fnd_api.g_exc_error;
2268 elsif l_return_status = fnd_api.g_ret_sts_unexp_error then
2269 raise fnd_api.g_exc_unexpected_error;
2270 end if;
2271
2272 end if;
2273
2274 --------------------------------------------------------------------------------------------
2275 -- standard customer user hook post-processing
2276 --------------------------------------------------------------------------------------------
2277 if ( jtf_usr_hks.ok_to_execute( g_pkg_name, l_api_name, 'A', 'C' ) ) then
2278 jtf_ec_cuhk.Update_Escalation_Post(
2279 p_esc_id => l_escalation_id,
2280 p_esc_number => l_escalation_number,
2281 p_object_version => l_object_version_number,
2282 p_esc_record => l_esc_record,
2283 p_reference_documents => l_reference_documents,
2284 p_esc_contacts => l_esc_contacts,
2285 p_cont_points => l_cont_points,
2286 p_notes => l_notes,
2287 x_return_status => l_return_status,
2288 x_msg_count => x_msg_count,
2289 x_msg_data => x_msg_data);
2290
2291 if l_return_status = fnd_api.g_ret_sts_error then
2292 raise fnd_api.g_exc_error;
2293 elsif l_return_status = fnd_api.g_ret_sts_unexp_error then
2294 raise fnd_api.g_exc_unexpected_error;
2295 end if;
2296
2297 end if;
2298
2299 x_object_version_number :=l_object_version_number;
2300 x_workflow_process_id := l_wf_process_id;
2301
2302 -- Standard check of p_commit.
2303
2304 IF FND_API.To_Boolean( p_commit ) THEN
2305 COMMIT WORK;
2306 END IF;
2307
2308 fnd_msg_pub.count_and_get
2309 ( p_count => x_msg_count,
2310 p_data => x_msg_data
2311 );
2312
2313 EXCEPTION
2314
2315 WHEN fnd_api.g_exc_error
2316 THEN
2317 ROLLBACK TO Update_Escalation;
2318 x_return_status := fnd_api.g_ret_sts_error;
2319 fnd_msg_pub.count_and_get (
2320 p_count => x_msg_count,
2321 p_data => x_msg_data
2322 );
2323
2324 WHEN fnd_api.g_exc_unexpected_error
2325 THEN
2326
2327 ROLLBACK TO Update_Escalation;
2328 x_return_status := fnd_api.g_ret_sts_unexp_error;
2329 fnd_msg_pub.count_and_get (
2330 p_count => x_msg_count,
2331 p_data => x_msg_data
2332 );
2333
2334 WHEN OTHERS
2335 THEN
2336 ROLLBACK TO Update_Escalation;
2337 x_return_status := fnd_api.g_ret_sts_unexp_error;
2338 if FND_MSG_PUB.Check_Msg_Level
2339 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2340 then
2341 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
2342 end if;
2343
2344 fnd_msg_pub.count_and_get (
2345 p_count => x_msg_count,
2346 p_data => x_msg_data
2347 );
2348 END UPDATE_ESCALATION;
2349
2350 PROCEDURE DELETE_ESCALATION(
2351 p_api_version IN NUMBER,
2352 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
2353 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
2354 x_return_status OUT NOCOPY VARCHAR2,
2355 x_msg_count OUT NOCOPY NUMBER,
2356 x_msg_data OUT NOCOPY VARCHAR2,
2357 p_user_id IN NUMBER,
2358 p_login_id IN NUMBER,
2359 p_esc_id IN jtf_tasks_b.task_id%TYPE,
2360 p_esc_number IN jtf_tasks_b.task_number%TYPE,
2361 p_object_version IN NUMBER) Is
2362
2363 l_api_version CONSTANT NUMBER := 1.0;
2364 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ESCALATION';
2365 l_escalation_id jtf_tasks_b.task_id%TYPE := p_esc_id;
2366 l_escalation_number jtf_tasks_b.task_number%TYPE := p_esc_number;
2367 l_return_status varchar2(1) := 'x';
2368 l_esc_task_id jtf_tasks_b.task_id%TYPE;
2369 l_object_version_number NUMBER := p_object_version;
2370 l_user_id NUMBER := p_user_id;
2371 l_login_id NUMBER := p_login_id;
2372
2373 BEGIN
2374
2375 SAVEPOINT Delete_Escalation;
2376
2377 -- Standard call to check for call compatibility.
2378
2379
2380 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2381 p_api_version,
2382 l_api_name,
2383 G_PKG_NAME)
2384 THEN
2385 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2386 END IF;
2387
2388 -- Check p_init_msg_list
2389
2390 IF FND_API.To_Boolean( p_init_msg_list ) THEN
2391 FND_MSG_PUB.initialize;
2392 END IF;
2393
2394 -- Initialize API return status to success
2395
2396 x_return_status := FND_API.G_RET_STS_SUCCESS;
2397
2398 --------------------------------------------------------------------------------------------
2399 -- standard customer user hook pre-processing
2400 --------------------------------------------------------------------------------------------
2401 if ( jtf_usr_hks.ok_to_execute( g_pkg_name, l_api_name, 'B', 'C' ) ) then
2402
2403 jtf_ec_cuhk.Delete_Escalation_Pre(
2404 p_esc_id => l_escalation_id,
2405 p_esc_number => l_escalation_number,
2406 p_object_version => l_object_version_number,
2407 x_return_status => l_return_status,
2408 x_msg_count => x_msg_count,
2409 x_msg_data => x_msg_data);
2410
2411 if l_return_status = fnd_api.g_ret_sts_error then
2412 raise fnd_api.g_exc_error;
2413 elsif l_return_status = fnd_api.g_ret_sts_unexp_error then
2414 raise fnd_api.g_exc_unexpected_error;
2415 end if;
2416
2417 end if;
2418 --------------------------------------------------------------------------------------------
2419 -- standard verticle industry user hook pre-processing
2420 --------------------------------------------------------------------------------------------
2421 if ( jtf_usr_hks.ok_to_execute( g_pkg_name, l_api_name, 'B', 'V' ) ) then
2422
2423 jtf_ec_vuhk.Delete_Escalation_Pre(
2424 p_esc_id => l_escalation_id,
2425 p_esc_number => l_escalation_number,
2426 p_object_version => l_object_version_number,
2427 x_return_status => l_return_status,
2428 x_msg_count => x_msg_count,
2429 x_msg_data => x_msg_data);
2430
2431 if l_return_status = fnd_api.g_ret_sts_error then
2432 raise fnd_api.g_exc_error;
2433 elsif l_return_status = fnd_api.g_ret_sts_unexp_error then
2434 raise fnd_api.g_exc_unexpected_error;
2435 end if;
2436
2437 end if;
2438
2439 -------------------------------------------------------------------------
2440 -- validation
2441 -------------------------------------------------------------------------
2442 -- validate p_user_id
2443 -------------------------------------------------------------------------
2444
2445 if l_user_id is NOT NULL then
2446 jtf_ec_util.Validate_Who_Info( l_api_name,
2447 l_user_id,
2448 l_login_id,
2449 l_return_status
2450 );
2451 if (l_return_status = fnd_api.g_ret_sts_error) then
2452 raise fnd_api.g_exc_error;
2453 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
2454 raise fnd_api.g_exc_unexpected_error;
2455 end if;
2456 else
2457 jtf_ec_util.Add_Invalid_Argument_Msg(l_api_name, 'NULL' ,'user_id');
2458 raise fnd_api.g_exc_error;
2459 end if;
2460
2461 -------------------------------------------------------------------------
2462 -- Validate Escalation Task
2463 -- Performs also number to id conversion
2464 -------------------------------------------------------------------------
2465
2466 l_return_status := 'x';
2467
2468 if p_esc_id <> fnd_api.g_miss_num then
2469 l_escalation_id := p_esc_id;
2470 if p_esc_number <> fnd_api.g_miss_char then
2471 jtf_ec_util.add_param_ignored_msg(l_api_name, 'p_esc_number');
2472 end if;
2473 elsif p_esc_number <> fnd_api.g_miss_char then
2474 l_escalation_number := p_esc_number;
2475 else jtf_ec_util.add_missing_param_msg(l_api_name, 'p_esc_name');
2476 end if;
2477
2478 jtf_ec_util.Validate_Esc_Document(l_escalation_id,
2479 l_escalation_number,
2480 l_esc_task_id, -- converted number to id
2481 l_return_status);
2482
2483 if (l_return_status = fnd_api.g_ret_sts_error) then
2484 raise fnd_api.g_exc_error;
2485 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
2486 raise fnd_api.g_exc_unexpected_error;
2487 end if;
2488
2489 l_escalation_id := l_esc_task_id;
2490
2491 JTF_EC_PVT.DELETE_ESCALATION(
2492 P_API_VERSION => l_api_version,
2493 P_INIT_MSG_LIST => fnd_api.g_false,
2494 P_COMMIT => fnd_api.g_false,
2495 P_OBJECT_VERSION_NUMBER => l_object_version_number,
2496 P_ESCALATION_ID => l_escalation_id,
2497 P_ESCALATION_NUMBER => l_escalation_number,
2498 X_RETURN_STATUS => l_return_status,
2499 X_MSG_COUNT => x_msg_count,
2500 X_MSG_DATA => x_msg_data);
2501
2502 if (l_return_status = fnd_api.g_ret_sts_error) then
2503 raise fnd_api.g_exc_error;
2504 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
2505 raise fnd_api.g_exc_unexpected_error;
2506 end if;
2507
2508 --------------------------------------------------------------------------------------------
2509 -- standard verticle industry user hook post-processing
2510 --------------------------------------------------------------------------------------------
2511 if ( jtf_usr_hks.ok_to_execute( g_pkg_name, l_api_name, 'A', 'V' ) ) then
2512
2513 jtf_ec_vuhk.Delete_Escalation_Post(
2514 p_esc_id => l_escalation_id,
2515 p_esc_number => l_escalation_number,
2516 p_object_version => l_object_version_number,
2517 x_return_status => l_return_status,
2518 x_msg_count => x_msg_count,
2519 x_msg_data => x_msg_data);
2520
2521 if l_return_status = fnd_api.g_ret_sts_error then
2522 raise fnd_api.g_exc_error;
2523 elsif l_return_status = fnd_api.g_ret_sts_unexp_error then
2524 raise fnd_api.g_exc_unexpected_error;
2525 end if;
2526
2527 end if;
2528
2529 --------------------------------------------------------------------------------------------
2530 -- standard customer user hook post-processing
2531 --------------------------------------------------------------------------------------------
2532
2533 if ( jtf_usr_hks.ok_to_execute( g_pkg_name, l_api_name, 'A', 'C' ) ) then
2534
2535 jtf_ec_cuhk.Delete_Escalation_Post(
2536 p_esc_id => l_escalation_id,
2537 p_esc_number => l_escalation_number,
2538 p_object_version => l_object_version_number,
2539 x_return_status => l_return_status,
2540 x_msg_count => x_msg_count,
2541 x_msg_data => x_msg_data);
2542
2543 if l_return_status = fnd_api.g_ret_sts_error then
2544 raise fnd_api.g_exc_error;
2545 elsif l_return_status = fnd_api.g_ret_sts_unexp_error then
2546 raise fnd_api.g_exc_unexpected_error;
2547 end if;
2548
2549 end if;
2550
2551 -- Standard check of p_commit.
2552
2553 IF FND_API.To_Boolean( p_commit ) THEN
2554 COMMIT WORK;
2555 END IF;
2556
2557 fnd_msg_pub.count_and_get
2558 ( p_count => x_msg_count,
2559 p_data => x_msg_data
2560 );
2561
2562 EXCEPTION
2563
2564 WHEN fnd_api.g_exc_error
2565 THEN
2566 ROLLBACK TO Delete_Escalation;
2567 x_return_status := fnd_api.g_ret_sts_error;
2568
2569 fnd_msg_pub.count_and_get (
2570 p_count => x_msg_count,
2571 p_data => x_msg_data
2572 );
2573
2574 WHEN fnd_api.g_exc_unexpected_error
2575 THEN
2576
2577 ROLLBACK TO Delete_Escalation;
2578 x_return_status := fnd_api.g_ret_sts_unexp_error;
2579 fnd_msg_pub.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
2580
2581 WHEN OTHERS
2582 THEN
2583 ROLLBACK TO Delete_Escalation;
2584 x_return_status := fnd_api.g_ret_sts_unexp_error;
2585
2586 if FND_MSG_PUB.Check_Msg_Level
2587 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2588 then
2589 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
2590 end if;
2591
2592 fnd_msg_pub.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
2593
2594 END DELETE_ESCALATION;
2595
2596 END JTF_EC_PUB;