[Home] [Help]
PACKAGE BODY: APPS.JTF_TASK_REFERENCES_PUB
Source
1 PACKAGE BODY jtf_task_references_pub AS
2 /* $Header: jtfptknb.pls 115.31 2002/12/04 01:45:33 cjang ship $ */
3 g_pkg_name VARCHAR2(30) := 'JTF_TASK_REFERENCES_PUB';
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_task_id IN NUMBER DEFAULT NULL,
10 p_task_number IN VARCHAR2 DEFAULT NULL,
11 p_object_type_code IN VARCHAR2 DEFAULT NULL,
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_task_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_id_column jtf_objects_b.select_id%TYPE;
42 l_name_column jtf_objects_b.select_name%TYPE;
43 l_from_clause jtf_objects_b.from_table%TYPE;
44 l_where_clause jtf_objects_b.where_clause%TYPE;
45 l_object_name jtf_task_references_b.object_name%TYPE;
46 l_object_id jtf_task_references_b.object_id%TYPE;
47 l_task_reference_id NUMBER;
48 l_rowid ROWID;
49 l_task_id jtf_tasks_b.task_id%TYPE := p_task_id;
50 l_task_number jtf_tasks_b.task_number%TYPE := p_task_number;
51 sql_stmt VARCHAR2(4000);
52 x CHAR;
53 l_object_type_code jtf_objects_b.object_code%TYPE := p_object_type_code;
54
55 CURSOR c_references
56 IS
57 SELECT select_id,
58 select_name,
59 from_table,
60 where_clause
61 FROM jtf_objects_vl
62 WHERE object_code = l_object_type_code;
63
64 CURSOR c_reference_codes
65 IS
66 SELECT 1
67 FROM fnd_lookups
68 WHERE lookup_type = 'JTF_TASK_REFERENCE_CODES'
69 AND lookup_code = p_reference_code;
70
71 references_rec c_references%ROWTYPE;
72 BEGIN
73
74 savepoint create_references_Pub ;
75
76 x_return_status := fnd_api.g_ret_sts_success;
77
78 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
79 THEN
80 RAISE fnd_api.g_exc_unexpected_error;
81 END IF;
82
83 IF fnd_api.to_boolean (p_init_msg_list)
84 THEN
85 fnd_msg_pub.initialize;
86 END IF;
87
88
89
90 jtf_task_utl.validate_task (
91 x_return_status => x_return_status,
92 p_task_id => l_task_id,
93 p_task_number => l_task_number,
94 x_task_id => l_task_id
95 );
96
97 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
98 THEN
99 x_return_status := fnd_api.g_ret_sts_unexp_error;
100 RAISE fnd_api.g_exc_unexpected_error;
101 END IF;
102
103
104
105 IF l_task_id IS NULL
106 THEN
107 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_TASK');
108 fnd_msg_pub.add;
109 x_return_status := fnd_api.g_ret_sts_unexp_error;
110 RAISE fnd_api.g_exc_unexpected_error;
111 END IF;
112 ----
113 ---- Commenting to use the common code from jtf_task_utl.
114 ---- Also, the validation was incorrect since it was validating the id from the name. rather than vide versa.
115 ----
116 ----
117
118 -------
119 ------- Validate source object details
120 -------
121 jtf_task_utl.validate_source_object (
122 p_object_code => p_object_type_code,
123 p_object_id => p_object_id ,
124 p_object_name => p_object_name,
125 x_return_status => x_return_status
126 );
127
128 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
129 THEN
130 x_return_status := fnd_api.g_ret_sts_unexp_error;
131 RAISE fnd_api.g_exc_unexpected_error;
132 END IF;
133
134
135
136 /* jtf_task_utl.validate_object_type (
137 p_object_code => l_object_type_code,
138 p_object_type_name => NULL,
139 x_return_status => x_return_status,
140 x_object_code => l_object_type_code
141 );
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
151
152 IF l_object_type_code IS NULL
153 THEN
154 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_OBJECT_CODE');
155 fnd_msg_pub.add;
156 x_return_status := fnd_api.g_ret_sts_unexp_error;
157 RAISE fnd_api.g_exc_unexpected_error;
158 END IF;
159
160
161
162 OPEN c_references;
163
164 FETCH c_references INTO l_id_column, l_name_column, l_from_clause, l_where_clause;
165
166
167
168
169
170 IF c_references%NOTFOUND
171 THEN
172 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_OBJECT_CODE');
173 fnd_message.set_token('P_OBJECT_CODE',p_object_type_code);
174 fnd_msg_pub.add;
175 RAISE fnd_api.g_exc_unexpected_error;
176 END IF;
177
178 */
179
180
181
182 /*
183
184 This part of code commented by GJASHNAN
185 Made Object Name as a mandatory parameter.
186
187 IF p_object_name IS NULL
188 THEN
189 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_OBJECT_NAME');
190 fnd_msg_pub.add;
191 RAISE fnd_api.g_exc_unexpected_error;
192 END IF;
193
194 */
195 /* SELECT DECODE (l_where_clause, NULL, ' ', l_where_clause || ' AND ')
196 INTO l_where_clause
197 FROM dual;
198 sql_stmt := ' SELECT ' ||
199 l_name_column ||
200 ' , ' ||
201 l_id_column ||
202 ' from ' ||
203 l_from_clause ||
204 ' where ' ||
205 l_where_clause ||
206 l_name_column ||
207 ' = ' ||
208 ''''||
209 p_object_name||
210 '''';
211
212 EXECUTE IMMEDIATE sql_stmt
213 INTO l_object_name, l_object_id;
214
215 IF p_object_id IS NOT NULL
216 THEN
217 IF l_object_id <> p_object_id
218 THEN
219 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_OBJECT_ID');
220 fnd_message.set_token('P_OBJECT_ID', p_object_id );
221 fnd_msg_pub.add;
222 RAISE fnd_api.g_exc_unexpected_error;
223 END IF;
224 END IF;
225 */
226
227
228
229 IF p_reference_code IS NOT NULL
230 THEN
231 OPEN c_reference_codes;
232 FETCH c_reference_codes INTO x;
233
234 IF c_reference_codes%NOTFOUND
235 THEN
236 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_REFER_CODE');
237 fnd_msg_pub.add;
238 RAISE fnd_api.g_exc_unexpected_error;
239 END IF;
240 END IF;
241
242 -- Parameter initialized to false to ignore error messages - Enh # 2102281
243 jtf_task_utl.g_show_error_for_dup_reference := False;
244 jtf_task_references_pvt.create_references (
245 p_api_version => 1.0,
246 p_init_msg_list => fnd_api.g_false,
247 p_commit => fnd_api.g_false,
248 p_task_id => l_task_id,
249 p_object_type_code => l_object_type_code,
250 p_object_name => p_object_name,
251 p_object_id => p_object_id,
252 p_object_details => p_object_details,
253 p_reference_code => p_reference_code,
254 p_usage => p_usage,
255 x_return_status => x_return_status,
256 x_task_reference_id => x_task_reference_id,
257 x_msg_count => x_msg_count ,
258 x_msg_data => x_msg_data,
259 p_attribute1 => p_attribute1 ,
260 p_attribute2 => p_attribute2 ,
261 p_attribute3 => p_attribute3 ,
262 p_attribute4 => p_attribute4 ,
263 p_attribute5 => p_attribute5 ,
264 p_attribute6 => p_attribute6 ,
265 p_attribute7 => p_attribute7 ,
266 p_attribute8 => p_attribute8 ,
267 p_attribute9 => p_attribute9 ,
268 p_attribute10 => p_attribute10 ,
269 p_attribute11 => p_attribute11 ,
270 p_attribute12 => p_attribute12 ,
271 p_attribute13 => p_attribute13 ,
272 p_attribute14 => p_attribute14 ,
273 p_attribute15 => p_attribute15,
274 p_attribute_category => p_attribute_category
275 );
276
277 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
278 THEN
279 x_return_status := fnd_api.g_ret_sts_unexp_error;
280 RAISE fnd_api.g_exc_unexpected_error;
281 END IF;
282
283 IF fnd_api.to_boolean (p_commit)
284 THEN
285 COMMIT WORK;
286 END IF;
287
288 if c_references%isopen then
289 close c_references ;
290 end if;
291
292
293 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
294
295
296
297
298 EXCEPTION
299 WHEN fnd_api.g_exc_unexpected_error
300 THEN
301
302 if c_references%isopen then
303 close c_references ;
304 end if;
305
306 rollback to create_references_Pub ;
307 x_return_status := fnd_api.g_ret_sts_unexp_error;
308 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
309 WHEN NO_DATA_FOUND
310 THEN
311
312 if c_references%isopen then
313 close c_references ;
314 end if;
315 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_OBJECT_NAME');
316 fnd_message.set_token('P_OBJECT_TYPE_CODE',p_object_type_code );
317 fnd_message.set_token('P_OBJECT_NAME', p_object_name );
318 fnd_msg_pub.add;
319 x_return_status := fnd_api.g_ret_sts_unexp_error;
320
321 WHEN OTHERS
322 THEN
323 if c_references%isopen then
324 close c_references ;
325 end if;
326 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
327 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
328 x_return_status := fnd_api.g_ret_sts_unexp_error;
329 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
330 END;
331 -------------------------------------------------
332 -------------------------------------------------
333 -------------------------------------------------
334 -------------------------------------------------
335 PROCEDURE lock_references (
336 p_api_version IN NUMBER,
337 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
338 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
339 p_task_reference_id IN NUMBER,
340 p_object_version_number IN NUMBER,
341 x_return_status OUT NOCOPY VARCHAR2,
342 x_msg_data OUT NOCOPY VARCHAR2,
343 x_msg_count OUT NOCOPY NUMBER
344 ) is
345 l_api_version CONSTANT NUMBER := 1.0;
346 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_REFERENCES';
347
348
349 Resource_Locked exception ;
350
351 PRAGMA EXCEPTION_INIT ( Resource_Locked , - 54 ) ;
352
353 begin
354 SAVEPOINT lock_references_pub;
355
356 x_return_status := fnd_api.g_ret_sts_success;
357
358 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
359 THEN
360 RAISE fnd_api.g_exc_unexpected_error;
361 END IF;
362
363 IF fnd_api.to_boolean (p_init_msg_list)
364 THEN
365 fnd_msg_pub.initialize;
366 END IF;
367
368 x_return_status := fnd_api.g_ret_sts_success;
369
370 jtf_task_references_pkg.lock_row(
371 x_task_reference_id => p_task_reference_id ,
372 x_object_version_number => p_object_version_number );
373
374
375 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
376 EXCEPTION
377 WHEN Resource_Locked then
378 ROLLBACK TO lock_references_pub;
379 fnd_message.set_name ('JTF', 'JTF_TASK_RESOURCE_LOCKED');
380 fnd_message.set_token ('P_LOCKED_RESOURCE', 'References');
381 fnd_msg_pub.add;
382 x_return_status := fnd_api.g_ret_sts_unexp_error;
383 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
384 WHEN fnd_api.g_exc_unexpected_error
385 THEN
386 ROLLBACK TO lock_references_pub;
387 x_return_status := fnd_api.g_ret_sts_unexp_error;
388 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
389 WHEN OTHERS
390 THEN
391 ROLLBACK TO lock_references_pub;
392 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
393 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
394 fnd_msg_pub.add;
395 x_return_status := fnd_api.g_ret_sts_unexp_error;
396 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
397 END;
398 -------------------------------------------------
399 -------------------------------------------------
400 -------------------------------------------------
401 -------------------------------------------------
402 /* PROCEDURE validate_source_object (
403 p_object_type_code IN VARCHAR2,
404 p_object_id IN NUMBER,
405 p_object_name IN VARCHAR2,
406 x_return_status OUT NOCOPY VARCHAR2
407 )
408 IS
409 CURSOR c_references
410 IS
411 SELECT select_id,
412 select_name,
413 from_table,
414 where_clause
415 FROM jtf_objects_vl
416 WHERE object_code = p_object_type_code;
417
418
419
420
421 l_id_column jtf_objects_b.select_id%TYPE;
422 l_name_column jtf_objects_b.select_name%TYPE;
423 l_from_clause jtf_objects_b.from_table%TYPE;
424 l_where_clause jtf_objects_b.where_clause%TYPE;
425 l_object_type_code jtf_tasks_b.source_object_type_code%TYPE := p_object_type_code;
426 l_object_name jtf_tasks_b.source_object_name%TYPE := p_object_name;
427 l_object_id jtf_tasks_b.source_object_id%TYPE := p_object_id;
428 sql_stmt VARCHAR2(2000);
429 BEGIN
430
431
432
433 x_return_status := fnd_api.g_ret_sts_success;
434 OPEN c_references;
435 FETCH c_references INTO l_id_column, l_name_column, l_from_clause, l_where_clause;
436
437
438 IF c_references%NOTFOUND
439 THEN
440
441 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_OBJECT_CODE');
442 fnd_message.set_token('P_OBJECT_TYPE_CODE',p_object_type_code);
443 fnd_msg_pub.add;
444 RAISE fnd_api.g_exc_unexpected_error;
445 END IF;
446
447
448 SELECT DECODE (l_where_clause, NULL, ' ', l_where_clause || ' AND ')
449 INTO l_where_clause
450 FROM dual;
451 sql_stmt := ' SELECT ' ||
452 l_name_column ||
453 ' , ' ||
454 l_id_column ||
455 ' from ' ||
456 l_from_clause ||
457 ' where ' ||
458 l_where_clause ||
459 l_name_column ||
460 ' = ' ||
461 p_object_name;
462
463 EXECUTE IMMEDIATE sql_stmt
464 INTO l_object_name, l_object_id;
465
466 IF p_object_id IS NOT NULL
467 THEN
468 IF p_object_id <> l_object_id
469 THEN
470 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_OBJECT_NAME');
471 fnd_message.set_token('P_OBJECT_TYPE_CODE',p_object_type_code );
472 fnd_message.set_token('P_OBJECT_NAME', p_object_name );
473 fnd_msg_pub.add;
474 x_return_status := fnd_api.g_ret_sts_unexp_error;
475 RAISE fnd_api.g_exc_unexpected_error;
476 END IF;
477 END IF;
478 EXCEPTION
479 WHEN fnd_api.g_exc_unexpected_error
480 THEN
481 x_return_status := fnd_api.g_ret_sts_unexp_error;
482 WHEN NO_DATA_FOUND
483 THEN
484 x_return_status := fnd_api.g_ret_sts_unexp_error;
485
486 WHEN OTHERS
487 THEN
488
489 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
490 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
491 x_return_status := fnd_api.g_ret_sts_unexp_error;
492 END;
493 */
494
495 PROCEDURE update_references (
496 p_api_version IN NUMBER,
497 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
498 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
499 p_object_version_number IN OUT NOCOPY NUMBER ,
500 p_task_reference_id IN NUMBER,
501 p_object_type_code IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
502 p_object_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
503 p_object_id IN NUMBER DEFAULT fnd_api.g_miss_num,
504 p_object_details IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
505 p_reference_code IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
506 p_usage IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
507 x_return_status OUT NOCOPY VARCHAR2,
508 x_msg_data OUT NOCOPY VARCHAR2,
509 x_msg_count OUT NOCOPY NUMBER,
510 p_attribute1 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
511 p_attribute2 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
512 p_attribute3 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
513 p_attribute4 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
514 p_attribute5 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
515 p_attribute6 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
516 p_attribute7 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
517 p_attribute8 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
518 p_attribute9 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
519 p_attribute10 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
520 p_attribute11 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
521 p_attribute12 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
522 p_attribute13 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
523 p_attribute14 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
524 p_attribute15 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
525 p_attribute_category IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char
526 )
527 IS
528 l_api_version CONSTANT NUMBER := 1.0;
529 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_REFERENCES';
530
531 l_task_id jtf_tasks_b.task_id%TYPE;
532 l_object_type_code jtf_objects_b.object_code%TYPE := p_object_type_code;
533 l_task_reference_id jtf_task_references_b.task_reference_id%TYPE := p_task_reference_id;
534 l_reference_code jtf_task_references_b.reference_code%TYPE := p_reference_code;
535 l_usage jtf_task_references_tl.usage%TYPE := p_usage;
536 l_object_name jtf_task_references_b.object_name%TYPE;
537 l_object_id jtf_task_references_b.object_id%TYPE;
538
539 CURSOR c_task_reference
540 IS
541 SELECT task_id,
542 DECODE (p_object_type_code, fnd_api.g_miss_char, object_type_code, p_object_type_code) object_type_code,
543 DECODE (p_object_name, fnd_api.g_miss_char, object_name, p_object_name) object_name,
544 DECODE (p_object_id, fnd_api.g_miss_num, object_id, p_object_id) object_id,
545 DECODE (p_object_details, fnd_api.g_miss_char, object_details, p_object_details) object_details,
546 DECODE (p_reference_code, fnd_api.g_miss_char, reference_code, p_reference_code) reference_code,
547 DECODE (p_usage, fnd_api.g_miss_char, usage, p_usage) usage,
548 decode( p_attribute1 , fnd_api.g_miss_char , attribute1 , p_attribute1 ) attribute1 ,
549 decode( p_attribute2 , fnd_api.g_miss_char , attribute2 , p_attribute2 ) attribute2 ,
550 decode( p_attribute3 , fnd_api.g_miss_char , attribute3 , p_attribute3 ) attribute3 ,
551 decode( p_attribute4 , fnd_api.g_miss_char , attribute4 , p_attribute4 ) attribute4 ,
552 decode( p_attribute5 , fnd_api.g_miss_char , attribute5 , p_attribute5 ) attribute5 ,
553 decode( p_attribute6 , fnd_api.g_miss_char , attribute6 , p_attribute6 ) attribute6 ,
554 decode( p_attribute7 , fnd_api.g_miss_char , attribute7 , p_attribute7 ) attribute7 ,
555 decode( p_attribute8 , fnd_api.g_miss_char , attribute8 , p_attribute8 ) attribute8 ,
556 decode( p_attribute9 , fnd_api.g_miss_char , attribute9 , p_attribute9 ) attribute9 ,
557 decode( p_attribute10 , fnd_api.g_miss_char , attribute10 , p_attribute10 ) attribute10 ,
558 decode( p_attribute11 , fnd_api.g_miss_char , attribute11 , p_attribute11 ) attribute11 ,
559 decode( p_attribute12 , fnd_api.g_miss_char , attribute12 , p_attribute12 ) attribute12 ,
560 decode( p_attribute13 , fnd_api.g_miss_char , attribute13 , p_attribute13 ) attribute13 ,
561 decode( p_attribute14 , fnd_api.g_miss_char , attribute14 , p_attribute14 ) attribute14 ,
562 decode( p_attribute15 , fnd_api.g_miss_char , attribute15 , p_attribute15 ) attribute15 ,
563 decode( p_attribute_category,fnd_api.g_miss_char,attribute_category,p_attribute_category) attribute_category
564 FROM jtf_task_references_vl
565 WHERE task_reference_id = p_task_reference_id;
566
567 task_references c_task_reference%ROWTYPE;
568 BEGIN
569
570
571
572 savepoint update_task_reference_pub ;
573
574 x_return_status := fnd_api.g_ret_sts_success;
575
576 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
577 THEN
578 RAISE fnd_api.g_exc_unexpected_error;
579 END IF;
580
581 IF fnd_api.to_boolean (p_init_msg_list)
582 THEN
583 fnd_msg_pub.initialize;
584 END IF;
585
586 IF l_task_reference_id IS NULL
587 THEN
588 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_REFER');
589 fnd_msg_pub.add;
590 x_return_status := fnd_api.g_ret_sts_unexp_error;
591 RAISE fnd_api.g_exc_unexpected_error;
592 END IF;
593
594 OPEN c_task_reference;
595 FETCH c_task_reference INTO task_references;
596
597 IF c_task_reference%NOTFOUND
598 THEN
599 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_REFER');
600 fnd_message.set_token('P_TASK_REFERENCE_ID',p_task_reference_id);
601 fnd_msg_pub.add;
602 x_return_status := fnd_api.g_ret_sts_unexp_error;
603 RAISE fnd_api.g_exc_unexpected_error;
604 END IF;
605
606
607
608 l_task_id := task_references.task_id;
609 /* --------
610 -------- Validate Object Type
611 --------
612 l_object_type_code := task_references.object_type_code;
613
614 jtf_task_utl.validate_object_type (
615 p_object_code => l_object_type_code,
616 p_object_type_name => NULL,
617 x_return_status => x_return_status,
618 x_object_code => l_object_type_code
619 );
620
621 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
622 THEN
623 x_return_status := fnd_api.g_ret_sts_unexp_error;
624 RAISE fnd_api.g_exc_unexpected_error;
625 END IF;
626
627
628 --------
629 -------- Validate object details
630 --------
631 l_object_name := task_references.object_name;
632
633
634 l_object_id := task_references.object_id;
635
636
637 validate_source_object(
638 p_object_type_code => l_object_type_code,
639 p_object_name => l_object_name,
640 p_object_id => l_object_id,
641 x_return_status => x_return_status
642 );
643
644 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
645 THEN
646 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_REFER_DETAILS');
647 fnd_msg_pub.add;
648 x_return_status := fnd_api.g_ret_sts_unexp_error;
649 RAISE fnd_api.g_exc_unexpected_error;
650 END IF;
651 */
652
653 -------
654 ------- Validate source object details
655 -------
656
657
658 jtf_task_utl.validate_source_object (
659 p_object_code => task_references.object_type_code,
660 p_object_id => task_references.object_id ,
661 p_object_name => task_references.object_name,
662 x_return_status => x_return_status
663 );
664
665 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
666 THEN
667 x_return_status := fnd_api.g_ret_sts_unexp_error;
668 RAISE fnd_api.g_exc_unexpected_error;
669 END IF;
670
671
672
673
674 --------
675 -------- Validate Reference code
676 --------
677 l_reference_code := task_references.reference_code;
678
679 IF ( l_reference_code IS NOT NULL
680 AND l_reference_code <> task_references.reference_code)
681 THEN
682 jtf_task_utl.validate_reference_codes (p_reference_code => l_reference_code, x_return_status => x_return_status);
683
684 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
685 THEN
686 x_return_status := fnd_api.g_ret_sts_unexp_error;
687 RAISE fnd_api.g_exc_unexpected_error;
688 END IF;
689 END IF;
690
691 l_usage := task_references.usage;
692
693 --- Call the private ;
694
695 -- Parameter initialized to false to ignore error messages - Enh # 2102281
696 jtf_task_utl.g_show_error_for_dup_reference := False;
697 jtf_task_references_pvt.update_references (
698 p_api_version => 1.0,
699 p_init_msg_list => fnd_api.g_false,
700 p_commit => fnd_api.g_false,
701 p_object_version_number => p_object_version_number,
702 p_task_reference_id => l_task_reference_id,
703 p_object_type_code => task_references.object_type_code,
704 p_object_name => task_references.object_name,
705 p_object_id => task_references.object_id,
706 p_object_details => p_object_details,
707 p_reference_code => l_reference_code,
708 p_usage => l_usage,
709 x_return_status => x_return_status,
710 x_msg_count => x_msg_count ,
711 x_msg_data => x_msg_data,
712 p_attribute1 => task_references.attribute1 ,
713 p_attribute2 => task_references.attribute2 ,
714 p_attribute3 => task_references.attribute3 ,
715 p_attribute4 => task_references.attribute4 ,
716 p_attribute5 => task_references.attribute5 ,
717 p_attribute6 => task_references.attribute6 ,
718 p_attribute7 => task_references.attribute7 ,
719 p_attribute8 => task_references.attribute8 ,
720 p_attribute9 => task_references.attribute9 ,
721 p_attribute10 => task_references.attribute10 ,
722 p_attribute11 => task_references.attribute11 ,
723 p_attribute12 => task_references.attribute12 ,
724 p_attribute13 => task_references.attribute13 ,
725 p_attribute14 => task_references.attribute14 ,
726 p_attribute15 => task_references.attribute15 ,
727 p_attribute_category => task_references.attribute_category
728 );
729
730 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
731 THEN
732 RAISE fnd_api.g_exc_unexpected_error;
733 END IF;
734
735 IF fnd_api.to_boolean (p_commit)
736 THEN
737 COMMIT WORK;
738 END IF;
739
740 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
741
742 if c_task_reference%isopen then
743 close c_task_reference ;
744 end if;
745
746
747 EXCEPTION
748 WHEN fnd_api.g_exc_unexpected_error
749 THEN
750 ROLLBACK TO update_task_reference_pub;
751 x_return_status := fnd_api.g_ret_sts_unexp_error;
752 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
753 if c_task_reference%isopen then
754 close c_task_reference ;
755 end if;
756
757 WHEN OTHERS
758 THEN
759 ROLLBACK TO update_task_reference_pub;
760 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
761 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
762 fnd_msg_pub.add;
763 x_return_status := fnd_api.g_ret_sts_unexp_error;
764 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
765 if c_task_reference%isopen then
766 close c_task_reference ;
767 end if;
768
769
770 END; ----- Update Task
771
772 -------------------------------------------------
773 -------------------------------------------------
774 -------------------------------------------------
775 -------------------------------------------------
776
777
778 PROCEDURE delete_references (
779 p_api_version IN NUMBER,
780 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
781 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
782 p_object_version_number IN NUMBER ,
783 p_task_reference_id IN NUMBER ,
784 x_return_status OUT NOCOPY VARCHAR2,
785 x_msg_data OUT NOCOPY VARCHAR2,
786 x_msg_count OUT NOCOPY NUMBER
787 )
788 IS
789 l_api_version CONSTANT NUMBER := 1.0;
790 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_REFERENCES';
791 BEGIN
792 SAVEPOINT delete_task_reference_pub;
793
794 x_return_status := fnd_api.g_ret_sts_success;
795
796 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
797 THEN
798 RAISE fnd_api.g_exc_unexpected_error;
799 END IF;
800
801 IF fnd_api.to_boolean (p_init_msg_list)
802 THEN
803 fnd_msg_pub.initialize;
804 END IF;
805
806 jtf_task_references_pub.lock_references
807 ( P_API_VERSION => 1.0,
808 P_INIT_MSG_LIST => fnd_api.g_false ,
809 P_COMMIT => fnd_api.g_false ,
810 P_TASK_reference_ID => p_task_reference_id ,
811 P_OBJECT_VERSION_NUMBER => p_object_version_number,
812 X_RETURN_STATUS => x_return_status ,
813 X_MSG_DATA => x_msg_data ,
814 X_MSG_COUNT => x_msg_count ) ;
815
816 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
817 THEN
818 x_return_status := fnd_api.g_ret_sts_unexp_error;
819 RAISE fnd_api.g_exc_unexpected_error;
820 END IF;
821
822 jtf_task_references_pvt.delete_references (
823 p_api_version => 1.0,
824 p_init_msg_list => fnd_api.g_false,
825 p_commit => fnd_api.g_false,
826 p_object_version_number => p_object_version_number,
827 p_task_reference_id => p_task_reference_id,
828 x_return_status => x_return_status,
829 x_msg_data => x_msg_data,
830 x_msg_count => x_msg_count
831 );
832
833 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
834 THEN
835 RAISE fnd_api.g_exc_unexpected_error;
836 END IF;
837
838 IF fnd_api.to_boolean (p_commit)
839 THEN
840 COMMIT WORK;
841 END IF;
842
843 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
844 EXCEPTION
845 WHEN fnd_api.g_exc_unexpected_error
846 THEN
847 ROLLBACK TO delete_task_reference_pub;
848 x_return_status := fnd_api.g_ret_sts_unexp_error;
849 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
850 WHEN OTHERS
851 THEN
852 ROLLBACK TO delete_task_reference_pub;
853 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
854 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
855 fnd_msg_pub.add;
856 x_return_status := fnd_api.g_ret_sts_unexp_error;
857 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
858 END;
859 END;