1 PACKAGE BODY jtf_task_utl_ext AS
2 /* $Header: jtfptkxb.pls 120.2 2005/12/21 07:04:41 sbarat ship $ */
3
4 G_CD VARCHAR2(30) := 'CREATION_DATE';
5 G_SS VARCHAR2(30) := 'SCHEDULED_START';
6 G_SE VARCHAR2(30) := 'SCHEDULED_END';
7 G_PS VARCHAR2(30) := 'PLANNED_START';
8 G_PE VARCHAR2(30) := 'PLANNED_END';
9 G_AS VARCHAR2(30) := 'ACTUAL_START';
10 G_AE VARCHAR2(30) := 'ACTUAL_END';
11
12
13 FUNCTION adjust_date(p_original_date in date, p_adjustment_time in number, p_adjustment_time_uom in varchar2)
14 return DATE
15 is
16 l_adjustment_time number;
17 l_base_uom_code varchar2(30);
18 begin
19
20 -- Return the original date if there is no adjustment time or if there is no adjustment time UOM specified.
21 if (p_adjustment_time is null or p_adjustment_time = 0 or p_adjustment_time_uom is null or p_original_date is null)
22 then
23 return p_original_date;
24 end if;
25
26 -- Get default inventory code, which is always hours
27 select uom_code into l_base_uom_code from mtl_units_of_measure
28 where base_uom_flag = 'Y' and uom_class = fnd_profile.value('JTF_TIME_UOM_CLASS');
29 -- Get the adjustment time
30
31 if (p_adjustment_time_uom = l_base_uom_code)
32 then
33 l_adjustment_time := p_adjustment_time;
34 else
35 l_adjustment_time := inv_convert.inv_um_convert( item_id => null,
36 precision => 2,
37 from_quantity => p_adjustment_time,
38 from_unit => p_adjustment_time_uom,
39 to_unit => l_base_uom_code,
40 from_name => null,
41 to_name => null);
42 end if;
43
44 -- return the converted adjusted date
45 return p_original_date + l_adjustment_time/24;
46 end;
47
48
49 -- Get the booking start date
50 FUNCTION get_bsd
51 (
52 p_calendar_start_date IN DATE,
53 p_calendar_end_date IN DATE,
54 p_actual_start_date IN DATE,
55 p_actual_end_date IN DATE,
56 p_actual_travel_duration IN NUMBER,
57 p_actual_travel_duration_uom IN VARCHAR2,
58 p_planned_effort IN NUMBER,
59 p_planned_effort_uom IN VARCHAR2,
60 p_actual_effort IN NUMBER,
61 p_actual_effort_uom IN VARCHAR2
62 ) RETURN DATE
63 IS
64 l_start_date DATE;
65 BEGIN
66 -- Populate the booking dates by using actual dates from the assignment
67 IF (p_actual_start_date IS NULL) OR
68 (p_actual_start_date > p_actual_end_date) OR
69 (p_actual_end_date IS NULL AND
70 NVL(p_actual_effort, NVL(p_planned_effort, -1)) < 0)
71 THEN
72 -- Populate the booking dates by using calendar dates from the task
73 IF (p_calendar_start_date IS NULL) OR
74 (p_calendar_start_date > p_calendar_end_date) OR
75 (p_calendar_end_date IS NULL AND NVL(p_planned_effort, -1) < 0)
76 THEN
77 RETURN NULL;
78 END IF;
79 l_start_date := p_calendar_start_date;
80 ELSE
81 l_start_date := p_actual_start_date;
82 END IF;
83
84 IF NVL(p_actual_travel_duration, 0) > 0
85 THEN
86 l_start_date := jtf_task_utl_ext.adjust_date(
87 p_actual_start_date,
88 p_actual_travel_duration * (-1),
89 p_actual_travel_duration_uom);
90 END IF;
91
92 RETURN l_start_date;
93 END get_bsd;
94
95
96 -- Get the booking end date
97 FUNCTION get_bed
98 (
99 p_calendar_start_date IN DATE,
100 p_calendar_end_date IN DATE,
101 p_actual_start_date IN DATE,
102 p_actual_end_date IN DATE,
103 p_actual_travel_duration IN NUMBER,
104 p_actual_travel_duration_uom IN VARCHAR2,
105 p_planned_effort IN NUMBER,
106 p_planned_effort_uom IN VARCHAR2,
107 p_actual_effort IN NUMBER,
108 p_actual_effort_uom IN VARCHAR2
109 ) RETURN DATE
110 IS
111 l_end_date DATE;
112 BEGIN
113 -- Populate the booking date by using actual dates from the assignment
114 IF (p_actual_start_date IS NULL) OR
115 (p_actual_start_date > p_actual_end_date) OR
116 (p_actual_end_date IS NULL AND
117 NVL(p_actual_effort, NVL(p_planned_effort, -1)) < 0)
118 THEN
119 -- Populate the booking dates by using calendar dates from the task
120 IF (p_calendar_start_date IS NULL) OR
121 (p_calendar_start_date > p_calendar_end_date) OR
122 (p_calendar_end_date IS NULL AND NVL(p_planned_effort, -1) < 0)
123 THEN
124 l_end_date := NULL;
125 ELSIF (p_calendar_start_date <= p_calendar_end_date)
126 THEN
127 l_end_date := p_calendar_end_date;
128 ELSE
129 l_end_date := jtf_task_utl_ext.adjust_date
130 (p_calendar_start_date,
131 p_planned_effort,
132 p_planned_effort_uom
133 );
134 END IF;
135 ELSE
136 IF p_actual_start_date <= p_actual_end_date
137 THEN
138 l_end_date := p_actual_end_date;
139 ELSIF p_actual_effort >= 0
140 THEN
141 l_end_date := jtf_task_utl_ext.adjust_date
142 (p_actual_start_date,
143 p_actual_effort,
144 p_actual_effort_uom
145 );
146 ELSE
147 l_end_date := jtf_task_utl_ext.adjust_date
148 (p_actual_start_date,
149 p_planned_effort,
150 p_planned_effort_uom
151 );
152 END IF;
153 END IF;
154
155 RETURN l_end_date;
156
157 END get_bed;
158
159
160
161 ------------------------------------------------------
162 -- For enhancement 2666995
163 FUNCTION get_open_flag (p_task_status_id IN NUMBER)
164 RETURN VARCHAR2
165 IS
166 CURSOR c_status IS
167 SELECT NVL(completed_flag,'N') completed_flag,
168 NVL(cancelled_flag,'N') cancelled_flag,
169 NVL(rejected_flag,'N') rejected_flag,
170 NVL(closed_flag,'N') closed_flag
171 FROM jtf_task_statuses_b
172 WHERE task_status_id = p_task_status_id;
173
174 rec_status c_status%ROWTYPE;
175 l_open_flag VARCHAR2(1) := jtf_task_utl.g_yes;
176 BEGIN
177 OPEN c_status;
178 FETCH c_status INTO rec_status;
179
180 IF c_status%NOTFOUND
181 THEN
182 CLOSE c_status;
183 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_STATUS_ID');
184 fnd_message.set_token ('P_TASK_STATUS_ID', p_task_status_id);
185 fnd_msg_pub.add;
186 RAISE fnd_api.g_exc_unexpected_error;
187 END IF;
188 CLOSE c_status;
189
190 IF rec_status.completed_flag = jtf_task_utl.g_yes OR
191 rec_status.cancelled_flag = jtf_task_utl.g_yes OR
192 rec_status.rejected_flag = jtf_task_utl.g_yes OR
193 rec_status.closed_flag = jtf_task_utl.g_yes
194 THEN
195 l_open_flag := jtf_task_utl.g_no;
196 END IF;
197
198 RETURN l_open_flag;
199 END get_open_flag;
200 ------------------------------------------------------
201
202 ------------------------------------------------------
203 -- For enhancement 2683868
204
205 -- Moved from jtf_task_utl
206 PROCEDURE set_calendar_dates (
207 p_show_on_calendar in varchar2,
208 p_date_selected in varchar2,
209 p_planned_start_date in date,
210 p_planned_end_date in date,
211 p_scheduled_start_date in date,
212 p_scheduled_end_date in date,
213 p_actual_start_date in date,
214 p_actual_end_date in date,
215 x_show_on_calendar IN OUT NOCOPY varchar2,-- Fixed from OUT to IN OUT
216 x_date_selected IN OUT NOCOPY varchar2,-- Fixed from OUT to IN OUT
217 x_calendar_start_date OUT NOCOPY date,
218 x_calendar_end_date OUT NOCOPY date,
219 x_return_status OUT NOCOPY varchar2,
220 p_task_status_id IN NUMBER, -- Enhancement 2683868: new parameter
221 p_creation_date IN DATE -- Enhancement 2683868: new parameter
222 )
223 is
224
225 -- Fix for bug 2932012
226 --cursor c_date_selected is
227 --select decode(fnd_profile.value('JTF_TASK_DEFAULT_DATE_SELECTED'),
228 -- 'PLANNED', 'P',
229 -- 'SCHEDULED', 'S',
230 -- 'ACTUAL', 'A',
231 -- 'S')
232 -- from dual;
233
234 l_date_selected varchar2(1);
235 l_cal_start_date date;
236 l_cal_end_date date;
237 l_show_2day varchar2(1);
238 l_date_profile varchar2(30) := fnd_profile.value('JTF_TASK_DEFAULT_DATE_SELECTED');
239
240 begin
241 x_return_status := fnd_api.g_ret_sts_success;
242
243 -- get the default date_selected value from the profile
244 -- if not set, assume 'S'
245
246 ------------------------------------------------
247 -- Fixed bug 2629463:
248 -- Only when date_selected is not passed,
249 -- then get the value from the profile.
250 ------------------------------------------------
251 IF p_date_selected IS NULL OR
252 p_date_selected = fnd_api.g_miss_char
253 THEN
254 -- Fix for bug 2932012
255 --open c_date_selected;
256 --fetch c_date_selected into l_date_selected;
257 --if c_date_selected%NOTFOUND then
258 -- close c_date_selected;
259 -- raise fnd_api.g_exc_unexpected_error;
260 --end if;
261 --close c_date_selected;
262 IF (l_date_profile = 'PLANNED')
263 THEN
264 l_date_selected := 'P';
265 ELSIF(l_date_profile = 'SCHEDULED')
266 THEN
267 l_date_selected := 'S';
268 ELSIF(l_date_profile = 'ACTUAL')
269 THEN
270 l_date_selected := 'A';
271 ELSE
272 l_date_selected := 'S';
273 END IF;
274
275 ELSE
276 l_date_selected := p_date_selected;
277 END IF;
278 ------------------------------------------------
279
280 -- set up the dates to be used, according to the date selected
281
282 if l_date_selected = 'P' then
283 l_cal_start_date := p_planned_start_date;
284 l_cal_end_date := p_planned_end_date;
285 elsif
286 l_date_selected = 'S' then
287 l_cal_start_date := p_scheduled_start_date;
288 l_cal_end_date := p_scheduled_end_date;
289 elsif
290 l_date_selected = 'A' then
291 l_cal_start_date := p_actual_start_date;
292 l_cal_end_date := p_actual_end_date;
293 ----------------------------------------
294 -- Enhancement 2683868
295 elsif l_date_selected = 'D'
296 then
297 set_start_n_due_date (
298 p_task_status_id => p_task_status_id,
299 p_planned_start_date => p_planned_start_date,
300 p_planned_end_date => p_planned_end_date,
301 p_scheduled_start_date => p_scheduled_start_date,
302 p_scheduled_end_date => p_scheduled_end_date,
303 p_actual_start_date => p_actual_start_date,
304 p_actual_end_date => p_actual_end_date,
305 p_creation_date => p_creation_date,
306 x_calendar_start_date => l_cal_start_date,
307 x_calendar_end_date => l_cal_end_date,
308 x_return_status => x_return_status);
309
310 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
311 THEN
312 RAISE fnd_api.g_exc_unexpected_error;
313 END IF;
314 ----------------------------------------
315 end if;
316
317 -- set the default return values
318
319 x_show_on_calendar := 'Y';
320 x_date_selected := l_date_selected; -- For fix bug 2467890: always store date_selected
321 x_calendar_start_date := l_cal_start_date; -- For fix bug 2629463: Determine the calendar dates
322 x_calendar_end_date := l_cal_end_date; -- For fix bug 2629463: Determine the calendar dates
323
324 --------------------------------------------------------------------------------
325 -- For Fix bug 2467890, 2629463:
326 -- At this stage, decide show_on_calendar flag only
327 -- Hence removed the assignment statement for date_selected AND calendar dates
328 --------------------------------------------------------------------------------
329 if (p_show_on_calendar is null or p_show_on_calendar = fnd_api.g_miss_char)
330 then
331 if l_cal_start_date is not null and
332 l_cal_end_date is not null
333 then
334 if (p_date_selected is null or p_date_selected = fnd_api.g_miss_char)
335 then
336 x_show_on_calendar := 'Y';
337 else
338 x_show_on_calendar := p_show_on_calendar;
339 end if;
340 ------------------------------------------------------
341 -- For fix bug 2467890, 2926463
342 elsif l_cal_start_date is null and
343 l_cal_end_date is null
344 then
345 x_show_on_calendar := 'N';
346 --------------------------------------------------------------------------------
347 -- Before the fix of the bug 2629463,
348 -- During creation of task, this api defaulted date_selected as NULL.
349 -- And if the profile value is changed, the changed profile value was affecting
350 -- the decision for calendar start date and end date during update of the task.
351 -- To follow the same functionality, store date_selected as NULL if calendar dates
352 -- have not been decided on before.
353 --------------------------------------------------------------------------------
354 -- Bug 2962576: If the p_date_selected is 'D',
355 -- then always store 'D' for date_selected
356 IF x_date_selected <> 'D' THEN
357 x_date_selected := NULL;
358 END IF;
359 ------------------------------------------------------
360 end if;
361 else
362 x_show_on_calendar := p_show_on_calendar;
363 end if;
364
365 EXCEPTION
366 WHEN fnd_api.g_exc_unexpected_error THEN
367 x_return_status := fnd_api.g_ret_sts_unexp_error;
368
369 WHEN OTHERS
370 THEN
371 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
372 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
373 fnd_msg_pub.add;
374 x_return_status := fnd_api.g_ret_sts_unexp_error;
375 end;
376
377 PROCEDURE set_start_n_due_date (
378 p_task_status_id IN NUMBER,
379 p_planned_start_date IN DATE,
380 p_planned_end_date IN DATE,
381 p_scheduled_start_date IN DATE,
382 p_scheduled_end_date IN DATE,
383 p_actual_start_date IN DATE,
384 p_actual_end_date IN DATE,
385 p_creation_date IN DATE,
386 x_calendar_start_date OUT NOCOPY DATE,
387 x_calendar_end_date OUT NOCOPY DATE,
388 x_return_status OUT NOCOPY VARCHAR2
389 )
390 IS
391 CURSOR c_status IS
392 SELECT start_date_type
393 , end_date_type
394 FROM jtf_task_statuses_b
395 WHERE task_status_id = p_task_status_id;
396
397 rec_status c_status%ROWTYPE;
398
399 -- Added by SBARAT on 21/12/2005 for bug# 4616119
400 l_date_profile varchar2(30) := fnd_profile.value('JTF_TASK_DEFAULT_DATE_SELECTED');
401
402 BEGIN
403 x_return_status := fnd_api.g_ret_sts_success;
404
405 ---------------------------------------------------
406 -- Get status information
407 OPEN c_status;
408 FETCH c_status INTO rec_status;
409
410 IF c_status%NOTFOUND
411 THEN
412 CLOSE c_status;
413 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_STATUS_ID');
414 fnd_message.set_token ('P_TASK_STATUS_ID', p_task_status_id);
415 fnd_msg_pub.add;
416 RAISE fnd_api.g_exc_unexpected_error;
417 END IF;
418 CLOSE c_status;
419
420 IF rec_status.start_date_type IS NULL OR
421 rec_status.end_date_type IS NULL
422 THEN
423 /************** Start of addition by SBARAT on 21/12/2005 for bug# 4616119 **************/
424 IF l_date_profile IS NOT NULL
425 THEN
426 IF (l_date_profile = 'PLANNED')
427 THEN
428 x_calendar_start_date := p_planned_start_date;
429 x_calendar_end_date := p_planned_end_date;
430 ELSIF(l_date_profile = 'SCHEDULED')
431 THEN
432 x_calendar_start_date := p_scheduled_start_date;
433 x_calendar_end_date := p_scheduled_end_date;
434 ELSIF(l_date_profile = 'ACTUAL')
435 THEN
436 x_calendar_start_date := p_actual_start_date;
437 x_calendar_end_date := p_actual_end_date;
438 ELSE
439 x_calendar_start_date := NULL;
440 x_calendar_end_date := NULL;
441 END IF;
442 ELSE
443 x_calendar_start_date := p_scheduled_start_date;
444 x_calendar_end_date := p_scheduled_end_date;
445 END IF;
446 /************** End of addition by SBARAT on 21/12/2005 for bug# 4616119 **************/
447 RETURN;
448 END IF;
449
450 ---------------------------------------------
451 -- Determine calendar start date
452 IF rec_status.start_date_type = G_CD -- Creation Date
453 THEN
454 x_calendar_start_date := p_creation_date;
455
456 ELSIF rec_status.start_date_type = G_PS -- Planned Start Date
457 THEN
458 IF p_planned_start_date IS NULL
459 THEN
460 -- If date type is deriven by planned start date and its value is null
461 -- then throw an error "The planned start date must be provided."
462 fnd_message.set_name ('JTF', 'JTF_TASK_NULL_PLANNED_ST_DATE');
463 fnd_msg_pub.add;
464 x_return_status := fnd_api.g_ret_sts_unexp_error;
465 END IF;
466
467 x_calendar_start_date := p_planned_start_date;
468
469 ELSIF rec_status.start_date_type = G_SS -- Scheduled Start Date
470 THEN
471 IF p_scheduled_start_date IS NULL
472 THEN
473 -- If date type is deriven by schedule start date and its value is null,
474 -- then throw an error "The scheduled start date must be provided."
475 fnd_message.set_name ('JTF', 'JTF_TASK_NULL_SCHEDULE_ST_DATE');
476 fnd_msg_pub.add;
477 x_return_status := fnd_api.g_ret_sts_unexp_error;
478 END IF;
479
480 x_calendar_start_date := p_scheduled_start_date;
481
482 ELSIF rec_status.start_date_type = G_AS -- Actual Start Date
483 THEN
484 IF p_actual_start_date IS NULL
485 THEN
486 -- If date type is deriven by actual start date and its value is null
487 -- then throw an error "The actual start date must be provided."
488 fnd_message.set_name ('JTF', 'JTF_TASK_NULL_ACTUAL_ST_DATE');
489 fnd_msg_pub.add;
490 x_return_status := fnd_api.g_ret_sts_unexp_error;
491 END IF;
492
493 x_calendar_start_date := p_actual_start_date;
494 END IF;
495
496 ---------------------------------------------
497 -- Determine calendar end date
498 IF rec_status.end_date_type = G_CD -- Creation Date
499 THEN
500 x_calendar_end_date := p_creation_date;
501
502 ELSIF rec_status.end_date_type = G_PE -- Planned End Date
503 THEN
504 IF p_planned_end_date IS NULL
505 THEN
506 -- If date type is deriven by planned end date and its value is null
507 -- then throw an error "The planned end date must be provided."
508 fnd_message.set_name ('JTF', 'JTF_TASK_NULL_PLANNED_EN_DATE');
509 fnd_msg_pub.add;
510 x_return_status := fnd_api.g_ret_sts_unexp_error;
511 END IF;
512
513 x_calendar_end_date := p_planned_end_date;
514
515 ELSIF rec_status.end_date_type = G_SE -- Scheduled End Date
516 THEN
517 IF p_scheduled_end_date IS NULL
518 THEN
519 -- If date type is deriven by schedule end date and its value is null,
520 -- then throw an error "The scheduled end date must be provided."
521 fnd_message.set_name ('JTF', 'JTF_TASK_NULL_SCHEDULE_EN_DATE');
522 fnd_msg_pub.add;
523 x_return_status := fnd_api.g_ret_sts_unexp_error;
524 END IF;
525
526 x_calendar_end_date := p_scheduled_end_date;
527
528 ELSIF rec_status.end_date_type = G_AE -- Actual End Date
529 THEN
530 IF p_actual_end_date IS NULL
531 THEN
532 -- If date type is deriven by actual end date and its value is null
533 -- then throw an error "The actual end date must be provided."
534 fnd_message.set_name ('JTF', 'JTF_TASK_NULL_ACTUAL_EN_DATE');
535 fnd_msg_pub.add;
536 x_return_status := fnd_api.g_ret_sts_unexp_error;
537 END IF;
538
539 x_calendar_end_date := p_actual_end_date;
540 END IF;
541
542 IF x_return_status = fnd_api.g_ret_sts_unexp_error
543 THEN
544 RAISE fnd_api.g_exc_unexpected_error;
545 END IF;
546
547 EXCEPTION
548 WHEN fnd_api.g_exc_unexpected_error THEN
549 x_return_status := fnd_api.g_ret_sts_unexp_error;
550
551 WHEN OTHERS THEN
552 x_return_status := fnd_api.g_ret_sts_unexp_error;
553 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
554 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
555 fnd_msg_pub.add;
556 END set_start_n_due_date;
557
558 ------------------------------------------------------
559 -- For enhancement 2734020
560 ------------------------------------------------------
561 FUNCTION get_last_number(p_sequence_name IN VARCHAR2)
562 RETURN NUMBER
563 IS
564 l_return_status BOOLEAN;
565 l_status VARCHAR2(1);
566 l_oracle_schema VARCHAR2(30);
567 l_industry VARCHAR2(1);
568
569 CURSOR c_seq IS
570 SELECT last_number
571 FROM all_sequences
572 WHERE sequence_name = p_sequence_name
573 AND sequence_owner = l_oracle_schema;
574
575 rec_seq c_seq%ROWTYPE;
576 BEGIN
577 l_return_status := FND_INSTALLATION.GET_APP_INFO(
578 application_short_name => 'JTF',
579 status => l_status,
580 industry => l_industry,
581 oracle_schema => l_oracle_schema);
582
583 if (NOT l_return_status) or (l_oracle_schema IS NULL)
584 then
585 -- defaulted to the JTF
586 l_oracle_schema := 'JTF';
587 end if;
588
589 OPEN c_seq;
590 FETCH c_seq INTO rec_seq;
591
592 IF c_seq%NOTFOUND
593 THEN
594 CLOSE c_seq;
595 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_SEQ');
596 fnd_message.set_token ('P_SEQ', p_sequence_name);
597 fnd_msg_pub.add;
598 raise fnd_api.g_exc_unexpected_error;
599 END IF;
600 CLOSE c_seq;
601
602 RETURN rec_seq.last_number;
603 END get_last_number;
604
605 -----------
606 ------------- For Bug 2786689 (CYCLIC TASK) ..
607 -----------
608 PROCEDURE validate_cyclic_task (
609 p_task_id IN NUMBER,
610 p_parent_task_id IN NUMBER,
611 x_return_status OUT NOCOPY VARCHAR2
612 )
613 IS
614 CURSOR c_cyclic_task
615 IS
616 SELECT task_id , parent_task_id , level
617 FROM jtf_tasks_b
618 START WITH task_id = p_task_id
619 CONNECT BY PRIOR task_id = parent_task_id ;
620
621 cyclic_task_rec c_cyclic_task%ROWTYPE;
622 BEGIN
623 x_return_status := fnd_api.g_ret_sts_success;
624
625 IF (p_parent_task_id IS NOT NULL)
626 THEN
627 FOR cyclic_task_rec IN c_cyclic_task
628 LOOP
629 IF (p_parent_task_id = cyclic_task_rec.task_id)
630 THEN
631 x_return_status := fnd_api.g_ret_sts_unexp_error;
632 fnd_message.set_name ('JTF', 'JTF_TASK_CYCLIC_TASKS');
633 fnd_message.set_token ('P_TASK_NAME', jtf_task_utl_ext.get_task_name(p_task_id));
634 fnd_message.set_token ('P_PARENT_TASK_NAME', jtf_task_utl_ext.get_task_name(p_parent_task_id));
635 fnd_msg_pub.add;
636 END IF;
637 END LOOP;
638
639 END IF;
640 END;
641
642 ------------------------------------------------------
643 -- For bug 2891531
644 ------------------------------------------------------
645 PROCEDURE update_object_code (
646 p_task_id IN NUMBER
647 ,p_old_object_code IN VARCHAR2
648 ,p_new_object_code IN VARCHAR2
649 ,p_old_object_id IN NUMBER
650 ,p_new_object_id IN NUMBER
651 ,p_new_object_name IN VARCHAR2
652 ,x_return_status OUT NOCOPY VARCHAR2
653 ,x_msg_count OUT NOCOPY NUMBER
654 ,x_msg_data OUT NOCOPY VARCHAR2
655 )
656 IS
657 CURSOR c_ref (b_task_id jtf_tasks_b.task_id%type,
658 b_source_id hz_parties.party_id%type) IS
659 SELECT task_reference_id, object_version_number
660 FROM jtf_task_references_b
661 WHERE task_id = b_task_id
662 AND object_id = b_source_id;
663
664 l_task_ref_id NUMBER;
665 l_obj_version_number NUMBER;
666 BEGIN
667 x_return_status := fnd_api.g_ret_sts_success;
668
669 IF p_old_object_code NOT IN ('TASK', 'APPOINTMENT')
670 THEN
671 IF (NVL(p_new_object_id, 0) <> fnd_api.g_miss_num AND
672 NVL(p_new_object_id, 0) <> NVL(p_old_object_id, 0))
673 THEN
674 -----------------------------
675 -- Delete the old reference
676 -----------------------------
677 IF p_old_object_code IN ('PARTY')
678 THEN
679 -- delete the old one
680 jtf_task_utl.delete_party_reference(
681 p_reference_from => 'TASK',
682 p_task_id => p_task_id,
683 p_party_id => p_old_object_id,
684 x_msg_count => x_msg_count,
685 x_msg_data => x_msg_data,
686 x_return_status => x_return_status
687 );
688 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
689 THEN
690 RAISE fnd_api.g_exc_unexpected_error;
691 END IF;
692 ELSE -- other than party Relation, Person, Organization 2102281
693 OPEN c_ref (p_task_id, p_old_object_id);
694 FETCH c_ref INTO l_task_ref_id, l_obj_version_number;
695 CLOSE c_ref;
696
697 jtf_task_utl.g_show_error_for_dup_reference := FALSE;
698
699 jtf_task_references_pub.delete_references (
700 p_api_version => 1.0,
701 p_init_msg_list => fnd_api.g_false,
702 p_commit => fnd_api.g_false,
703 p_object_version_number => l_obj_version_number,
704 p_task_reference_id => l_task_ref_id,
705 x_return_status => x_return_status,
706 x_msg_count => x_msg_count,
707 x_msg_data => x_msg_data
708 );
709
710 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
711 THEN
712 RAISE fnd_api.g_exc_unexpected_error;
713 END IF;
714 END IF;
715 END IF;
716 END IF;
717
718 IF p_new_object_code NOT IN ('TASK', 'APPOINTMENT')
719 THEN
720 --------------------------
721 -- Create a new reference
722 --------------------------
723 IF (NVL(p_new_object_id, 0) <> fnd_api.g_miss_num AND
724 NVL(p_new_object_id, 0) <> NVL(p_old_object_id, 0))
725 THEN
726 IF p_new_object_code IN ('PARTY')
727 THEN
728 -- create a new one
729 jtf_task_utl.create_party_reference(
730 p_reference_from => 'TASK',
731 p_task_id => p_task_id,
732 p_party_id => p_new_object_id,
733 x_msg_count => x_msg_count,
734 x_msg_data => x_msg_data,
735 x_return_status => x_return_status
736 );
737
738 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
739 THEN
740 RAISE fnd_api.g_exc_unexpected_error;
741 END IF;
742 ELSE -- other than party Relation, Person, Organization 2102281
743 jtf_task_utl.g_show_error_for_dup_reference := False;
744
745 jtf_task_references_pvt.create_references (
746 p_api_version => 1.0,
747 p_init_msg_list => fnd_api.g_false,
748 p_commit => fnd_api.g_false,
749 p_task_id => p_task_id,
750 p_object_type_code => p_new_object_code,
751 p_object_name => p_new_object_name,
752 p_object_id => p_new_object_id,
753 x_return_status => x_return_status,
754 x_msg_count => x_msg_count,
755 x_msg_data => x_msg_data,
756 x_task_reference_id => l_task_ref_id
757 );
758
759 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
760 THEN
761 RAISE fnd_api.g_exc_unexpected_error;
762 END IF;
763 END IF;
764 END IF;
765 END IF;
766
767 EXCEPTION
768 WHEN OTHERS THEN
769 x_return_status := fnd_api.g_ret_sts_unexp_error;
770 END update_object_code;
771
772 -- For Fix Bug 2896532
773 FUNCTION get_object_details (p_object_code IN VARCHAR2
774 ,p_object_id IN NUMBER)
775 RETURN VARCHAR2
776 IS
777 CURSOR c_object IS
778 SELECT select_id, select_details, from_table, where_clause
779 FROM jtf_objects_b
780 WHERE object_code = p_object_code;
781
782 l_id_column jtf_objects_b.select_id%TYPE;
783 l_detail_column jtf_objects_b.select_details%TYPE;
784 l_from_clause jtf_objects_b.from_table%TYPE;
785 l_where_clause jtf_objects_b.where_clause%TYPE;
786
787 l_object_details VARCHAR2(2000);
788 sql_stmt VARCHAR2(2000);
789 BEGIN
790 OPEN c_object;
791 FETCH c_object
792 INTO l_id_column
793 , l_detail_column
794 , l_from_clause
795 , l_where_clause;
796
797 IF c_object%NOTFOUND
798 THEN
799 CLOSE c_object;
800 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_OBJECT_CODE');
801 fnd_message.set_token ('P_OBJECT_CODE', p_object_code);
802 fnd_msg_pub.add;
803 RAISE fnd_api.g_exc_unexpected_error;
804 END IF;
805 CLOSE c_object;
806
807 IF l_detail_column IS NOT NULL
808 THEN
809 -- SELECT DECODE (l_where_clause, NULL, ' ', l_where_clause || ' AND ')
810 -- INTO l_where_clause
811 -- FROM dual;
812
813 -- Fix for bug 2932012
814 IF (l_where_clause IS NULL)
815 THEN
816 l_where_clause := ' ';
817 ELSE
818 l_where_clause := l_where_clause || ' AND ';
819 END IF;
820
821 sql_stmt := ' SELECT ' || l_detail_column ||
822 ' FROM ' || l_from_clause ||
823 ' WHERE ' || l_where_clause ||
824 l_id_column ||' = :object_id ';
825
826 EXECUTE IMMEDIATE sql_stmt
827 INTO l_object_details
828 USING p_object_id;
829 END IF;
830
831 RETURN l_object_details;
832
833 EXCEPTION
834 WHEN fnd_api.g_exc_unexpected_error THEN
835 RETURN NULL;
836
837 WHEN NO_DATA_FOUND THEN
838 RETURN NULL;
839
840 WHEN OTHERS THEN
841 RETURN NULL;
842
843 END get_object_details;
844
845 --Bug 2786689
846 FUNCTION get_task_name (p_task_id IN NUMBER)
847 RETURN VARCHAR2
848 AS
849 l_task_name jtf_tasks_vl.task_name%TYPE;
850 BEGIN
851 IF p_task_id IS NULL
852 THEN
853 RETURN NULL;
854 ELSE
855 SELECT task_name
856 INTO l_task_name
857 FROM jtf_tasks_vl
858 WHERE task_id = p_task_id;
859 END IF;
860
861 RETURN l_task_name;
862 EXCEPTION
863 WHEN OTHERS
864 THEN
865 RETURN NULL;
866 END get_task_name;
867
868 /*
869 Function added for bug #3360228 - extended from
870 jtf_task_utl.check_duplicate_reference.
871 */
872 FUNCTION check_dup_reference_for_update (
873 p_task_reference_id jtf_task_references_b.task_reference_id%type,
874 p_task_id jtf_tasks_b.task_id%type,
875 p_object_id hz_relationships.object_id%type,
876 p_object_type_code jtf_task_references_b.object_type_code%type)
877 return boolean
878 is
879
880 x_count NUMBER := 0;
881 x_return_value boolean := true;
882
883 begin
884
885 /*
886 If a reference is existing with the same task_refernce_id,
887 it shouldn't be treated as a duplicate when validating for
888 update. Added task_reference_id to the whereclause for
889 eliminate itself.
890 */
891
892 select count(object_id)
893 INTO x_count
894 FROM JTF_TASK_REFERENCES_b
895 WHERE task_reference_id <> p_task_reference_id
896 AND task_id = p_task_id
897 AND object_id = p_object_id
898 AND object_type_code = p_object_type_code
899 AND rownum = 1;
900
901 if x_count > 0 then
902 x_return_value := false;
903 else
904 x_return_value := true;
905 end if;
906
907 return x_return_value;
908
909 end check_dup_reference_for_update;
910
911 END jtf_task_utl_ext;
[Home] [Help]