[Home] [Help]
PACKAGE BODY: APPS.JTF_EC_REFERENCES_PVT
Source
1 PACKAGE BODY jtf_ec_references_pvt AS
2 /* $Header: jtfecreb.pls 115.19 2004/02/13 10:58:24 nselvam ship $ */
3 g_pkg_name CONSTANT VARCHAR2(30) := 'JTF_EC_REFERENCES_PVT';
4
5 PROCEDURE create_references (
6 p_api_version IN NUMBER,
7 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
8 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
9 p_escalation_id IN NUMBER DEFAULT NULL,
10 p_escalation_number IN VARCHAR2 DEFAULT NULL,
11 p_object_type_code IN VARCHAR2,
12 p_object_name IN VARCHAR2,
13 p_object_id IN NUMBER,
14 p_object_details IN VARCHAR2 DEFAULT NULL,
15 p_reference_code IN VARCHAR2 DEFAULT NULL,
16 p_usage IN VARCHAR2 DEFAULT NULL,
17 x_return_status OUT NOCOPY VARCHAR2,
18 x_msg_data OUT NOCOPY VARCHAR2,
19 x_msg_count OUT NOCOPY NUMBER,
20 x_escalation_reference_id OUT NOCOPY NUMBER,
21 p_attribute1 IN VARCHAR2 DEFAULT null ,
22 p_attribute2 IN VARCHAR2 DEFAULT null ,
23 p_attribute3 IN VARCHAR2 DEFAULT null ,
24 p_attribute4 IN VARCHAR2 DEFAULT null ,
25 p_attribute5 IN VARCHAR2 DEFAULT null ,
26 p_attribute6 IN VARCHAR2 DEFAULT null ,
27 p_attribute7 IN VARCHAR2 DEFAULT null ,
28 p_attribute8 IN VARCHAR2 DEFAULT null ,
29 p_attribute9 IN VARCHAR2 DEFAULT null ,
30 p_attribute10 IN VARCHAR2 DEFAULT null ,
31 p_attribute11 IN VARCHAR2 DEFAULT null ,
32 p_attribute12 IN VARCHAR2 DEFAULT null ,
33 p_attribute13 IN VARCHAR2 DEFAULT null ,
34 p_attribute14 IN VARCHAR2 DEFAULT null ,
35 p_attribute15 IN VARCHAR2 DEFAULT null ,
36 p_attribute_category IN VARCHAR2 DEFAULT null
37 )
38 IS
39 l_api_version CONSTANT NUMBER := 1.0;
40 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_REFERENCES';
41 l_escalation_reference_id NUMBER;
42 l_rowid ROWID;
43 l_escalation_id jtf_tasks_b.task_id%TYPE := p_escalation_id;
44 l_escalation_number jtf_tasks_b.task_number%TYPE := p_escalation_number;
45 x CHAR;
46 --Created for BES enh 2660883
47 l_esc_ref_rec jtf_ec_references_pvt.Esc_Ref_rec;
48
49 BEGIN
50 SAVEPOINT create_references_pub;
51 x_return_status := fnd_api.g_ret_sts_success;
52
53 IF NOT fnd_api.compatible_api_call (
54 l_api_version,
55 p_api_version,
56 l_api_name,
57 g_pkg_name
58 )
59 THEN
60 RAISE fnd_api.g_exc_unexpected_error;
61 END IF;
62
63 IF fnd_api.to_boolean (p_init_msg_list)
64 THEN
65 fnd_msg_pub.initialize;
66 END IF;
67
68 -----
69 ----- Validate escaltion
70 -----
71 jtf_task_utl.validate_task (
72 x_return_status => x_return_status,
73 p_task_id => l_escalation_id,
74 p_task_number => l_escalation_number,
75 x_task_id => l_escalation_id
76 );
77
78 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
79 THEN
80 x_return_status := fnd_api.g_ret_sts_unexp_error;
81 RAISE fnd_api.g_exc_unexpected_error;
82 END IF;
83
84 IF l_escalation_id IS NULL
85 THEN
86 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_TASK');
87 fnd_msg_pub.add;
88 x_return_status := fnd_api.g_ret_sts_unexp_error;
89 RAISE fnd_api.g_exc_unexpected_error;
90 END IF;
91
92 ----
93 ----
94 ----
95 ----
96 IF p_reference_code IS NOT NULL
97 THEN
98 IF jtf_task_utl.validate_lookup (
99 'JTF_TASK_REFERENCE_CODES',
100 p_reference_code,
101 'Escalation Reference Code (JTF_TASK_REFERENCE_CODES)'
102 )
103 THEN
104 NULL;
105 ELSE
106 x_return_status := fnd_api.g_ret_sts_unexp_error;
107 RAISE fnd_api.g_exc_unexpected_error;
108 END IF;
109 END IF;
110
111 jtf_task_references_pub.create_references (
112 p_api_version => 1.0,
113 p_init_msg_list => fnd_api.g_false,
114 p_commit => fnd_api.g_false,
115 p_task_id => l_escalation_id,
116 p_object_type_code => p_object_type_code,
117 p_object_name => p_object_name,
118 p_object_id => p_object_id,
119 p_object_details => p_object_details,
120 p_reference_code => p_reference_code,
121 p_usage => p_usage,
122 x_return_status => x_return_status,
123 x_msg_data => x_msg_data,
124 x_msg_count => x_msg_count,
125 x_task_reference_id => x_escalation_reference_id,
126 p_attribute1 => p_attribute1,
127 p_attribute2 => p_attribute2,
128 p_attribute3 => p_attribute3,
129 p_attribute4 => p_attribute4,
130 p_attribute5 => p_attribute5,
131 p_attribute6 => p_attribute6,
132 p_attribute7 => p_attribute7,
133 p_attribute8 => p_attribute8,
134 p_attribute9 => p_attribute9,
135 p_attribute10 => p_attribute10,
136 p_attribute11 => p_attribute11,
137 p_attribute12 => p_attribute12,
138 p_attribute13 => p_attribute13,
139 p_attribute14 => p_attribute14,
140 p_attribute15 => p_attribute15,
141 p_attribute_category => p_attribute_category
142 );
143
144 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
145 THEN
146 x_return_status := fnd_api.g_ret_sts_unexp_error;
147 RAISE fnd_api.g_exc_unexpected_error;
148 END IF;
149
150 IF fnd_api.to_boolean (p_commit)
151 THEN
152 COMMIT WORK;
153 END IF;
154
155 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
156
157 --Created for BES enh 2660883
158 begin
159
160 l_esc_ref_rec.task_reference_id := x_escalation_reference_id;
161 l_esc_ref_rec.object_type_code := p_object_type_code;
162 l_esc_ref_rec.reference_code := p_reference_code;
163 l_esc_ref_rec.object_id := p_object_id;
164 -- Added for Bug # 3385990
165 l_esc_ref_rec.task_id := l_escalation_id;
166
167 jtf_esc_wf_events_pvt.publish_create_escRef
168 (p_esc_ref_rec => l_esc_ref_rec);
169
170 EXCEPTION when others then
171 null;
172 END;
173 --End BES enh 2660883
174
175 EXCEPTION
176 WHEN fnd_api.g_exc_unexpected_error
177 THEN
178 ROLLBACK TO create_references_pub;
179 x_return_status := fnd_api.g_ret_sts_unexp_error;
180 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
181 WHEN NO_DATA_FOUND
182 THEN
183 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_OBJECT_NAME');
184 fnd_message.set_token ('P_OBJECT_TYPE_CODE', p_object_type_code);
185 fnd_message.set_token ('P_OBJECT_NAME', p_object_name);
186 fnd_msg_pub.add;
187 x_return_status := fnd_api.g_ret_sts_unexp_error;
188 WHEN OTHERS
189 THEN
190 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
191 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
192 x_return_status := fnd_api.g_ret_sts_unexp_error;
193 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
194 END;
195
196 /* PROCEDURE lock_references (
197 p_api_version IN NUMBER,
198 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
199 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
200 p_escalation_reference_id IN NUMBER,
201 p_object_version_number IN NUMBER,
202 x_return_status OUT VARCHAR2,
203 x_msg_data OUT VARCHAR2,
204 x_msg_count OUT NUMBER
205 ) ;
206 */
207 PROCEDURE update_references (
208 p_api_version IN NUMBER,
209 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
210 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
211 p_object_version_number IN OUT NOCOPY NUMBER,
212 p_escalation_reference_id IN NUMBER,
213 p_object_type_code IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
214 p_object_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
215 p_object_id IN NUMBER DEFAULT fnd_api.g_miss_num,
216 p_object_details IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
217 p_reference_code IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
218 p_usage IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
219 x_return_status OUT NOCOPY VARCHAR2,
220 x_msg_data OUT NOCOPY VARCHAR2,
221 x_msg_count OUT NOCOPY NUMBER,
222 p_attribute1 IN VARCHAR2 DEFAULT null ,
223 p_attribute2 IN VARCHAR2 DEFAULT null ,
224 p_attribute3 IN VARCHAR2 DEFAULT null ,
225 p_attribute4 IN VARCHAR2 DEFAULT null ,
226 p_attribute5 IN VARCHAR2 DEFAULT null ,
227 p_attribute6 IN VARCHAR2 DEFAULT null ,
228 p_attribute7 IN VARCHAR2 DEFAULT null ,
229 p_attribute8 IN VARCHAR2 DEFAULT null ,
230 p_attribute9 IN VARCHAR2 DEFAULT null ,
231 p_attribute10 IN VARCHAR2 DEFAULT null ,
232 p_attribute11 IN VARCHAR2 DEFAULT null ,
233 p_attribute12 IN VARCHAR2 DEFAULT null ,
234 p_attribute13 IN VARCHAR2 DEFAULT null ,
235 p_attribute14 IN VARCHAR2 DEFAULT null ,
236 p_attribute15 IN VARCHAR2 DEFAULT null ,
237 p_attribute_category IN VARCHAR2 DEFAULT null,
238 p_task_id IN NUMBER DEFAULT fnd_api.g_miss_num
239 )
240 IS
241 l_api_version CONSTANT NUMBER := 1.0;
242 l_api_name CONSTANT VARCHAR2(30)
243 := 'UPDATE_REFERENCES';
244 l_task_reference_id jtf_task_references_vl.task_reference_id%TYPE
245 := p_escalation_reference_id;
246
247 CURSOR c_escalation_reference
248 IS
249 SELECT 1 x
250 FROM jtf_task_references_vl
251 WHERE task_reference_id = p_escalation_reference_id;
252
253 escalation_reference c_escalation_reference%ROWTYPE;
254 x CHAR;
255 --Created for BES enh 2660883
256 l_esc_ref_rec_old jtf_ec_references_pvt.Esc_Ref_rec;
257 l_esc_ref_rec_new jtf_ec_references_pvt.Esc_Ref_rec;
258
259 -- Added Task_ID for Bug # 3385990
260 CURSOR c_ref_orig (reference_id IN NUMBER) IS
261 SELECT REFERENCE_CODE , OBJECT_TYPE_CODE , OBJECT_ID, TASK_ID
262 FROM JTF_TASK_REFERENCES_B
263 WHERE task_reference_id = reference_id;
264
265 rec_ref_orig c_ref_orig%ROWTYPE;
266
267 BEGIN
268
269
270 SAVEPOINT update_references_pub;
271
272 x_return_status := fnd_api.g_ret_sts_success;
273
274 IF NOT fnd_api.compatible_api_call (
275 l_api_version,
276 p_api_version,
277 l_api_name,
278 g_pkg_name
279 )
280 THEN
281 RAISE fnd_api.g_exc_unexpected_error;
282 END IF;
283
284 IF fnd_api.to_boolean (p_init_msg_list)
285 THEN
286 fnd_msg_pub.initialize;
287 END IF;
288
289 IF p_escalation_reference_id IS NULL
290 THEN
291 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_REFER');
292 fnd_msg_pub.add;
293 x_return_status := fnd_api.g_ret_sts_unexp_error;
294 RAISE fnd_api.g_exc_unexpected_error;
295 END IF;
296
297 OPEN c_escalation_reference;
298 FETCH c_escalation_reference INTO escalation_reference;
299
300
301 IF c_escalation_reference%NOTFOUND
302 THEN
303 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_REFER');
304 fnd_message.set_token ('P_TASK_REFERENCE_ID', p_escalation_reference_id);
305 fnd_msg_pub.add;
306 x_return_status := fnd_api.g_ret_sts_unexp_error;
307 RAISE fnd_api.g_exc_unexpected_error;
308 END IF;
309
310
311 -----
312 ----- checking each source doc. should have only one escalation.
313 ----- ( other than the given escalation ).
314 BEGIN
315 SELECT 1
316 INTO x
317 FROM jtf_tasks_vl tasks,
318 jtf_task_references_vl reference,
319 jtf_task_statuses_vl status
320 WHERE reference.task_reference_id <> p_escalation_reference_id
321 AND tasks.task_id = reference.task_id
322 AND tasks.task_type_id = jtf_ec_pub.g_escalation_type_id
323 AND reference.object_type_code = p_object_type_code
324 AND reference.object_id = p_object_id
325 AND tasks.task_status_id = status.task_status_id
326 AND status.closed_flag = 'Y';
327
328 fnd_message.set_name ('JTF', 'JTF_EC_DUPLICATE_ESCALATION');
329 fnd_msg_pub.add;
330 x_return_status := fnd_api.g_ret_sts_unexp_error;
331 RAISE fnd_api.g_exc_unexpected_error;
332 EXCEPTION
333 WHEN NO_DATA_FOUND
334 THEN
335 NULL;
336 WHEN TOO_MANY_ROWS
337 THEN
338 fnd_message.set_name ('JTF', 'JTF_EC_DUPLICATE_ESCALATION');
339 fnd_msg_pub.add;
340 x_return_status := fnd_api.g_ret_sts_unexp_error;
341 RAISE fnd_api.g_exc_unexpected_error;
342 END;
343
344 --Created for BES enh 2660883
345 -- Code moved here to get the old values
346 begin
347 OPEN c_ref_orig (p_escalation_reference_id);
348 FETCH c_ref_orig INTO rec_ref_orig;
349 CLOSE c_ref_orig;
350
351 l_esc_ref_rec_old.task_reference_id := p_escalation_reference_id;
352 l_esc_ref_rec_old.object_type_code := rec_ref_orig.object_type_code;
353 l_esc_ref_rec_old.reference_code := rec_ref_orig.reference_code;
354 l_esc_ref_rec_old.object_id := rec_ref_orig.object_id;
355 -- Added for Bug # 3385990
356 l_esc_ref_rec_old.task_id := rec_ref_orig.task_id;
357
358 --
359 l_esc_ref_rec_new.task_reference_id := p_escalation_reference_id;
360 l_esc_ref_rec_new.object_type_code := p_object_type_code;
361 l_esc_ref_rec_new.reference_code := p_reference_code;
362 l_esc_ref_rec_new.object_id := p_object_id;
363 -- Added for Bug # 3385990
364 l_esc_ref_rec_new.task_id := p_task_id;
365 -- End of moved code
366
367 jtf_task_references_pub.update_references (
368 p_api_version => 1.0,
369 p_init_msg_list => fnd_api.g_false,
370 p_commit => fnd_api.g_false,
371 p_object_version_number => p_object_version_number,
372 p_task_reference_id => p_escalation_reference_id,
373 p_object_type_code => p_object_type_code,
374 p_object_name => p_object_name,
375 p_object_id => p_object_id,
376 p_object_details => p_object_details,
377 p_reference_code => p_reference_code,
378 p_usage => NULL,
379 x_return_status => x_return_status,
380 x_msg_data => x_msg_data,
381 x_msg_count => x_msg_count,
382 p_attribute1 => p_attribute1,
383 p_attribute2 => p_attribute2,
384 p_attribute3 => p_attribute3,
385 p_attribute4 => p_attribute4,
386 p_attribute5 => p_attribute5,
387 p_attribute6 => p_attribute6,
388 p_attribute7 => p_attribute7,
389 p_attribute8 => p_attribute8,
390 p_attribute9 => p_attribute9,
391 p_attribute10 => p_attribute10,
392 p_attribute11 => p_attribute11,
393 p_attribute12 => p_attribute12,
394 p_attribute13 => p_attribute13,
395 p_attribute14 => p_attribute14,
396 p_attribute15 => p_attribute15,
397 p_attribute_category => p_attribute_category
398 );
399
400 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
401 THEN
402 x_return_status := fnd_api.g_ret_sts_unexp_error;
403 RAISE fnd_api.g_exc_unexpected_error;
404 END IF;
405
406 IF fnd_api.to_boolean (p_commit)
407 THEN
408 COMMIT WORK;
409 END IF;
410
411 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
412
413
414 jtf_esc_wf_events_pvt.publish_update_escRef
415 (P_ESC_REF_REC_OLD => l_esc_ref_rec_old,
416 P_ESC_REF_REC_NEW => l_esc_ref_rec_new);
417
418 EXCEPTION when others then
419 null;
420 END;
421 --End BES enh 2660883
422
423 EXCEPTION
424 WHEN fnd_api.g_exc_unexpected_error
425 THEN
426 ROLLBACK TO update_references_pub;
427 x_return_status := fnd_api.g_ret_sts_unexp_error;
428 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
429 WHEN NO_DATA_FOUND
430 THEN
431 ROLLBACK TO update_references_pub;
432 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_OBJECT_NAME');
433 fnd_message.set_token ('P_OBJECT_TYPE_CODE', p_object_type_code);
434 fnd_message.set_token ('P_OBJECT_NAME', p_object_name);
435 fnd_msg_pub.add;
436 x_return_status := fnd_api.g_ret_sts_unexp_error;
437 WHEN OTHERS
438 THEN
439 ROLLBACK TO update_references_pub;
440 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
441 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
442 fnd_msg_pub.add;
443 x_return_status := fnd_api.g_ret_sts_unexp_error;
444 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
445 END;
446
447 ---------
448 ---------
449 ---- start of delete_references
450 ---------
451 ---------
452 PROCEDURE delete_references (
453 p_api_version IN NUMBER,
454 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
455 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
456 p_object_version_number IN NUMBER,
457 p_escalation_reference_id IN NUMBER,
458 x_return_status OUT NOCOPY VARCHAR2,
459 x_msg_data OUT NOCOPY VARCHAR2,
460 x_msg_count OUT NOCOPY NUMBER
461 )
462 IS
463 l_api_version CONSTANT NUMBER := 1.0;
464 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_REFERENCE';
465
466 --Created for BES enh 2660883
467 l_esc_ref_rec jtf_ec_references_pvt.Esc_Ref_rec;
468
469 -- Added Task_ID for Bug # 3385990
470 CURSOR c_ref_orig (reference_id IN NUMBER) IS
471 SELECT REFERENCE_CODE, OBJECT_TYPE_CODE, OBJECT_ID, TASK_ID
472 FROM JTF_TASK_REFERENCES_B
473 WHERE task_reference_id = reference_id;
474
475 rec_ref_orig c_ref_orig%ROWTYPE;
476
477 BEGIN
478 SAVEPOINT delete_task_reference_pub;
479 x_return_status := fnd_api.g_ret_sts_success;
480
481 IF NOT fnd_api.compatible_api_call (
482 l_api_version,
483 p_api_version,
484 l_api_name,
485 g_pkg_name
486 )
487 THEN
488 RAISE fnd_api.g_exc_unexpected_error;
489 END IF;
490
491 IF fnd_api.to_boolean (p_init_msg_list)
492 THEN
493 fnd_msg_pub.initialize;
494 END IF;
495
496 --Created for BES enh 2660883
497 OPEN c_ref_orig (p_escalation_reference_id);
498 FETCH c_ref_orig INTO rec_ref_orig;
499 CLOSE c_ref_orig;
500 --end
501
502 jtf_task_references_pvt.delete_references (
503 p_api_version => 1.0,
504 p_init_msg_list => fnd_api.g_false,
505 p_commit => fnd_api.g_false,
506 p_object_version_number => p_object_version_number,
507 p_task_reference_id => p_escalation_reference_id,
508 x_return_status => x_return_status,
509 x_msg_data => x_msg_data,
510 x_msg_count => x_msg_count
511 );
512
513 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
514 THEN
515 RAISE fnd_api.g_exc_unexpected_error;
516 END IF;
517
518 IF fnd_api.to_boolean (p_commit)
519 THEN
520 COMMIT WORK;
521 END IF;
522
523 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
524
525 --Created for BES enh 2660883
526 begin
527
528 l_esc_ref_rec.task_reference_id := p_escalation_reference_id;
529 l_esc_ref_rec.object_type_code := rec_ref_orig.object_type_code;
530 l_esc_ref_rec.reference_code := rec_ref_orig.reference_code;
531 l_esc_ref_rec.object_id := rec_ref_orig.object_id;
532 -- Added for Bug # 3385990
533 l_esc_ref_rec.task_id := rec_ref_orig.task_id;
534
535 jtf_esc_wf_events_pvt.publish_delete_escRef
536 (p_esc_ref_rec => l_esc_ref_rec);
537
538 EXCEPTION when others then
539 null;
540 END;
541 --End BES enh 2660883
542 EXCEPTION
543 WHEN fnd_api.g_exc_unexpected_error
544 THEN
545 ROLLBACK TO delete_task_reference_pub;
546 x_return_status := fnd_api.g_ret_sts_unexp_error;
547 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
548 WHEN OTHERS
549 THEN
550 ROLLBACK TO delete_task_reference_pub;
551 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
552 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
553 x_return_status := fnd_api.g_ret_sts_unexp_error;
554 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
555 END;
556
557 END;