[Home] [Help]
PACKAGE BODY: APPS.JTF_TASK_REFERENCES_PVT
Source
1 PACKAGE BODY jtf_task_references_pvt AS
2 /* $Header: jtfvtknb.pls 120.1 2005/07/02 01:46:00 appldev ship $ */
3 g_pkg_name constant varchar2(30) := 'JTF_TASK_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_task_id IN NUMBER DEFAULT NULL,
10 p_object_type_code IN VARCHAR2 DEFAULT NULL,
11 p_object_name IN VARCHAR2 DEFAULT NULL,
12 p_object_id IN NUMBER DEFAULT NULL,
13 p_object_details IN VARCHAR2 DEFAULT NULL,
14 p_reference_code IN VARCHAR2 DEFAULT NULL,
15 p_usage IN VARCHAR2 DEFAULT NULL,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2,
19 x_task_reference_id OUT NOCOPY NUMBER,
20 p_attribute1 IN VARCHAR2 DEFAULT null ,
21 p_attribute2 IN VARCHAR2 DEFAULT null ,
22 p_attribute3 IN VARCHAR2 DEFAULT null ,
23 p_attribute4 IN VARCHAR2 DEFAULT null ,
24 p_attribute5 IN VARCHAR2 DEFAULT null ,
25 p_attribute6 IN VARCHAR2 DEFAULT null ,
26 p_attribute7 IN VARCHAR2 DEFAULT null ,
27 p_attribute8 IN VARCHAR2 DEFAULT null ,
28 p_attribute9 IN VARCHAR2 DEFAULT null ,
29 p_attribute10 IN VARCHAR2 DEFAULT null ,
30 p_attribute11 IN VARCHAR2 DEFAULT null ,
31 p_attribute12 IN VARCHAR2 DEFAULT null ,
32 p_attribute13 IN VARCHAR2 DEFAULT null ,
33 p_attribute14 IN VARCHAR2 DEFAULT null ,
34 p_attribute15 IN VARCHAR2 DEFAULT null ,
35 p_attribute_category IN VARCHAR2 DEFAULT null
36
37 )
38 IS
39 l_api_version CONSTANT NUMBER := 1.0;
40 l_task_reference_id jtf_task_references_b.task_reference_id%TYPE;
41 l_rowid ROWID;
42 l_api_name VARCHAR2(30) := 'CREATE_REFERENCES';
43
44 CURSOR c_jtf_task_references (
45 l_rowid IN ROWID
46 )
47 IS
48 SELECT 1
49 FROM jtf_task_references_b
50 WHERE ROWID = l_rowid;
51
52 x CHAR;
53
54 BEGIN
55
56 savepoint create_references_pvt ;
57
58 x_return_status := fnd_api.g_ret_sts_success;
59
60 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
61 THEN
62 RAISE fnd_api.g_exc_unexpected_error;
63 END IF;
64
65 IF fnd_api.to_boolean (p_init_msg_list)
66 THEN
67 fnd_msg_pub.initialize;
68 END IF;
69
70 -- 2102281
71 if not (jtf_task_utl.check_duplicate_reference(p_task_id, p_object_id, p_object_type_code))
72 then
73 if (jtf_task_utl.g_show_error_for_dup_reference)
74 then
75 fnd_message.set_name('JTF','JTF_TASK_DUPLICATE_REF');
76 fnd_message.set_token('P_OBJECT_NAME',p_object_name);
77 fnd_message.set_token('P_OBJECT_TYPE',p_object_type_code);
78 fnd_msg_pub.add;
79 RAISE fnd_api.g_exc_unexpected_error;
80 else
81 jtf_task_utl.g_show_error_for_dup_reference := True;
82 return;
83 end if;
84 end if;
85
86
87
88 SELECT jtf_task_references_s.nextval
89 INTO l_task_reference_id
90 FROM dual;
91
92 /* Made a call to the function jtf_task_utl.check_truncation, since it was inserting Party Name
93 which was greater than 80 characters */
94 jtf_task_references_pkg.insert_row (
95 x_rowid => l_rowid,
96 x_task_reference_id => l_task_reference_id,
97 x_task_id => p_task_id,
98 x_object_type_code => p_object_type_code,
99 x_object_name => jtf_task_utl.check_truncation(p_object_name),
100 x_object_id => p_object_id,
101 x_object_details => NVL(p_object_details, -- For fixing bug 2896532
102 jtf_task_utl_ext.get_object_details(
103 p_object_type_code
104 ,p_object_id)),
105 x_reference_code => p_reference_code,
106 x_usage => p_usage,
107 x_creation_date => SYSDATE,
108 x_created_by => jtf_task_utl.created_by,
109 x_last_update_date => SYSDATE,
110 x_last_updated_by => jtf_task_utl.updated_by,
111 x_last_update_login => jtf_task_utl.login_id,
112 x_attribute1 => p_attribute1 ,
113 x_attribute2 => p_attribute2 ,
114 x_attribute3 => p_attribute3 ,
115 x_attribute4 => p_attribute4 ,
116 x_attribute5 => p_attribute5 ,
117 x_attribute6 => p_attribute6 ,
118 x_attribute7 => p_attribute7 ,
119 x_attribute8 => p_attribute8 ,
120 x_attribute9 => p_attribute9 ,
121 x_attribute10 => p_attribute10 ,
122 x_attribute11 => p_attribute11 ,
123 x_attribute12 => p_attribute12 ,
124 x_attribute13 => p_attribute13 ,
125 x_attribute14 => p_attribute14 ,
126 x_attribute15 => p_attribute15,
127 x_attribute_category => p_attribute_category
128 );
129
130 OPEN c_jtf_task_references (l_rowid);
131 FETCH c_jtf_task_references INTO x;
132
133 IF c_jtf_task_references%NOTFOUND
134 THEN
135 fnd_message.set_name ('JTF', 'JTF_TASK_ERROR_CREATING_REFER');
136 fnd_msg_pub.add;
137 x_return_status := fnd_api.g_ret_sts_unexp_error;
138 RAISE fnd_api.g_exc_unexpected_error;
139 ELSE
140 x_task_reference_id := l_task_reference_id;
141 END IF;
142
143
144 IF fnd_api.to_boolean (p_commit)
145 THEN
146 COMMIT WORK;
147 END IF;
148
149 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
150
151 EXCEPTION
152 WHEN fnd_api.g_exc_unexpected_error
153 THEN
154 ROLLBACK TO create_references_pvt;
155 x_return_status := fnd_api.g_ret_sts_unexp_error;
156 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
157 WHEN OTHERS
158 THEN
159 ROLLBACK TO create_references_pvt;
160 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
161 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
162 fnd_msg_pub.add;
163 x_return_status := fnd_api.g_ret_sts_unexp_error;
164 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
165 END;
166
167 PROCEDURE update_references (
168 p_api_version IN NUMBER,
169 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
170 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
171 p_object_version_number in OUT NOCOPY number,
172 p_task_reference_id IN NUMBER,
173 p_object_type_code IN VARCHAR2 DEFAULT NULL,
174 p_object_name IN VARCHAR2 DEFAULT NULL,
175 p_object_id IN NUMBER DEFAULT NULL,
176 p_object_details IN VARCHAR2 DEFAULT NULL,
177 p_reference_code IN VARCHAR2 DEFAULT NULL,
178 p_usage IN VARCHAR2 DEFAULT NULL,
179 x_return_status OUT NOCOPY VARCHAR2,
180 x_msg_count OUT NOCOPY NUMBER,
181 x_msg_data OUT NOCOPY VARCHAR2,
182 p_attribute1 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
183 p_attribute2 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
184 p_attribute3 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
185 p_attribute4 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
186 p_attribute5 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
187 p_attribute6 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
188 p_attribute7 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
189 p_attribute8 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
190 p_attribute9 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
191 p_attribute10 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
192 p_attribute11 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
193 p_attribute12 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
194 p_attribute13 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
195 p_attribute14 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
196 p_attribute15 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
197 p_attribute_category IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char
198 )
199 IS
200 l_api_name VARCHAR2(30) := 'UPDATE_REFERENCES';
201 l_api_version CONSTANT NUMBER := 1.0;
202 l_task_id jtf_tasks_b.task_id%TYPE;
203 l_object_type_code jtf_objects_b.object_code%TYPE;
204 l_task_reference_id jtf_task_references_b.task_reference_id%TYPE := p_task_reference_id;
205 l_reference_code jtf_task_references_b.reference_code%TYPE;
206 l_usage jtf_task_references_tl.usage%TYPE;
207 l_object_name jtf_task_references_b.object_name%TYPE;
208 l_object_id jtf_task_references_b.object_id%TYPE;
209 l_object_details jtf_task_references_b.object_details%TYPE;
210
211 CURSOR c_task_reference
212 IS
213 SELECT task_id,
214 DECODE (p_task_reference_id, fnd_api.g_miss_num, task_reference_id, p_task_reference_id) task_reference_id,
215 DECODE (p_object_type_code, fnd_api.g_miss_char, object_type_code, p_object_type_code) object_type_code,
216 DECODE (p_object_name, fnd_api.g_miss_char, object_name, p_object_name) object_name,
217 DECODE (p_object_id, fnd_api.g_miss_num, object_id, p_object_id) object_id,
218 DECODE (p_object_details, fnd_api.g_miss_char, object_details, p_object_details) object_details,
219 DECODE (p_reference_code, fnd_api.g_miss_char, reference_code, p_reference_code) reference_code,
220 DECODE (p_usage, fnd_api.g_miss_char, usage, p_usage) usage,
221 decode( p_attribute1 , fnd_api.g_miss_char , attribute1 , p_attribute1 ) attribute1 ,
222 decode( p_attribute2 , fnd_api.g_miss_char , attribute2 , p_attribute2 ) attribute2 ,
223 decode( p_attribute3 , fnd_api.g_miss_char , attribute3 , p_attribute3 ) attribute3 ,
224 decode( p_attribute4 , fnd_api.g_miss_char , attribute4 , p_attribute4 ) attribute4 ,
225 decode( p_attribute5 , fnd_api.g_miss_char , attribute5 , p_attribute5 ) attribute5 ,
226 decode( p_attribute6 , fnd_api.g_miss_char , attribute6 , p_attribute6 ) attribute6 ,
227 decode( p_attribute7 , fnd_api.g_miss_char , attribute7 , p_attribute7 ) attribute7 ,
228 decode( p_attribute8 , fnd_api.g_miss_char , attribute8 , p_attribute8 ) attribute8 ,
229 decode( p_attribute9 , fnd_api.g_miss_char , attribute9 , p_attribute9 ) attribute9 ,
230 decode( p_attribute10 , fnd_api.g_miss_char , attribute10 , p_attribute10 ) attribute10 ,
231 decode( p_attribute11 , fnd_api.g_miss_char , attribute11 , p_attribute11 ) attribute11 ,
232 decode( p_attribute12 , fnd_api.g_miss_char , attribute12 , p_attribute12 ) attribute12 ,
233 decode( p_attribute13 , fnd_api.g_miss_char , attribute13 , p_attribute13 ) attribute13 ,
234 decode( p_attribute14 , fnd_api.g_miss_char , attribute14 , p_attribute14 ) attribute14 ,
235 decode( p_attribute15 , fnd_api.g_miss_char , attribute15 , p_attribute15 ) attribute15 ,
236 decode( p_attribute_category,fnd_api.g_miss_char,attribute_category,p_attribute_category) attribute_category
237 FROM jtf_task_references_vl
238 WHERE task_reference_id = l_task_reference_id;
239
240 task_references c_task_reference%ROWTYPE;
241 BEGIN
242
243 savepoint update_task_reference_pvt ;
244
245 x_return_status := fnd_api.g_ret_sts_success;
246
247
248 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
249 THEN
250 RAISE fnd_api.g_exc_unexpected_error;
251 END IF;
252
253 IF fnd_api.to_boolean (p_init_msg_list)
254 THEN
255 fnd_msg_pub.initialize;
256 END IF;
257
258
259
260 IF l_task_reference_id IS NULL
261 THEN
262 fnd_message.set_name ('JTF', 'JTF_TASK_MISS_REFER');
263
264 fnd_msg_pub.add;
265 x_return_status := fnd_api.g_ret_sts_unexp_error;
266 RAISE fnd_api.g_exc_unexpected_error;
267 END IF;
268
269 OPEN c_task_reference;
270 FETCH c_task_reference INTO task_references;
271
272 IF c_task_reference%NOTFOUND
273 THEN
274 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_REFER');
275 fnd_message.set_token('P_TASK_REFERENCE_ID',p_task_reference_id);
276 fnd_msg_pub.add;
277 x_return_status := fnd_api.g_ret_sts_unexp_error;
278 RAISE fnd_api.g_exc_unexpected_error;
279 END IF;
280
281 l_task_id := task_references.task_id;
282 l_reference_code := task_references.reference_code;
283 l_object_type_code := task_references.object_type_code;
284 l_object_id := task_references.object_id;
285
286 /* Made a call to the function jtf_task_utl.check_truncation, since it was inserting Party Name
287 which was greater than 80 characters */
288
289 l_object_name := jtf_task_utl.check_truncation(task_references.object_name);
290 l_object_details := task_references.object_details;
291 l_reference_code := task_references.reference_code;
292 l_usage := task_references.usage;
293
294 -- 2102281
295 /*
296 Bug 3360228
297 For update, calling jtf_task_utl_ext.check_dup_reference_for_update for
298 checking duplicates instead of.
299 */
300 if not (jtf_task_utl_ext.check_dup_reference_for_update(l_task_reference_id, l_task_id, l_object_id, l_object_type_code))
301 then
302 if (jtf_task_utl.g_show_error_for_dup_reference)
303 then
304 fnd_message.set_name('JTF','JTF_TASK_DUPLICATE_REF');
305 fnd_message.set_token('P_OBJECT_NAME',l_object_name);
306 fnd_message.set_token('P_OBJECT_TYPE',l_object_type_code);
307 fnd_msg_pub.add;
308 RAISE fnd_api.g_exc_unexpected_error;
309 else
310 jtf_task_utl.g_show_error_for_dup_reference := True;
311 return;
312 end if;
313 end if;
314
315 jtf_task_references_pub.lock_references
316 ( P_API_VERSION => 1.0,
317 P_INIT_MSG_LIST => fnd_api.g_false ,
318 P_COMMIT => fnd_api.g_false ,
319 P_TASK_reference_ID => l_task_reference_id ,
320 P_OBJECT_VERSION_NUMBER => p_object_version_number,
321 X_RETURN_STATUS => x_return_status ,
322 X_MSG_DATA => x_msg_data ,
323 X_MSG_COUNT => x_msg_count ) ;
324
325 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
326 THEN
327 x_return_status := fnd_api.g_ret_sts_unexp_error;
328 RAISE fnd_api.g_exc_unexpected_error;
329 END IF;
330
331 p_object_version_number := p_object_version_number + 1 ;
332
333 jtf_task_references_pkg.update_row (
334 x_task_reference_id => l_task_reference_id,
335 x_task_id => l_task_id,
336 x_object_type_code => l_object_type_code,
337 x_object_name => l_object_name,
338 x_object_id => l_object_id,
339 x_object_details => l_object_details,
340 x_reference_code => l_reference_code,
341 x_attribute1 => task_references.attribute1 ,
342 x_attribute2 => task_references.attribute2 ,
343 x_attribute3 => task_references.attribute3 ,
344 x_attribute4 => task_references.attribute4 ,
345 x_attribute5 => task_references.attribute5 ,
346 x_attribute6 => task_references.attribute6 ,
347 x_attribute7 => task_references.attribute7 ,
348 x_attribute8 => task_references.attribute8 ,
349 x_attribute9 => task_references.attribute9 ,
350 x_attribute10 => task_references.attribute10 ,
351 x_attribute11 => task_references.attribute11 ,
352 x_attribute12 => task_references.attribute12 ,
353 x_attribute13 => task_references.attribute13 ,
354 x_attribute14 => task_references.attribute14 ,
355 x_attribute15 => task_references.attribute15 ,
356 x_attribute_category => task_references.attribute_category,
357 x_usage => l_usage,
358 x_last_update_date => SYSDATE,
359 x_last_updated_by => jtf_task_utl.updated_by,
360 x_last_update_login => jtf_task_utl.login_id,
361 x_object_version_number => p_object_version_number
362 );
363
364 EXCEPTION
365 WHEN fnd_api.g_exc_unexpected_error
366 THEN
367 ROLLBACK TO update_task_reference_pvt;
368 x_return_status := fnd_api.g_ret_sts_unexp_error;
369 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
370 WHEN OTHERS
371 THEN
372 ROLLBACK TO update_task_reference_pvt;
373 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
374 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
375 fnd_msg_pub.add;
376 x_return_status := fnd_api.g_ret_sts_unexp_error;
377 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
378 END;
379
380
381 PROCEDURE delete_references (
382 p_api_version IN NUMBER,
383 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
384 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
385 p_object_version_number in number,
386 p_task_reference_id IN NUMBER,
387 x_return_status OUT NOCOPY VARCHAR2,
388 x_msg_data OUT NOCOPY VARCHAR2,
389 x_msg_count OUT NOCOPY NUMBER,
390 p_from_task_api IN VARCHAR2 DEFAULT 'N'
391 )
392 IS
393 l_api_version CONSTANT NUMBER := 1.0;
394 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_REFERENCES';
395
396
397 CURSOR c_jtf_task_ref_delete
398 IS
399 SELECT task_id, object_id
400 FROM jtf_task_references_b
401 where task_reference_id = p_task_reference_id ;
402
403 l_task_id jtf_task_references_b.task_id%type;
404 l_object_id jtf_task_references_b.object_id%type;
405
406 BEGIN
407
408 SAVEPOINT delete_task_reference_pvt;
409
410 x_return_status := fnd_api.g_ret_sts_success;
411
412 IF fnd_api.to_boolean (p_init_msg_list)
413 THEN
414 fnd_msg_pub.initialize;
415 END IF;
416
417 -- 2102281
418
419 OPEN c_jtf_task_ref_delete ;
420
421 FETCH c_jtf_task_ref_delete INTO l_task_id, l_object_id;
422
423 IF (c_jtf_task_ref_delete%NOTFOUND) THEN
424 fnd_message.set_name ('JTF', 'JTF_TASK_REFERENCE_NOT_FOUND');
425 fnd_msg_pub.add;
426 RAISE fnd_api.g_exc_unexpected_error;
427 END IF;
428
429
430 CLOSE c_jtf_task_ref_delete ;
431
432 --By pass this check if the call is made from JTF_TASKS_PVT.DELETE_TASK
433 --JTF_TASKS_PVT calls this API with p_from_task_api ='Y'. Bug number 3995359
434
435 IF (p_from_task_api ='N') THEN
436
437 if not (jtf_task_utl.check_reference_delete(l_task_id, l_object_id))
438 THEN
439 if not (jtf_task_utl.g_show_error_for_dup_reference) then
440 jtf_task_utl.g_show_error_for_dup_reference := True;
441 end if;
442
443 --The API returns the message JTF_TASK_ERROR_NO_REFERENCES when you try to
444 --delete references which are created automically for the customer when the
445 --task is created. Refer to bug 3875523.
446
447 fnd_message.set_name ('JTF', 'JTF_TASK_ERROR_REF_DELETE');
448 fnd_msg_pub.add;
449 x_return_status := fnd_api.g_ret_sts_error;
450 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data =>x_msg_data);
451 return;
452
453 end if;
454
455
456 END IF;
457
458 jtf_task_references_pkg.lock_row(
459 X_TASK_REFERENCE_ID =>p_task_reference_id,
460 X_OBJECT_VERSION_NUMBER =>p_object_version_number);
461
462 jtf_task_references_pkg.delete_row (
463 x_task_reference_id => p_task_reference_id);
464
465
466 IF fnd_api.to_boolean (p_commit)
467 THEN
468 COMMIT WORK;
469 END IF;
470
471
472 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
473
474 EXCEPTION
475
476 WHEN NO_DATA_FOUND
477 THEN
478 ROLLBACK TO delete_task_reference_pvt;
479 fnd_message.set_name ('JTF', 'JTF_TASK_ERROR_DELETING_REFER');
480 fnd_msg_pub.add;
481 x_return_status := fnd_api.g_ret_sts_unexp_error;
482 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
483
484 WHEN fnd_api.g_exc_unexpected_error
485 THEN
486 ROLLBACK TO delete_task_reference_pvt;
487 if (c_jtf_task_ref_delete%ISOPEN) THEN
488 CLOSE c_jtf_task_ref_delete;
489 END IF;
490 x_return_status := fnd_api.g_ret_sts_unexp_error;
491 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
492
493 WHEN OTHERS
494 THEN
495 ROLLBACK TO delete_task_reference_pvt;
496 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
497 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
498 fnd_msg_pub.add;
499 x_return_status := fnd_api.g_ret_sts_unexp_error;
500 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
501 END;
502 END;