[Home] [Help]
PACKAGE BODY: APPS.JTF_TASK_DEPENDENCY_PVT
Source
1 PACKAGE BODY jtf_task_dependency_pvt AS
2 /* $Header: jtfvtkeb.pls 120.3 2006/02/22 03:37:52 sbarat ship $ */
3 g_pkg_name VARCHAR2(30) := 'JTF_TASK_DEPENDENCY_PVT';
4
5
6 function validate_dependency(p_task_id in number,
7 p_dependent_on_task_id in number,
8 p_dependency_type_code in varchar2,
9 p_adjustment_time in number,
10 p_adjustment_time_uom in varchar2,
11 p_validated_flag in varchar2 default 'N'
12 )
13 return varchar2
14 is
15
16 -- Cursor for getting scheduled_start_date and scheduled_end_date
17 cursor c_task_dates(l_task_id in number)
18 is
19 select scheduled_start_date ssd, scheduled_end_date sed
20 from jtf_tasks_b
21 where task_id = l_task_id;
22
23 l_child_start_date date;
24 l_master_start_date date;
25 l_child_end_date date;
26 l_master_end_date date;
27
28 begin
29
30 -- If validated_flag is null or not 'Y', simply do nothing.
31 if (p_validated_flag is null or p_validated_flag <> 'Y')
32 then
33 return fnd_api.g_ret_sts_success;
34 end if;
35
36 -- Get the scheduled dates for task
37 open c_task_dates(p_task_id);
38 fetch c_task_dates into l_child_start_date, l_child_end_date;
39 close c_task_dates;
40
41 -- Get the scheduled dates for the dependent task of the task
42 open c_task_dates(p_dependent_on_task_id);
43 fetch c_task_dates into l_master_start_date, l_master_end_date;
44 close c_task_dates;
45
46 -- If scheduled_start_date and scheduled_end_date for
47 -- parent task and child task are null, do nothing.
48 if (l_child_start_date is null and
49 l_master_start_date is null and
50 l_child_end_date is null and
51 l_master_end_date is null)
52 then
53 return fnd_api.g_ret_sts_success;
54 end if;
55
56 -- Dependency type code is 'SS'
57 if (p_dependency_type_code = 'SS' and
58 l_child_start_date is not null and
59 l_master_start_date is not null)
60 then
61 l_master_start_date :=
62 JTF_TASK_UTL_EXT.adjust_date(l_master_start_date,
63 p_adjustment_time,
64 p_adjustment_time_uom);
65
66 if l_master_start_date > l_child_start_date
67 then
68 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_DEPEND_S2S');
69 fnd_msg_pub.add;
70 raise fnd_api.g_exc_error;
71 end if;
72
73 -- Dependency type code is 'FF'
74 elsif (p_dependency_type_code = 'FF' and
75 l_child_end_date is not null and
76 l_master_end_date is not null)
77 then
78 l_master_end_date :=
79 JTF_TASK_UTL_EXT.adjust_date(l_master_end_date,
80 p_adjustment_time,
81 p_adjustment_time_uom);
82
83 if (l_master_end_date > l_child_end_date)
84 then
85 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_DEPEND_F2F');
86 fnd_msg_pub.add;
87 raise fnd_api.g_exc_error;
88 end if;
89
90 -- Dependency type code is 'FS'
91 elsif (p_dependency_type_code = 'FS' and
92 l_child_end_date is not null and
93 l_master_start_date is not null)
94 then
95 l_master_end_date :=
96 JTF_TASK_UTL_EXT.adjust_date(l_master_end_date,
97 p_adjustment_time,
98 p_adjustment_time_uom);
99
100 if (l_master_end_date > l_child_start_date)
101 then
102 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_DEPEND_F2S');
103 fnd_msg_pub.add;
104 raise fnd_api.g_exc_error;
105 end if;
106
107 -- Dependency type code is 'SF'
108 elsif (p_dependency_type_code = 'SF' and
109 l_child_start_date is not null and
110 l_master_end_date is not null)
111 then
112 l_master_start_date :=
113 JTF_TASK_UTL_EXT.adjust_date(l_master_start_date,
114 p_adjustment_time,
115 p_adjustment_time_uom);
116
117 if (l_master_start_date > l_child_end_date)
118 then
119 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_DEPEND_S2F');
120 fnd_msg_pub.add;
121 raise fnd_api.g_exc_error;
122 end if;
123 end if;
124
125 return fnd_api.g_ret_sts_success;
126
127 exception
128 when fnd_api.g_exc_error
129 then
130 return fnd_api.g_ret_sts_error;
131 when others
132 then
133 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
134 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
135 fnd_msg_pub.add;
136 return fnd_api.g_ret_sts_unexp_error;
137 end;
138
139
140 function validate_task_dependency (p_task_id in number,
141 p_dependent_on_task_id in number,
142 p_dependency_id in number,
143 p_template_flag in varchar2)
144 return varchar2
145 is
146 x char;
147 l_source_object_type_code jtf_tasks_b.source_object_type_code%TYPE;
148 l_source_object_id jtf_tasks_b.source_object_id%TYPE;
149
150 -- Cursor for checking if there is a duplicate dependency
151 cursor c_check_dup_temp_y (l_task_id in number,
152 l_dependent_on_task_id in number,
153 l_dependency_id in number)
154 is
155 select 1
156 from jtf_task_depends
157 where task_id = l_task_id
158 and dependent_on_task_id = l_dependent_on_task_id
159 and dependency_id <> l_dependency_id
160 and template_flag = 'Y';
161
162 -- Cursor for checking if there is a duplicate dependency
163 cursor c_check_dup_temp_n (l_task_id in number,
164 l_dependent_on_task_id in number,
165 l_dependency_id in number)
166 is
167 select 1
168 from jtf_task_depends
169 where task_id = l_task_id
170 and dependent_on_task_id = l_dependent_on_task_id
171 and dependency_id <> l_dependency_id
172 and (template_flag = 'N' or template_flag is null);
173
174 -- Cursor for checking if there is a cyclic chain dependency
175 cursor c_check_cyc_temp_y (l_dependency_id in number)
176 is
177 select 1
178 from jtf_task_depends
179 where task_id = p_dependent_on_task_id
180 and dependency_id <> l_dependency_id
181 connect by prior task_id = dependent_on_task_id
182 start with dependent_on_task_id = p_task_id
183 and template_flag = 'Y';
184
185 -- Cursor for checking if there is a cyclic chain dependency
186 cursor c_check_cyc_temp_n (l_dependency_id in number)
187 is
188 select 1
189 from jtf_task_depends
190 where task_id = p_dependent_on_task_id
191 and dependency_id <> l_dependency_id
192 connect by prior task_id = dependent_on_task_id
193 start with dependent_on_task_id = p_task_id
194 and (template_flag = 'N' or template_flag is null);
195
196 l_return_status varchar2(1):= fnd_api.g_ret_sts_success;
197
198 begin
199
200 if p_task_id = p_dependent_on_task_id
201 then
202 l_return_status := fnd_api.g_ret_sts_error;
203 fnd_message.set_name ('JTF', 'JTF_TASK_ITSELF_DEPENDS');
204 fnd_msg_pub.add;
205 raise fnd_api.g_exc_error;
206 end if;
207
208 if (p_template_flag = 'Y')
209 then
210 open c_check_dup_temp_y(p_task_id, p_dependent_on_task_id, p_dependency_id);
211 fetch c_check_dup_temp_y into x;
212
213 if c_check_dup_temp_y%found
214 then
215 close c_check_dup_temp_y;
216 l_return_status := fnd_api.g_ret_sts_error;
217 fnd_message.set_name ('JTF', 'JTF_TASK_DEPENDS_EXISTS');
218 fnd_msg_pub.add;
219 raise fnd_api.g_exc_error;
220 end if;
221 close c_check_dup_temp_y;
222
223 open c_check_dup_temp_y(p_dependent_on_task_id, p_task_id, p_dependency_id);
224 fetch c_check_dup_temp_y into x;
225
226 if c_check_dup_temp_y%found
227 then
228 close c_check_dup_temp_y;
229 l_return_status := fnd_api.g_ret_sts_error;
230 fnd_message.set_name ('JTF', 'JTF_TASK_DEPENDS_RVERSE_EXISTS');
231 fnd_msg_pub.add;
232 raise fnd_api.g_exc_error;
233 end if;
234 close c_check_dup_temp_y;
235
236 open c_check_cyc_temp_y(p_dependency_id);
237 fetch c_check_cyc_temp_y into x;
238
239 if c_check_cyc_temp_y%found
240 then
241 close c_check_cyc_temp_y;
242 l_return_status := fnd_api.g_ret_sts_unexp_error;
243 fnd_message.set_name ('JTF', 'JTF_TASK_DEPENDS_CYCLICAL');
244 fnd_msg_pub.add;
245 raise fnd_api.g_exc_error;
246 end if;
247 close c_check_cyc_temp_y;
248 elsif (p_template_flag is null or p_template_flag = 'N')
249 then
250 open c_check_dup_temp_n(p_task_id, p_dependent_on_task_id, p_dependency_id);
251 fetch c_check_dup_temp_n into x;
252
253 if c_check_dup_temp_n%found
254 then
255 close c_check_dup_temp_n;
256 l_return_status := fnd_api.g_ret_sts_error;
257 fnd_message.set_name ('JTF', 'JTF_TASK_DEPENDS_EXISTS');
258 fnd_msg_pub.add;
259 raise fnd_api.g_exc_error;
260 end if;
261 close c_check_dup_temp_n;
262
263 open c_check_dup_temp_n(p_dependent_on_task_id, p_task_id, p_dependency_id);
264 fetch c_check_dup_temp_n into x;
265
266 if c_check_dup_temp_n%found
267 then
268 close c_check_dup_temp_n;
269 l_return_status := fnd_api.g_ret_sts_error;
270 fnd_message.set_name ('JTF', 'JTF_TASK_DEPENDS_RVERSE_EXISTS');
271 fnd_msg_pub.add;
272 raise fnd_api.g_exc_error;
273 end if;
274 close c_check_dup_temp_n;
275
276 open c_check_cyc_temp_n(p_dependency_id);
277 fetch c_check_cyc_temp_n into x;
278
279 if c_check_cyc_temp_n%found
280 then
281 close c_check_cyc_temp_n;
282 l_return_status := fnd_api.g_ret_sts_unexp_error;
283 fnd_message.set_name ('JTF', 'JTF_TASK_DEPENDS_CYCLICAL');
284 fnd_msg_pub.add;
285 raise fnd_api.g_exc_error;
286 end if;
287 close c_check_cyc_temp_n;
288 end if;
289
290 return l_return_status;
291
292 exception
293 when fnd_api.g_exc_error
294 then
295 return fnd_api.g_ret_sts_error;
296 when fnd_api.g_exc_unexpected_error
297 then
298 return fnd_api.g_ret_sts_unexp_error;
299 when others
300 then
301 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
302 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
303 fnd_msg_pub.add;
304 return fnd_api.g_ret_sts_unexp_error;
305 end;
306
307 procedure reconnect_dependency (p_api_version in number,
308 p_init_msg_list in varchar2 default fnd_api.g_false,
309 p_commit in varchar2 default fnd_api.g_false,
310 x_return_status out nocopy varchar2,
311 x_msg_data out nocopy varchar2,
312 x_msg_count out nocopy number,
313 p_task_id in number,
314 p_template_flag in varchar2 default 'N')
315 is
316 l_api_version constant number := 1.0;
317 l_api_name constant varchar2(30) := 'RECONNECT_DEPENDENCY';
318 l_dependency_id jtf_task_depends.dependency_id%TYPE := -1;
319 x_dependency_id jtf_task_depends.dependency_id%TYPE;
320
321 -- Cursor for finding successor dependencies
322 cursor c_successor_dependency
323 is
324 select dependency_id,
325 task_id,
326 dependency_type_code,
327 adjustment_time,
328 adjustment_time_uom,
329 object_version_number
330 from jtf_task_depends
331 where dependent_on_task_id = p_task_id and
335 cursor c_suc_template_dependency
332 (template_flag is null or template_flag = 'N');
333
334 -- Cursor for finding successor dependencies for template
336 is
337 select dependency_id,
338 task_id,
339 dependency_type_code,
340 adjustment_time,
341 adjustment_time_uom,
342 object_version_number
343 from jtf_task_depends
344 where dependent_on_task_id = p_task_id and template_flag = 'Y';
345
346 -- Cursor for finding predecessor dependencies
347 cursor c_predecessor_dependency
348 is
349 select dependency_id,
350 dependent_on_task_id,
351 dependency_type_code,
352 adjustment_time,
353 adjustment_time_uom,
354 object_version_number
355 from jtf_task_depends
356 where task_id = p_task_id and
357 (template_flag is null or template_flag = 'N');
358
359
360 -- Cursor for finding predecessor dependencies for template
361 cursor c_pre_template_dependency
362 is
363 select dependency_id,
364 dependent_on_task_id,
365 dependency_type_code,
366 adjustment_time,
367 adjustment_time_uom,
368 object_version_number
369 from jtf_task_depends
370 where task_id = p_task_id and template_flag = 'Y';
371
372 parent_depend c_predecessor_dependency%rowtype;
373 child_depend c_successor_dependency%rowtype;
374
375
376 begin
377 savepoint reconnect_dependency_pvt;
378
379 if not fnd_api.compatible_api_call (l_api_version,
380 p_api_version,
381 l_api_name,
382 g_pkg_name)
383 then
384 raise fnd_api.g_exc_unexpected_error;
385 end if;
386
387 if fnd_api.to_boolean (p_init_msg_list)
388 then
389 fnd_msg_pub.initialize;
390 end if;
391 -- Walk through parent dependencies and reconnect them with child dependencies
392 for parent_depend in c_predecessor_dependency
393 loop
394 for child_depend in c_successor_dependency
395 loop
396 if (parent_depend.dependency_type_code = child_depend.dependency_type_code)
397 then
398 if ((parent_depend.adjustment_time is null and
399 child_depend.adjustment_time is null) or
400 (parent_depend.adjustment_time is not null and
401 child_depend.adjustment_time is not null and
402 parent_depend.adjustment_time_uom =
403 child_depend.adjustment_time_uom and
404 parent_depend.adjustment_time = child_depend.adjustment_time))
405 then
406 jtf_task_dependency_pvt.create_task_dependency (
407 p_api_version => 1.0,
408 p_init_msg_list => fnd_api.g_false,
409 p_commit => fnd_api.g_false,
410 p_task_id => child_depend.task_id,
411 p_dependent_on_task_id => parent_depend.dependent_on_task_id,
412 p_dependency_type_code => parent_depend.dependency_type_code,
413 p_template_flag => p_template_flag,
414 p_adjustment_time => parent_depend.adjustment_time,
415 p_adjustment_time_uom => parent_depend.adjustment_time_uom,
416 x_return_status => x_return_status,
417 x_msg_data => x_msg_data,
418 x_msg_count => x_msg_count,
419 x_dependency_id => x_dependency_id);
420 end if;
421 end if;
422 end loop;
423 end loop;
424
425 if fnd_api.to_boolean (p_commit)
426 then
427 commit work;
428 end if;
429
430 exception
431 when fnd_api.g_exc_error
432 then
433 rollback to reconnect_dependency_pvt;
434 x_return_status := fnd_api.g_ret_sts_error;
435 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
436 when fnd_api.g_exc_unexpected_error
437 then
438 rollback to reconnect_dependency_pvt;
439 x_return_status := fnd_api.g_ret_sts_unexp_error;
440 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
441 when no_data_found
442 then
443 rollback to reconnect_dependency_pvt;
444 x_return_status := fnd_api.g_ret_sts_unexp_error;
445 fnd_message.set_name ('JTF', 'JTF_TASK_CREATING_DEPENDS');
446 fnd_msg_pub.add;
447 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
448 when others
449 then
450 rollback to reconnect_dependency_pvt;
451 x_return_status := fnd_api.g_ret_sts_unexp_error;
452 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
453 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
454 fnd_msg_pub.add;
455 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
456 end;
457
458
459 PROCEDURE create_task_dependency (
463 p_task_id IN NUMBER,
460 p_api_version IN NUMBER,
461 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
462 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
464 p_dependent_on_task_id IN NUMBER,
465 p_dependency_type_code IN VARCHAR2,
466 p_template_flag IN VARCHAR2 DEFAULT jtf_task_utl.g_no,
467 p_adjustment_time IN NUMBER DEFAULT NULL,
468 p_adjustment_time_uom IN VARCHAR2 DEFAULT NULL,
469 x_dependency_id OUT NOCOPY NUMBER,
470 x_return_status OUT NOCOPY VARCHAR2,
471 x_msg_data OUT NOCOPY VARCHAR2,
472 x_msg_count OUT NOCOPY NUMBER,
473 p_attribute1 IN VARCHAR2 DEFAULT null ,
474 p_attribute2 IN VARCHAR2 DEFAULT null ,
475 p_attribute3 IN VARCHAR2 DEFAULT null ,
476 p_attribute4 IN VARCHAR2 DEFAULT null ,
477 p_attribute5 IN VARCHAR2 DEFAULT null ,
478 p_attribute6 IN VARCHAR2 DEFAULT null ,
479 p_attribute7 IN VARCHAR2 DEFAULT null ,
480 p_attribute8 IN VARCHAR2 DEFAULT null ,
481 p_attribute9 IN VARCHAR2 DEFAULT null ,
482 p_attribute10 IN VARCHAR2 DEFAULT null ,
483 p_attribute11 IN VARCHAR2 DEFAULT null ,
484 p_attribute12 IN VARCHAR2 DEFAULT null ,
485 p_attribute13 IN VARCHAR2 DEFAULT null ,
486 p_attribute14 IN VARCHAR2 DEFAULT null ,
487 p_attribute15 IN VARCHAR2 DEFAULT null ,
488 p_attribute_category IN VARCHAR2 DEFAULT null ,
489 p_validated_flag in varchar2 default 'N'
490 )
491 IS
492
493 l_api_version CONSTANT NUMBER := 1.0;
494 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TASK_DEPENDENCY';
495 l_dependency_id jtf_task_depends.dependency_id%TYPE := -1;
496 l_rowid ROWID;
497 l_d_source_object_type_code jtf_tasks_b.source_object_type_code%TYPE;
498 l_d_source_object_id jtf_tasks_b.source_object_id%TYPE;
499 l_source_object_type_code jtf_tasks_b.source_object_type_code%TYPE;
500 l_source_object_id jtf_tasks_b.source_object_id%TYPE;
501
502 BEGIN
503 SAVEPOINT create_task_dependency_pvt;
504
505
506 IF NOT fnd_api.compatible_api_call (l_api_version,
507 p_api_version,
508 l_api_name,
509 g_pkg_name)
510 THEN
511 RAISE fnd_api.g_exc_unexpected_error;
512 END IF;
513
514 IF fnd_api.to_boolean (p_init_msg_list)
515 THEN
516 fnd_msg_pub.initialize;
517 END IF;
518
519 -- Validate effort
520 jtf_task_utl.validate_effort (x_return_status => x_return_status,
521 p_effort => p_adjustment_time,
522 p_effort_uom => p_adjustment_time_uom);
523
524 if (x_return_status = fnd_api.g_ret_sts_error)
525 then
526 raise fnd_api.g_exc_error;
527 elsif (x_return_status = fnd_api.g_ret_sts_unexp_error)
528 then
529 raise fnd_api.g_exc_unexpected_error;
530 end if;
531
532 if p_template_flag = jtf_task_utl.g_yes
533 then
534 if (jtf_task_utl.get_task_template_group (p_task_id) <>
535 jtf_task_utl.get_task_template_group (p_dependent_on_task_id))
536 then
537 fnd_message.set_name ('JTF', 'JTF_TASK_INCONSISTENT_TEMP');
538 fnd_message.set_token ('P_TASK_TEMPLATE_1', p_task_id);
539 fnd_message.set_token ('P_TASK_TEMPLATE_2', p_dependent_on_task_id);
540 fnd_msg_pub.add;
541 raise fnd_api.g_exc_unexpected_error;
542 end if;
543 else
544 jtf_task_utl.get_object_details (p_task_id => p_task_id,
545 p_template_flag => p_template_flag,
546 x_return_status => x_return_status,
547 x_source_object_code => l_source_object_type_code);
548
549
550 if not (x_return_status = fnd_api.g_ret_sts_success)
551 then
552 x_return_status := fnd_api.g_ret_sts_unexp_error;
553 raise fnd_api.g_exc_unexpected_error;
554 end if;
555
556 jtf_task_utl.get_object_details (p_task_id => p_dependent_on_task_id,
557 p_template_flag => p_template_flag,
558 x_return_status => x_return_status,
559 x_source_object_code => l_d_source_object_type_code);
560
561
562 if (x_return_status = fnd_api.g_ret_sts_error)
563 then
564 raise fnd_api.g_exc_error;
565 elsif (x_return_status = fnd_api.g_ret_sts_unexp_error)
566 then
567 raise fnd_api.g_exc_unexpected_error;
568 end if;
569
570
574 l_d_source_object_type_code is null))
571 if ((l_source_object_type_code is null and
572 l_d_source_object_type_code is not null) or
573 (l_source_object_type_code is not null and
575 then
576 fnd_message.set_name ('JTF', 'JTF_TASK_DEPENDS_TYPE_CODE');
577 fnd_msg_pub.add;
578 raise fnd_api.g_exc_error;
579 end if;
580
581 if (l_source_object_type_code is not null and l_d_source_object_type_code is not null)
582 then
583 if (l_source_object_type_code <> l_d_source_object_type_code)
584 then
585 fnd_message.set_name ('JTF', 'JTF_TASK_DEPENDS_TYPE_CODE');
586 fnd_msg_pub.add;
587 raise fnd_api.g_exc_error;
588 end if;
589 end if;
590 end if;
591
592 if ((validate_task_dependency ( p_task_id => p_task_id,
593 p_dependent_on_task_id => p_dependent_on_task_id,
594 p_dependency_id => l_dependency_id,
595 p_template_flag => p_template_flag)
596 = fnd_api.g_ret_sts_error) or
597 (p_validated_flag = 'Y' and NVL(p_template_flag,'N') <> 'Y' and -- Added p_template_flag by SBARAT on 22/02/206 for bug# 4998404
598 validate_dependency ( p_task_id => p_task_id,
599 p_dependent_on_task_id => p_dependent_on_task_id,
600 p_dependency_type_code => p_dependency_type_code,
601 p_adjustment_time => p_adjustment_time,
602 p_adjustment_time_uom => p_adjustment_time_uom,
603 p_validated_flag => p_validated_flag)
604 = fnd_api.g_ret_sts_error))
605 then
606 raise fnd_api.g_exc_error;
607 end if;
608
609
610 SELECT jtf_task_depends_s.nextval
611 INTO l_dependency_id
612 FROM dual;
613
614 jtf_task_depends_pkg.insert_row (
615 x_rowid => l_rowid,
616 x_dependency_id => l_dependency_id,
617 x_task_id => p_task_id,
618 x_dependent_on_task_id => p_dependent_on_task_id,
619 x_adjustment_time => p_adjustment_time,
620 x_adjustment_time_uom => p_adjustment_time_uom,
621 x_template_flag => p_template_flag,
622 x_validated_flag => p_validated_flag,
623 x_attribute1 => p_attribute1 ,
624 x_attribute2 => p_attribute2 ,
625 x_attribute3 => p_attribute3 ,
626 x_attribute4 => p_attribute4 ,
627 x_attribute5 => p_attribute5 ,
628 x_attribute6 => p_attribute6 ,
629 x_attribute7 => p_attribute7 ,
630 x_attribute8 => p_attribute8 ,
631 x_attribute9 => p_attribute9 ,
632 x_attribute10 => p_attribute10 ,
633 x_attribute11 => p_attribute11 ,
634 x_attribute12 => p_attribute12 ,
635 x_attribute13 => p_attribute13 ,
636 x_attribute14 => p_attribute14 ,
637 x_attribute15 => p_attribute15,
638 x_attribute_category => p_attribute_category ,
639 x_dependency_type_code => p_dependency_type_code,
640 x_creation_date => SYSDATE,
641 x_created_by => jtf_task_utl.created_by,
642 x_last_update_date => SYSDATE,
643 x_last_updated_by => jtf_task_utl.updated_by,
644 x_last_update_login => jtf_task_utl.login_id
645 );
646
647 if fnd_api.to_boolean (p_commit)
648 then
649 commit work;
650 end if;
651
652 x_dependency_id := l_dependency_id;
653
654 EXCEPTION
655 -- Bug 3342398
656 -- Added handle of fnd_api.g_exc_error.
657 WHEN fnd_api.g_exc_error
658 THEN
659 ROLLBACK TO create_task_dependency_pvt;
660 x_return_status := fnd_api.g_ret_sts_error;
661 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
662 WHEN fnd_api.g_exc_unexpected_error
663 THEN
664 ROLLBACK TO create_task_dependency_pvt;
665 x_return_status := fnd_api.g_ret_sts_unexp_error;
666 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
667 WHEN no_data_found
668 THEN
669 ROLLBACK TO create_task_dependency_pvt;
670 x_return_status := fnd_api.g_ret_sts_unexp_error;
671 fnd_message.set_name ('JTF', 'JTF_TASK_CREATING_DEPENDS');
672 fnd_msg_pub.add;
673 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
674 WHEN OTHERS
675 THEN
676 ROLLBACK TO create_task_dependency_pvt;
677 x_return_status := fnd_api.g_ret_sts_unexp_error;
678 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
679 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
680 fnd_msg_pub.add;
681 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
682 END;
683
687 ***********************************************************************************************************
684 /**********************************************************************************************************
685 ***********************************************************************************************************
686 ***********************************************************************************************************
688 ***********************************************************************************************************/
689 PROCEDURE update_task_dependency (
690 p_api_version IN NUMBER,
691 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
692 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
693 p_object_version_number IN out NOCOPY NUMBER,
694 p_dependency_id IN NUMBER,
695 p_task_id IN NUMBER DEFAULT fnd_api.g_miss_num,
696 p_dependent_on_task_id IN NUMBER DEFAULT fnd_api.g_miss_num,
697 p_dependency_type_code IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
698 p_adjustment_time IN NUMBER DEFAULT fnd_api.g_miss_num,
699 p_adjustment_time_uom IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
700 x_return_status OUT NOCOPY VARCHAR2,
701 x_msg_count OUT NOCOPY NUMBER,
702 x_msg_data OUT NOCOPY VARCHAR2,
703 p_attribute1 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
704 p_attribute2 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
705 p_attribute3 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
706 p_attribute4 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
707 p_attribute5 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
708 p_attribute6 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
709 p_attribute7 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
710 p_attribute8 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
711 p_attribute9 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
712 p_attribute10 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
713 p_attribute11 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
714 p_attribute12 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
715 p_attribute13 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
716 p_attribute14 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
717 p_attribute15 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
718 p_attribute_category IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
719 p_validated_flag in varchar2 default 'N'
720 )
721 IS
722 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TASK_DEPENDENCY';
723
724 l_source_object_type_code jtf_tasks_b.source_object_type_code%TYPE;
725 l_source_object_id jtf_tasks_b.source_object_id%TYPE;
726 l_d_source_object_type_code jtf_tasks_b.source_object_type_code%TYPE;
727 l_d_source_object_id jtf_tasks_b.source_object_id%TYPE;
728 l_dependency_id jtf_task_depends.dependency_id%TYPE := p_dependency_id;
729 l_task_id jtf_task_depends.task_id%TYPE := p_task_id;
730 l_dependent_on_task_id jtf_task_depends.dependent_on_task_id%TYPE := p_dependent_on_task_id;
731 l_dependency_type_code jtf_task_depends.dependency_type_code%TYPE := p_dependency_type_code;
732 l_adjustment_time jtf_task_depends.adjustment_time%TYPE := p_adjustment_time;
733 l_adjustment_time_uom jtf_task_depends.adjustment_time_uom%TYPE := p_adjustment_time_uom;
734 l_template_flag jtf_task_depends.template_flag%TYPE;
735 Resource_Locked exception ;
736
737 PRAGMA EXCEPTION_INIT ( Resource_Locked , - 54 ) ;
738
739
740 CURSOR c_jtf_task_depends
741 IS
742 SELECT DECODE (p_task_id, fnd_api.g_miss_num, task_id, p_task_id) task_id,
743 DECODE (p_dependent_on_task_id, fnd_api.g_miss_num, dependent_on_task_id, p_dependent_on_task_id) dependent_on_task_id,
744 DECODE (p_dependency_type_code, fnd_api.g_miss_char, dependency_type_code, p_dependency_type_code) dependency_type_code,
745 template_flag template_flag,
746 DECODE (p_adjustment_time, fnd_api.g_miss_num, adjustment_time, p_adjustment_time) adjustment_time,
747 DECODE (p_adjustment_time_uom, fnd_api.g_miss_char, adjustment_time_uom, p_adjustment_time_uom) adjustment_time_uom,
748 created_by,
749 DECODE( p_attribute1 , fnd_api.g_miss_char , attribute1 , p_attribute1 ) attribute1 ,
750 DECODE( p_attribute2 , fnd_api.g_miss_char , attribute2 , p_attribute2 ) attribute2 ,
751 DECODE( p_attribute3 , fnd_api.g_miss_char , attribute3 , p_attribute3 ) attribute3 ,
752 DECODE( p_attribute4 , fnd_api.g_miss_char , attribute4 , p_attribute4 ) attribute4 ,
753 DECODE( p_attribute5 , fnd_api.g_miss_char , attribute5 , p_attribute5 ) attribute5 ,
754 DECODE( p_attribute6 , fnd_api.g_miss_char , attribute6 , p_attribute6 ) attribute6 ,
755 DECODE( p_attribute7 , fnd_api.g_miss_char , attribute7 , p_attribute7 ) attribute7 ,
756 DECODE( p_attribute8 , fnd_api.g_miss_char , attribute8 , p_attribute8 ) attribute8 ,
757 DECODE( p_attribute9 , fnd_api.g_miss_char , attribute9 , p_attribute9 ) attribute9 ,
758 DECODE( p_attribute10 , fnd_api.g_miss_char , attribute10 , p_attribute10 ) attribute10 ,
759 DECODE( p_attribute11 , fnd_api.g_miss_char , attribute11 , p_attribute11 ) attribute11 ,
760 DECODE( p_attribute12 , fnd_api.g_miss_char , attribute12 , p_attribute12 ) attribute12 ,
761 DECODE( p_attribute13 , fnd_api.g_miss_char , attribute13 , p_attribute13 ) attribute13 ,
762 DECODE( p_attribute14 , fnd_api.g_miss_char , attribute14 , p_attribute14 ) attribute14 ,
763 DECODE( p_attribute15 , fnd_api.g_miss_char , attribute15 , p_attribute15 ) attribute15 ,
764 DECODE( p_attribute_category,fnd_api.g_miss_char,attribute_category,p_attribute_category) attribute_category
765 FROM jtf_task_depends
766 WHERE dependency_id = l_dependency_id;
767
768 task_depends c_jtf_task_depends%ROWTYPE;
769 BEGIN
770 --- This does not check between tasks and templates
771 --- because it is assumed the same is validated before
772 --- calling this proc.
773
774 SAVEPOINT update_task_dependency_pvt;
775 x_return_status := fnd_api.g_ret_sts_success;
776
777 OPEN c_jtf_task_depends;
778 FETCH c_jtf_task_depends INTO task_depends;
779
780 IF c_jtf_task_depends%NOTFOUND
781 THEN
782 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_DEPENDS_ID');
783 fnd_message.set_token ('P_DEPENDENCY_ID', p_dependency_id);
784 fnd_msg_pub.add;
785 x_return_status := fnd_api.g_ret_sts_unexp_error;
786 RAISE fnd_api.g_exc_unexpected_error;
787 END IF;
788
789 l_template_flag := task_depends.template_flag;
790
791 IF l_template_flag IS NULL
792 THEN
793 l_template_flag := jtf_task_utl.g_no;
794 END IF;
795
796 IF l_task_id = fnd_api.g_miss_num
797 THEN
798 l_task_id := task_depends.task_id;
799 END IF;
800
801 IF l_dependent_on_task_id = fnd_api.g_miss_num
802 THEN
803 l_dependent_on_task_id := task_depends.dependent_on_task_id;
804 END IF;
805
806 IF l_dependency_type_code = fnd_api.g_miss_char
807 THEN
808 l_dependency_type_code := task_depends.dependency_type_code;
809 END IF;
810
811 IF l_adjustment_time = fnd_api.g_miss_num
815
812 THEN
813 l_adjustment_time := task_depends.adjustment_time;
814 END IF;
816 IF l_adjustment_time_uom = fnd_api.g_miss_char
817 THEN
818 l_adjustment_time_uom := task_depends.adjustment_time_uom;
819 END IF;
820
821 -- Validate effort
822 jtf_task_utl.validate_effort (x_return_status => x_return_status,
823 p_effort => p_adjustment_time,
824 p_effort_uom => p_adjustment_time_uom);
825
826 if (x_return_status = fnd_api.g_ret_sts_error)
827 then
831 raise fnd_api.g_exc_unexpected_error;
828 raise fnd_api.g_exc_error;
829 elsif (x_return_status = fnd_api.g_ret_sts_unexp_error)
830 then
832 end if;
833
834 if l_template_flag = jtf_task_utl.g_yes
835 then
836 if (jtf_task_utl.get_task_template_group (p_task_id) <>
837 jtf_task_utl.get_task_template_group (p_dependent_on_task_id))
838 then
839 fnd_message.set_name ('JTF', 'JTF_TASK_INCONSISTENT_TEMP');
840 fnd_message.set_token ('P_TASK_TEMPLATE_1', p_task_id);
841 fnd_message.set_token ('P_TASK_TEMPLATE_2', p_dependent_on_task_id);
842 fnd_msg_pub.add;
843 raise fnd_api.g_exc_unexpected_error;
844 end if;
845 else
849 x_source_object_code => l_source_object_type_code);
846 jtf_task_utl.get_object_details (p_task_id => p_task_id,
847 p_template_flag => l_template_flag,
848 x_return_status => x_return_status,
850
851
852 if not (x_return_status = fnd_api.g_ret_sts_success)
853 then
854 x_return_status := fnd_api.g_ret_sts_unexp_error;
855 raise fnd_api.g_exc_unexpected_error;
856 end if;
857
858 jtf_task_utl.get_object_details (p_task_id => p_dependent_on_task_id,
859 p_template_flag => l_template_flag,
860 x_return_status => x_return_status,
861 x_source_object_code => l_d_source_object_type_code);
862
863
864 if (x_return_status = fnd_api.g_ret_sts_error)
865 then
866 raise fnd_api.g_exc_error;
867 elsif (x_return_status = fnd_api.g_ret_sts_unexp_error)
868 then
869 raise fnd_api.g_exc_unexpected_error;
870 end if;
871
872
873 if ((l_source_object_type_code is null and
874 l_d_source_object_type_code is not null) or
875 (l_source_object_type_code is not null and
876 l_d_source_object_type_code is null))
877 then
878 fnd_message.set_name ('JTF', 'JTF_TASK_DEPENDS_TYPE_CODE');
879 fnd_msg_pub.add;
880 raise fnd_api.g_exc_error;
881 end if;
882
883 if (l_source_object_type_code is not null and l_d_source_object_type_code is not null)
884 then
885 if (l_source_object_type_code <> l_d_source_object_type_code)
886 then
887 fnd_message.set_name ('JTF', 'JTF_TASK_DEPENDS_TYPE_CODE');
888 fnd_msg_pub.add;
889 raise fnd_api.g_exc_error;
890 end if;
891 end if;
892 end if;
893
894 if ((validate_task_dependency ( p_task_id => p_task_id,
895 p_dependent_on_task_id => p_dependent_on_task_id,
896 p_dependency_id => l_dependency_id,
897 p_template_flag => l_template_flag)
898 = fnd_api.g_ret_sts_error) or
899 (p_validated_flag = 'Y' and NVL(l_template_flag,'N') <> 'Y' and -- Added p_template_flag by SBARAT on 22/02/206 for bug# 4998404
900 validate_dependency ( p_task_id => p_task_id,
901 p_dependent_on_task_id => p_dependent_on_task_id,
902 p_dependency_type_code => p_dependency_type_code,
903 p_adjustment_time => p_adjustment_time,
904 p_adjustment_time_uom => p_adjustment_time_uom,
905 p_validated_flag => p_validated_flag)
906 = fnd_api.g_ret_sts_error))
907 then
908 raise fnd_api.g_exc_error;
909 end if;
910
911
912 jtf_task_depends_pkg.lock_row(
913 x_dependency_id => p_dependency_id ,
914 x_object_version_number => p_object_version_number );
915
916
917 jtf_task_depends_pkg.update_row (
918 x_dependency_id => l_dependency_id,
919 x_object_version_number => p_object_version_number + 1,
920 x_task_id => l_task_id,
921 x_dependent_on_task_id => l_dependent_on_task_id,
922 x_dependency_type_code => l_dependency_type_code,
923 x_adjustment_time_uom => l_adjustment_time_uom,
924 x_template_flag => l_template_flag,
925 x_adjustment_time => l_adjustment_time,
926 x_validated_flag => p_validated_flag,
927 x_attribute1 => task_depends.attribute1 ,
928 x_attribute2 => task_depends.attribute2 ,
929 x_attribute3 => task_depends.attribute3 ,
930 x_attribute4 => task_depends.attribute4 ,
931 x_attribute5 => task_depends.attribute5 ,
932 x_attribute6 => task_depends.attribute6 ,
933 x_attribute7 => task_depends.attribute7 ,
934 x_attribute8 => task_depends.attribute8 ,
935 x_attribute9 => task_depends.attribute9 ,
936 x_attribute10 => task_depends.attribute10 ,
937 x_attribute11 => task_depends.attribute11 ,
938 x_attribute12 => task_depends.attribute12 ,
939 x_attribute13 => task_depends.attribute13 ,
940 x_attribute14 => task_depends.attribute14 ,
941 x_attribute15 => task_depends.attribute15 ,
942 x_attribute_category => task_depends.attribute_category ,
943 x_last_update_date => SYSDATE,
944 x_last_updated_by => jtf_task_utl.updated_by,
945 x_last_update_login => -1
946 );
947
948 IF c_jtf_task_depends%ISOPEN
949 THEN
950 CLOSE c_jtf_task_depends;
951 END IF;
952
953 IF fnd_api.to_boolean (p_commit)
954 THEN
955 COMMIT WORK;
956 END IF;
957
958 p_object_version_number := p_object_version_number + 1;
959
960 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
961 EXCEPTION
962 /*
963 Bug 3342398
964 Added handle of fnd_api.g_exc_error.
965 */
966 WHEN fnd_api.g_exc_error
967 THEN
968 ROLLBACK TO update_task_dependency_pvt;
969 x_return_status := fnd_api.g_ret_sts_error;
973 IF c_jtf_task_depends%ISOPEN
970 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
971 WHEN fnd_api.g_exc_unexpected_error
972 THEN
974 THEN
975 CLOSE c_jtf_task_depends;
976 END IF;
977 ROLLBACK TO update_task_dependency_pvt;
978 x_return_status := fnd_api.g_ret_sts_unexp_error;
979 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
980 WHEN no_data_found
981 THEN
982 ROLLBACK TO create_task_dependency_pvt;
983 x_return_status := fnd_api.g_ret_sts_unexp_error;
984 fnd_message.set_name ('JTF', 'JTF_TASK_CREATING_DEPENDS');
985 fnd_msg_pub.add;
986 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
987 WHEN Resource_Locked then
988 ROLLBACK TO lock_task_depends_pub;
989 fnd_message.set_name ('JTF', 'JTF_TASK_RESOURCE_LOCKED');
990 fnd_message.set_token ('P_LOCKED_RESOURCE', 'Contacts');
991 x_return_status := fnd_api.g_ret_sts_unexp_error;
992 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
993 WHEN OTHERS
994 THEN
995 IF c_jtf_task_depends%ISOPEN
996 THEN
997 CLOSE c_jtf_task_depends;
998 END IF;
999
1000 ROLLBACK TO update_task_dependency_pvt;
1001 x_return_status := fnd_api.g_ret_sts_unexp_error;
1002 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
1003 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
1004 fnd_msg_pub.add;
1005 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1006 END;
1007
1008 /**********************************************************************************************************
1009 ***********************************************************************************************************
1010 ***********************************************************************************************************
1011 ***********************************************************************************************************
1012 ***********************************************************************************************************/
1013 PROCEDURE delete_task_dependency (
1014 p_api_version IN NUMBER,
1015 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
1016 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
1017 p_object_version_number IN NUMBER,
1018 p_dependency_id IN NUMBER,
1019 x_return_status OUT NOCOPY VARCHAR2,
1020 x_msg_count OUT NOCOPY NUMBER,
1021 x_msg_data OUT NOCOPY VARCHAR2
1022 )
1023 IS
1024 x char;
1025 l_dependency_id jtf_task_depends.dependency_id%TYPE := p_dependency_id;
1026 Resource_Locked exception ;
1027 PRAGMA EXCEPTION_INIT ( Resource_Locked , - 54 ) ;
1028
1029
1030 CURSOR c_jtf_task_depends
1031 IS
1032 SELECT 1
1033 FROM jtf_task_depends
1034 WHERE dependency_id = l_dependency_id;
1035 BEGIN
1036 --- This does not check between tasks and templates
1037 --- because it is assumed the same is validated before
1038 --- calling this proc,
1039 SAVEPOINT delete_task_dependency_pvt;
1040
1041 x_return_status := fnd_api.g_ret_sts_success;
1042
1043 jtf_task_depends_pkg.lock_row(
1044 x_dependency_id => p_dependency_id ,
1045 x_object_version_number => p_object_version_number );
1046
1047 jtf_task_depends_pkg.delete_row (x_dependency_id => l_dependency_id);
1048 OPEN c_jtf_task_depends;
1049 FETCH c_jtf_task_depends INTO x;
1050
1051 IF c_jtf_task_depends%FOUND
1052 THEN
1053 fnd_message.set_name ('JTF', 'JTF_TASK_DELETING_DEPEND');
1054 fnd_msg_pub.add;
1055 x_return_status := fnd_api.g_ret_sts_unexp_error;
1056 RAISE fnd_api.g_exc_unexpected_error;
1057 CLOSE c_jtf_task_depends;
1058 ELSE
1059 CLOSE c_jtf_task_depends;
1060 END IF;
1061
1062 IF c_jtf_task_depends%ISOPEN
1063 THEN
1064 CLOSE c_jtf_task_depends;
1065 END IF;
1066
1067 IF fnd_api.to_boolean (p_commit)
1068 THEN
1069 COMMIT WORK;
1070 END IF;
1071
1072 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1073 EXCEPTION
1074 /*
1075 Bug 3342398
1076 Added handle of fnd_api.g_exc_error.
1077 */
1078 WHEN fnd_api.g_exc_error
1079 THEN
1080 ROLLBACK TO delete_task_dependency_pvt;
1081 x_return_status := fnd_api.g_ret_sts_error;
1082 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1083 WHEN fnd_api.g_exc_unexpected_error
1084 THEN
1085 ROLLBACK TO delete_task_dependency_pvt;
1086 x_return_status := fnd_api.g_ret_sts_unexp_error;
1087 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1088 WHEN Resource_Locked then
1089 ROLLBACK TO lock_task_depends_pub;
1090 fnd_message.set_name ('JTF', 'JTF_TASK_RESOURCE_LOCKED');
1091 fnd_message.set_token ('P_LOCKED_RESOURCE', 'Contacts');
1092 x_return_status := fnd_api.g_ret_sts_unexp_error;
1093 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1094 WHEN OTHERS
1095 THEN
1096 ROLLBACK TO delete_task_dependency_pvt;
1097 x_return_status := fnd_api.g_ret_sts_unexp_error;
1098 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
1099 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
1100 fnd_msg_pub.add;
1101 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1102 END;
1103 END; -- CREATE OR REPLACE PACKAGE spec