[Home] [Help]
PACKAGE BODY: APPS.CSF_TASKS_PUB
Source
1 PACKAGE BODY csf_tasks_pub AS
2 /* $Header: CSFPTSKB.pls 120.49.12010000.4 2009/02/18 04:13:44 venjayar ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'CSF_TASKS_PUB';
5
6 -- Task Types
7 g_dep_task_type_id CONSTANT NUMBER := 20;
8 g_arr_task_type_id CONSTANT NUMBER := 21;
9 g_esc_task_type_id CONSTANT NUMBER := 22;
10
11 /*-- Task Status Propagation Constants
12 g_working_bitcode CONSTANT NUMBER := 001; -- 000000001
13 g_assigned_bitcode CONSTANT NUMBER := 003; -- 000000011
14 g_planned_bitcode CONSTANT NUMBER := 007; -- 000000111
15 g_completed_bitcode CONSTANT NUMBER := 009; -- 000001001
16 g_closed_bitcode CONSTANT NUMBER := 025; -- 000011001
17 g_onhold_bitcode CONSTANT NUMBER := 032; -- 000100000
18 g_rejected_bitcode CONSTANT NUMBER := 096; -- 001100000
19 g_cancelled_bitcode CONSTANT NUMBER := 224; -- 011100000
20 g_start_bitcode CONSTANT NUMBER := 511; -- 111111111*/
21
22
23 -- Task Status Propagation Constants
24 g_working_bitcode CONSTANT NUMBER := 001; -- 000000001
25 g_assigned_bitcode CONSTANT NUMBER := 007; -- 000000111
26 g_planned_bitcode CONSTANT NUMBER := 015; -- 000001111
27 g_completed_bitcode CONSTANT NUMBER := 0017; -- 000010001
28 g_closed_bitcode CONSTANT NUMBER := 049; -- 000110001
29
30 --------Variables added for the bug 6646890---------------------
31 g_accepted_bitcode CONSTANT NUMBER := 003; -- 000000011
32 ---------------------------------------------------------
33
34 g_onhold_bitcode CONSTANT NUMBER := 064; -- 001000000
35 g_rejected_bitcode CONSTANT NUMBER := 192; -- 011000000
36 g_cancelled_bitcode CONSTANT NUMBER := 448; -- 111000000
37 g_start_bitcode CONSTANT NUMBER := 511; -- 111111111
38
39 -- Default Values from the Profiles
40 g_plan_scope CONSTANT NUMBER := fnd_profile.VALUE ('PLANSCOPE');
41
42 g_inplanning CONSTANT NUMBER := fnd_profile.value('CSF_DEFAULT_TASK_INPLANNING_STATUS');
43 g_assigned CONSTANT NUMBER := fnd_profile.value('CSF_DEFAULT_TASK_ASSIGNED_STATUS');
44 g_working CONSTANT NUMBER := fnd_profile.value('CSF_DEFAULT_TASK_WORKING_STATUS');
45 g_cancelled CONSTANT NUMBER := fnd_profile.value('CSF_DEFAULT_TASK_CANCELLED_STATUS');
46 g_unscheduled CONSTANT NUMBER := fnd_profile.value('CSF_DEFAULT_TASK_UNSCHEDULED_STATUS');
47
48 g_default_uom CONSTANT VARCHAR2(3) := fnd_profile.value('CSF_DEFAULT_EFFORT_UOM');
49 g_overtime CONSTANT NUMBER := NVL(fnd_profile.value('MAXOVERTIME'), 0) / (24 * 60);
50
51 TYPE number_tbl_type IS TABLE OF NUMBER
52 INDEX BY BINARY_INTEGER;
53
54 /*******************************************************************************
55 * Private Functions and Procedures *
56 ********************************************************************************/
57 FUNCTION is_cancel_status (p_status_id jtf_task_statuses_b.task_status_id%TYPE)
58 RETURN BOOLEAN IS
59 CURSOR c_cancelled_flag IS
60 SELECT task_status_id
61 FROM jtf_task_statuses_b
62 WHERE task_status_id = p_status_id
63 AND NVL (cancelled_flag, 'N') = 'Y';
64 BEGIN
65 FOR v_cancelled_flag IN c_cancelled_flag LOOP
66 RETURN TRUE;
67 END LOOP;
68 RETURN FALSE;
69 END is_cancel_status;
70
71 FUNCTION has_field_service_rule (p_task_type_id NUMBER)
72 RETURN VARCHAR2 IS
73 CURSOR c_task_type IS
74 SELECT task_type_id
75 FROM jtf_task_types_b
76 WHERE rule = 'DISPATCH'
77 AND NVL (schedule_flag, 'N') = 'Y'
78 AND task_type_id = p_task_type_id;
79 BEGIN
80 FOR v_task_type IN c_task_type LOOP
81 RETURN fnd_api.g_true;
82 END LOOP;
83 RETURN fnd_api.g_false;
84 END has_field_service_rule;
85
86 FUNCTION has_schedulable_status (p_task_status_id NUMBER)
87 RETURN BOOLEAN IS
88 CURSOR c_task_status IS
89 SELECT task_status_id
90 FROM jtf_task_statuses_b
91 WHERE NVL (schedulable_flag, 'N') = 'Y'
92 AND task_status_id = p_task_status_id;
93 BEGIN
94 FOR v_task_status IN c_task_status LOOP
95 RETURN TRUE;
96 END LOOP;
97 RETURN FALSE;
98 END has_schedulable_status;
99
100 FUNCTION task_number (p_task_id IN NUMBER)
101 RETURN VARCHAR2 IS
102 CURSOR c_number IS
103 SELECT task_number
104 FROM jtf_tasks_b
105 WHERE task_id = p_task_id;
106 l_task_number jtf_tasks_b.task_number%TYPE;
107 BEGIN
108 OPEN c_number;
109 FETCH c_number INTO l_task_number;
110 CLOSE c_number;
111 RETURN l_task_number;
112 END task_number;
113
114 FUNCTION is_debrief_closed(p_task_assignment_id NUMBER)
115 RETURN BOOLEAN IS
116 CURSOR c_debrief_status IS
117 SELECT NVL (cdh.processed_flag, 'PENDING') debrief_status
118 FROM csf_debrief_headers cdh
119 WHERE cdh.task_assignment_id = p_task_assignment_id;
120 BEGIN
121 FOR v_debrief_status IN c_debrief_status LOOP
122 IF v_debrief_status.debrief_status <> 'COMPLETED' THEN
123 RETURN FALSE;
124 END IF;
125 END LOOP;
126 RETURN TRUE;
127 END is_debrief_closed;
128
129 FUNCTION check_schedulable(
130 p_deleted_flag IN VARCHAR2
131 , p_planned_start_date IN DATE
132 , p_planned_end_date IN DATE
133 , p_planned_effort IN NUMBER
134 , p_task_type_id IN NUMBER
135 , p_task_status_id IN NUMBER
136 , x_reason_code OUT NOCOPY VARCHAR2
137 ) RETURN BOOLEAN IS
138 BEGIN
139 x_reason_code := NULL;
140 IF p_deleted_flag = 'Y' THEN
141 x_reason_code := 'CSF_DELETED_TASK';
142 ELSIF has_field_service_rule (p_task_type_id) = fnd_api.g_false THEN
143 x_reason_code := 'CSF_NON_FS_TASK';
144 ELSIF (p_planned_start_date IS NULL OR p_planned_end_date IS NULL) THEN
145 x_reason_code := 'CSF_PLANNED_DATE_NOT_SET';
146 ELSIF p_planned_effort IS NULL THEN
147 x_reason_code := 'CSF_PLANNED_EFFORT_NOT_SET';
148 ELSIF NOT has_schedulable_status (p_task_status_id) THEN
149 x_reason_code := 'CSF_STATUS_NOT_SCHEDULABLE';
150 END IF;
151 RETURN x_reason_code IS NULL;
152 END check_schedulable;
153
154
155 /*******************************************************************************
156 * Public Functions and Procedures *
157 ********************************************************************************/
158
159 FUNCTION get_task_status_name (p_task_status_id NUMBER)
160 RETURN VARCHAR2 IS
161 l_return_value VARCHAR2 (30);
162
163 CURSOR c_name IS
164 SELECT NAME
165 FROM jtf_task_statuses_vl
166 WHERE task_status_id = p_task_status_id;
167 BEGIN
168 OPEN c_name;
169 FETCH c_name INTO l_return_value;
170 CLOSE c_name;
171
172 RETURN l_return_value;
173 END get_task_status_name;
174
175 FUNCTION get_dep_task_type_id RETURN NUMBER IS
176 BEGIN
177 RETURN g_dep_task_type_id;
178 END get_dep_task_type_id;
179
180 FUNCTION get_arr_task_type_id RETURN NUMBER IS
181 BEGIN
182 RETURN g_arr_task_type_id;
183 END get_arr_task_type_id;
184
185 -- Validate Field Service State Transitions
186 FUNCTION validate_state_transition (
187 p_state_type VARCHAR2
188 , p_old_status_id NUMBER
189 , p_new_status_id NUMBER
190 )
191 RETURN VARCHAR2 IS
192 -- Validation when new object
193 CURSOR c_valid_new_trans IS
194 SELECT NULL
195 FROM jtf_state_responsibilities re
196 , jtf_state_rules_b ru
197 , jtf_state_transitions tr
198 WHERE (re.responsibility_id = fnd_global.resp_id OR fnd_global.resp_id = -1)
199 AND re.rule_id = ru.rule_id
200 AND ru.state_type = p_state_type
201 AND ru.rule_id = tr.rule_id
202 AND tr.initial_state_id = p_new_status_id;
203
204 -- Validation when existing object
205 CURSOR c_valid_existing_trans IS
206 SELECT NULL
207 FROM jtf_state_responsibilities re
208 , jtf_state_rules_b ru
209 , jtf_state_transitions tr
210 WHERE (re.responsibility_id = fnd_global.resp_id OR fnd_global.resp_id = -1)
211 AND re.rule_id = ru.rule_id
212 AND ru.state_type = p_state_type
213 AND ru.rule_id = tr.rule_id
214 AND tr.initial_state_id = p_old_status_id
215 AND tr.final_state_id = p_new_status_id;
216
217 l_dummy VARCHAR2(1);
218 l_transition_valid VARCHAR2(1);
219 BEGIN
220 l_transition_valid := fnd_api.g_false;
221
222 -- If the new Status eqauls the old Status... return Valid.
223 IF p_new_status_id = p_old_status_id THEN
224 l_transition_valid := fnd_api.g_true;
225 ELSIF p_old_status_id IS NULL THEN
226 OPEN c_valid_new_trans;
227 FETCH c_valid_new_trans INTO l_dummy;
228 IF c_valid_new_trans%FOUND THEN
229 l_transition_valid := fnd_api.g_true;
230 END IF;
231 CLOSE c_valid_new_trans;
232 ELSE
233 OPEN c_valid_existing_trans;
234 FETCH c_valid_existing_trans INTO l_dummy;
235 IF c_valid_existing_trans%FOUND THEN
236 l_transition_valid := fnd_api.g_true;
237 END IF;
238 CLOSE c_valid_existing_trans;
239 END IF;
240 RETURN l_transition_valid;
241 END validate_state_transition;
242
243 /**
244 * Used to retrieve the list of valid Task Statuses the Task can take either from
245 * from its current status or when it is created anew. It gives a list of Task
246 * Status Names rather than Task Status IDs.
247 */
248 FUNCTION get_valid_statuses (
249 p_state_type VARCHAR2
250 , p_old_status_id NUMBER
251 )
252 RETURN VARCHAR2 IS
253 l_return_value VARCHAR2 (2000);
254
255 -- Get valid statuses when the object is creeted for the first time
256 CURSOR c_valid_new_trans IS
257 SELECT DISTINCT tr.initial_state_id, ts.name
258 FROM jtf_state_responsibilities re
259 , jtf_state_rules_b ru
260 , jtf_state_transitions tr
261 , jtf_task_statuses_tl ts
262 WHERE (re.responsibility_id = fnd_global.resp_id OR fnd_global.resp_id = -1)
263 AND re.rule_id = ru.rule_id
264 AND ru.state_type = p_state_type
265 AND ru.rule_id = tr.rule_id
266 AND ts.task_status_id = tr.initial_state_id
267 AND ts.language = userenv('LANG');
268
269 -- Get valid statuses from an existing status
270 CURSOR c_valid_existing_trans IS
271 SELECT DISTINCT tr.final_state_id, ts.name
272 FROM jtf_state_responsibilities re
273 , jtf_state_rules_b ru
274 , jtf_state_transitions tr
275 , jtf_task_statuses_tl ts
276 WHERE (re.responsibility_id = fnd_global.resp_id OR fnd_global.resp_id = -1)
277 AND re.rule_id = ru.rule_id
278 AND ru.state_type = p_state_type
279 AND ru.rule_id = tr.rule_id
280 AND tr.initial_state_id = p_old_status_id
281 AND ts.task_status_id = tr.final_state_id
282 AND ts.language = userenv('LANG');
283 BEGIN
284 IF p_old_status_id IS NULL THEN
285 FOR v_valid_new_trans IN c_valid_new_trans LOOP
286 l_return_value := l_return_value || fnd_global.local_chr(10) || v_valid_new_trans.name;
287 END LOOP;
288 ELSE
289 FOR v_valid_existing_trans IN c_valid_existing_trans LOOP
290 l_return_value := l_return_value || fnd_global.local_chr(10) || v_valid_existing_trans.name;
291 END LOOP;
292 END IF;
293
294 RETURN l_return_value;
295 END get_valid_statuses;
296
297 -- Clubs the operation of the above functions validate_state_transition and
298 -- get_valid_statuses into one procedure.
299 PROCEDURE validate_status_change(p_old_status_id NUMBER, p_new_status_id NUMBER) IS
300 l_trans_valid VARCHAR2(1);
301 l_valid_statuses VARCHAR2(2000);
302 BEGIN
303 IF p_new_status_id IS NULL THEN
304 RETURN;
305 END IF;
306
307 IF p_new_status_id = p_old_status_id THEN
308 RETURN;
309 END IF;
310
311 l_trans_valid := validate_state_transition ('TASK_STATUS', p_old_status_id, p_new_status_id);
312 IF l_trans_valid = fnd_api.g_false THEN
313 l_valid_statuses := get_valid_statuses ('TASK_STATUS', p_old_status_id);
314 IF l_valid_statuses IS NULL THEN
315 fnd_message.set_name ('CSF', 'CSF_NO_STATE_TRANSITION');
316 ELSE
317 fnd_message.set_name ('CSF', 'CSF_INVALID_STATE_TRANSITION');
318 fnd_message.set_token ('P_VALID_STATUSES', l_valid_statuses);
319 END IF;
320 fnd_message.set_token ('P_NEW_STATUS', get_task_status_name (p_new_status_id));
321 fnd_msg_pub.ADD;
322 RAISE fnd_api.g_exc_error;
323 END IF;
324 END validate_status_change;
325
326 /**
327 * Checks whether the given Task is closable.
328 * @returns TRUE If Task is closable
329 * @returns FALSE If Task is not closable
330 */
331 FUNCTION is_task_closable (
332 x_return_status OUT NOCOPY VARCHAR2
333 , x_msg_count OUT NOCOPY NUMBER
334 , x_msg_data OUT NOCOPY VARCHAR2
335 , p_task_id IN NUMBER
336 )
337 RETURN BOOLEAN IS
338 l_api_name CONSTANT VARCHAR2 (30) := 'IS_TASK_CLOSABLE';
339
340 CURSOR c_task_details IS
341 SELECT task_status_id
342 FROM jtf_tasks_b
343 WHERE task_id = p_task_id;
344
345 -- Cursor to get all the Task Assignments for the Task to be closed.
346 CURSOR c_task_assignments IS
347 SELECT ta.task_assignment_id
348 , t.scheduled_start_date
349 , t.scheduled_end_date
350 , NVL (ts.closed_flag, 'N') closed_flag
351 , NVL (ts.cancelled_flag, 'N') cancelled_flag
352 , NVL (ts.completed_flag, 'N') completed_flag
353 , NVL (ts.rejected_flag, 'N') rejected_flag
354 FROM jtf_task_assignments ta, jtf_tasks_b t, jtf_task_statuses_b ts
355 WHERE ta.task_id = t.task_id
356 AND t.task_id = p_task_id
357 AND assignment_status_id = ts.task_status_id;
358
359 l_old_status_id NUMBER;
360 l_close_status_id NUMBER;
361 l_valid_statuses VARCHAR2 (2000);
362 l_update_schedulable_task VARCHAR2(3);
363 BEGIN
364 -- Initialize API return status to success
365 x_return_status := fnd_api.g_ret_sts_success;
366
367 OPEN c_task_details;
368 FETCH c_task_details INTO l_old_status_id;
369 IF c_task_details%NOTFOUND THEN
370 CLOSE c_task_details;
371 RAISE NO_DATA_FOUND;
372 END IF;
373 CLOSE c_task_details;
374
375 -- Before a Task can be closed, there are some checks that needs to be done
376
377 -- Check whether the State Transition is valid.
378 l_close_status_id := fnd_profile.VALUE ('CSF_DFLT_AUTO_CLOSE_TASK_STATUS');
379 IF validate_state_transition ('TASK_STATUS', l_old_status_id, l_close_status_id) = fnd_api.g_false THEN
380 l_valid_statuses := get_valid_statuses ('TASK_STATUS', l_old_status_id);
381 IF l_valid_statuses IS NULL THEN
382 fnd_message.set_name ('CSF', 'CSF_NO_STATE_TRANSITION');
383 ELSE
384 fnd_message.set_name ('CSF', 'CSF_INVALID_STATE_TRANSITION');
385 fnd_message.set_token ('P_VALID_STATUSES', l_valid_statuses);
386 END IF;
387 fnd_message.set_token ('P_NEW_STATUS', get_task_status_name (l_close_status_id));
388 fnd_msg_pub.ADD;
389 RAISE fnd_api.g_exc_error;
390 END IF;
391
392 -- Check whether the Assignments and the associated Debriefs have been closed
393 l_update_schedulable_task := NVL(fnd_profile.value('CSFW_UPD_SCHEDULABLE'), 'NO');
394 FOR v_task_assignment IN c_task_assignments LOOP
395
396 -- Check whether the Task Assignment is still Open.
397 IF v_task_assignment.closed_flag = 'N'
398 AND v_task_assignment.cancelled_flag = 'N'
399 AND v_task_assignment.completed_flag = 'N'
400 AND v_task_assignment.rejected_flag = 'N' THEN
401 fnd_message.set_name('CSF', 'CSF_CLOSED_TASK');
402 fnd_msg_pub.ADD;
403 RAISE fnd_api.g_exc_error;
404 END IF;
405
406 -- Check whether the Debrief is closed if Task Assignment is not open
407 -- and only when the profile "CSFW: Update Schedulable Task" is set to Yes
408 IF l_update_schedulable_task = 'YES' THEN
409 IF NOT is_debrief_closed(v_task_assignment.task_assignment_id) THEN
410 fnd_message.set_name('CSF', 'CSF_DEBRIEF_PENDING');
411 fnd_msg_pub.ADD;
412 RAISE fnd_api.g_exc_error;
413 END IF;
414 END IF;
415 END LOOP;
416
417 RETURN TRUE;
418 EXCEPTION
419 WHEN fnd_api.g_exc_error THEN
420 x_return_status := fnd_api.g_ret_sts_error;
421 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
422 RETURN FALSE;
423 WHEN fnd_api.g_exc_unexpected_error THEN
424 x_return_status := fnd_api.g_ret_sts_unexp_error;
425 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
426 RETURN FALSE;
427 WHEN OTHERS THEN
428 x_return_status := fnd_api.g_ret_sts_unexp_error;
429 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
430 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
431 END IF;
432 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
433 RETURN FALSE;
434 END is_task_closable;
435
436 /**
437 * Checks whether the given Task can be closed and returns True or False
438 * accordingly.
439 * @deprecated Use IS_TASK_CLOSABLE (SR Team is still calling this version)
440 */
441 FUNCTION task_is_closable (
442 x_return_status OUT NOCOPY VARCHAR2
443 , x_msg_count OUT NOCOPY NUMBER
444 , x_msg_data OUT NOCOPY VARCHAR2
445 , p_task_id IN NUMBER
446 )
447 RETURN BOOLEAN IS
448 BEGIN
449 RETURN is_task_closable(
450 x_return_status => x_return_status
451 , x_msg_count => x_msg_count
452 , x_msg_data => x_msg_data
453 , p_task_id => p_task_id
454 );
455 END task_is_closable;
456
457
458
459 FUNCTION is_task_schedulable (
460 x_return_status OUT NOCOPY VARCHAR2
461 , x_msg_count OUT NOCOPY NUMBER
462 , x_msg_data OUT NOCOPY VARCHAR2
463 , p_task_id IN NUMBER
464 )
465 RETURN BOOLEAN IS
466 l_api_name CONSTANT VARCHAR2(30) := 'IS_TASK_SCHEDULABLE';
467
468 CURSOR c_task_details IS
469 SELECT task_type_id
470 , task_status_id
471 , planned_start_date
472 , planned_end_date
473 , planned_effort
474 , address_id
475 , deleted_flag
476 FROM jtf_tasks_b
477 WHERE task_id = p_task_id;
478
479 l_task_details c_task_details%ROWTYPE;
480 l_schedulable BOOLEAN;
481 l_message_name VARCHAR2(100);
482 BEGIN
483 -- Initialize API return status to success
484 x_return_status := fnd_api.g_ret_sts_success;
485
486 -- Fetching the Task Details
487 OPEN c_task_details;
488 FETCH c_task_details INTO l_task_details;
489 IF c_task_details%NOTFOUND THEN
490 CLOSE c_task_details;
491 RAISE NO_DATA_FOUND;
492 END IF;
493 CLOSE c_task_details;
494
495 l_schedulable := check_schedulable(
496 p_deleted_flag => l_task_details.deleted_flag
497 , p_planned_start_date => l_task_details.planned_start_date
498 , p_planned_end_date => l_task_details.planned_end_date
499 , p_planned_effort => l_task_details.planned_effort
500 , p_task_type_id => l_task_details.task_type_id
501 , p_task_status_id => l_task_details.task_status_id
502 , x_reason_code => l_message_name
503 );
504
505 IF NOT l_schedulable THEN
506 fnd_message.set_name('CSF', l_message_name);
507 fnd_msg_pub.ADD;
508 RAISE fnd_api.g_exc_error;
509 END IF;
510
511 RETURN TRUE;
512 EXCEPTION
513 WHEN fnd_api.g_exc_error THEN
514 x_return_status := fnd_api.g_ret_sts_error;
515 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
516 RETURN FALSE;
517 WHEN fnd_api.g_exc_unexpected_error THEN
518 x_return_status := fnd_api.g_ret_sts_unexp_error;
519 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
520 RETURN FALSE;
521 WHEN OTHERS THEN
522 x_return_status := fnd_api.g_ret_sts_unexp_error;
523 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
524 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
525 END IF;
526 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
527 RETURN FALSE;
528 END is_task_schedulable;
529
530 FUNCTION is_task_scheduled (
531 x_return_status OUT NOCOPY VARCHAR2
532 , x_msg_count OUT NOCOPY NUMBER
533 , x_msg_data OUT NOCOPY VARCHAR2
534 , p_task_id IN NUMBER
535 )
536 RETURN BOOLEAN IS
537 l_api_name CONSTANT VARCHAR2(30) := 'IS_TASK_SCHEDULED';
538
539 CURSOR c_task_ta_det IS
540 SELECT t.scheduled_start_date
541 , t.scheduled_end_date
542 , t.task_split_flag
543 , t.task_status_id
544 , ta.resource_id
545 FROM jtf_tasks_b t, jtf_task_assignments ta
546 WHERE ta.task_id = t.task_id AND t.task_id = p_task_id;
547
548 l_sched_start jtf_tasks_b.scheduled_start_date%TYPE;
549 l_sched_end jtf_tasks_b.scheduled_end_date%TYPE;
550 l_resource_id jtf_task_assignments.resource_id%TYPE;
551 l_split_flag jtf_tasks_b.task_split_flag%TYPE;
552 l_status_id jtf_task_statuses_b.task_status_id%TYPE;
553 BEGIN
554 -- Initialize API return status to success
555 x_return_status := fnd_api.g_ret_sts_success;
556
557 OPEN c_task_ta_det;
558 FETCH c_task_ta_det
559 INTO l_sched_start, l_sched_end, l_split_flag, l_status_id, l_resource_id;
560 IF c_task_ta_det%NOTFOUND THEN
561 CLOSE c_task_ta_det;
562 RAISE NO_DATA_FOUND;
563 END IF;
564 CLOSE c_task_ta_det;
565
566 IF l_split_flag IS NULL OR l_split_flag = 'D' THEN
567 IF (l_resource_id IS NOT NULL) AND NOT (is_cancel_status (l_status_id)) THEN
568 IF l_sched_start IS NOT NULL AND l_sched_end IS NOT NULL THEN
569 RETURN TRUE;
570 END IF;
571 END IF;
572 ELSE -- task_split_flag is 'M'
573 -- put the additional logic here asked from Max.
574 RETURN TRUE;
575 END IF;
576 RETURN FALSE;
577 EXCEPTION
578 WHEN OTHERS THEN
579 x_return_status := fnd_api.g_ret_sts_unexp_error;
580 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
581 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
582 END IF;
583 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
584 RETURN FALSE;
585 END is_task_scheduled;
586
587 /**
588 * Determines whether the given Task is escalated or not
589 */
590 FUNCTION is_task_escalated(p_task_id NUMBER)
591 RETURN BOOLEAN IS
592 l_ref_task_id NUMBER;
593 l_escalated NUMBER;
594
595 CURSOR c_task_ref IS
596 SELECT task_id
597 FROM jtf_task_references_b r
598 WHERE r.reference_code = 'ESC'
599 AND r.object_type_code = 'TASK'
600 AND r.object_id = p_task_id;
601
602 CURSOR c_esc(b_task_id NUMBER) IS
603 SELECT 1
604 FROM jtf_tasks_b t
605 , jtf_task_statuses_b s
606 WHERE t.task_id = b_task_id
607 AND t.task_type_id = g_esc_task_type_id
608 AND s.task_status_id = t.task_status_id
609 AND NVL(s.closed_flag, 'N') <> 'Y'
610 AND NVL(t.deleted_flag, 'N') <> 'Y';
611 BEGIN
612 -- Get the Reference Task to the given Task
613 OPEN c_task_ref;
614 FETCH c_task_ref INTO l_ref_task_id;
615 CLOSE c_task_ref;
616
617 IF l_ref_task_id IS NULL THEN
618 RETURN FALSE;
619 END IF;
620
621 -- Check whether the Reference object is an Escalation Task
622 OPEN c_esc(l_ref_task_id);
623 FETCH c_esc INTO l_escalated;
624 CLOSE c_esc;
625
626 RETURN (l_escalated IS NOT NULL);
627 EXCEPTION
628 WHEN OTHERS THEN
629 IF c_task_ref%ISOPEN THEN
630 CLOSE c_task_ref;
631 END IF;
632 IF c_esc%ISOPEN THEN
633 CLOSE c_esc;
634 END IF;
635 RETURN FALSE;
636 END is_task_escalated;
637
638 /**
639 * Closes an existing task
640 */
641 PROCEDURE close_task (
642 p_api_version IN NUMBER
643 , p_init_msg_list IN VARCHAR2
644 , p_commit IN VARCHAR2
645 , x_return_status OUT NOCOPY VARCHAR2
646 , x_msg_count OUT NOCOPY NUMBER
647 , x_msg_data OUT NOCOPY VARCHAR2
648 , p_task_id IN NUMBER
649 ) IS
650 l_api_version CONSTANT NUMBER := 1.0;
651 l_api_name CONSTANT VARCHAR2(30) := 'CLOSE_TASK';
652
653 l_close_status_id NUMBER;
654 l_object_version_number NUMBER;
655 BEGIN
656 -- Standard start of API savepoint
657 SAVEPOINT close_task_pub;
658
659 -- Standard call to check for call compatibility
660 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
661 RAISE fnd_api.g_exc_unexpected_error;
662 END IF;
663
664 -- Initialize message list if p_init_msg_list is set to TRUE
665 IF fnd_api.to_boolean (p_init_msg_list) THEN
666 fnd_msg_pub.initialize;
667 END IF;
668
669 -- Initialize API return status to success
670 x_return_status := fnd_api.g_ret_sts_success;
671
672 l_close_status_id := fnd_profile.VALUE ('CSF_DFLT_AUTO_CLOSE_TASK_STATUS');
673 update_task_status (
674 p_api_version => 1.0
675 , x_return_status => x_return_status
676 , x_msg_count => x_msg_count
677 , x_msg_data => x_msg_data
678 , p_task_id => p_task_id
679 , p_task_status_id => l_close_status_id
680 , p_object_version_number => l_object_version_number
681 );
682
683 IF x_return_status = fnd_api.g_ret_sts_error THEN
684 RAISE fnd_api.g_exc_error;
685 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
686 RAISE fnd_api.g_exc_unexpected_error;
687 END IF;
688
689 -- Standard check of p_commit
690 IF fnd_api.to_boolean (p_commit) THEN
691 COMMIT WORK;
692 END IF;
693 -- Standard call to get message count and if count is 1, get message info
694 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
695 EXCEPTION
696 WHEN fnd_api.g_exc_error THEN
697 ROLLBACK TO close_task_pub;
698 x_return_status := fnd_api.g_ret_sts_error;
699 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
700 WHEN fnd_api.g_exc_unexpected_error THEN
701 ROLLBACK TO close_task_pub;
702 x_return_status := fnd_api.g_ret_sts_unexp_error;
703 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
704 WHEN OTHERS THEN
705 x_return_status := fnd_api.g_ret_sts_unexp_error;
706 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
707 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
708 END IF;
709 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
710 ROLLBACK TO close_task_pub;
711 END close_task;
712
713 -- Determines which tasks in a PL/SQL table are modified
714 PROCEDURE identify_modified_tasks (
715 p_api_version IN NUMBER
716 , p_init_msg_list IN VARCHAR2
717 , x_return_status OUT NOCOPY VARCHAR2
718 , x_msg_count OUT NOCOPY NUMBER
719 , x_msg_data OUT NOCOPY VARCHAR2
720 , x_collection IN OUT NOCOPY tasks_tbl_type
721 , x_count OUT NOCOPY NUMBER
722 ) IS
723 l_api_version CONSTANT NUMBER := 1.0;
724 l_api_name CONSTANT VARCHAR2(30) := 'IDENTIFY_MODIFIED_TASKS';
725 l_idx PLS_INTEGER;
726
727 CURSOR c_task_info (p_row_id VARCHAR) IS
728 SELECT t.object_version_number
729 , t.task_status_id
730 , ts.name task_status_name
731 , t.scheduled_start_date
732 , t.scheduled_end_date
733 , t.task_split_flag
734 , t.parent_task_id
735 , ts.schedulable_flag ts_schedulable_flag
736 , ts.assigned_flag
737 , tt.schedule_flag tt_schedule_flag
738 , ta.resource_name
739 FROM jtf_tasks_b t
740 , csf_ct_task_assignments ta
741 , jtf_task_statuses_vl ts
742 , jtf_task_types_b tt
743 WHERE t.ROWID = CHARTOROWID (p_row_id)
744 AND ts.task_status_id = t.task_status_id
745 AND tt.task_type_id = t.task_type_id
746 AND ta.task_id (+) = t.task_id;
747
748 l_task_info c_task_info%ROWTYPE;
749 BEGIN
750 -- standard call to check for call compatibility
751 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
752 RAISE fnd_api.g_exc_unexpected_error;
753 END IF;
754
755 -- initialize message list if p_init_msg_list is set to true
756 IF fnd_api.to_boolean (p_init_msg_list) THEN
757 fnd_msg_pub.initialize;
758 END IF;
759
760 -- initialize api return status to success
761 x_return_status := fnd_api.g_ret_sts_success;
762
763 -- start processing
764 x_count := 0;
765 l_idx := x_collection.FIRST;
766 WHILE l_idx IS NOT NULL LOOP
767 -- take only schedulable tasks into account
768 -- IF ( NVL (x_collection(l_idx).status_schedulable_flag, 'N') = 'Y' --commented for ER 6360530
769 -- OR NVL (x_collection(l_idx).status_assigned_flag, 'N') = 'Y'
770 -- )
771 -- AND NVL (x_collection(l_idx).type_schedulable_flag, 'N') = 'Y' THEN
772 OPEN c_task_info (x_collection (l_idx).row_id);
773 FETCH c_task_info INTO l_task_info;
774
775 IF c_task_info%FOUND THEN
776 IF NVL (l_task_info.object_version_number, -1)
777 <> NVL(x_collection(l_idx).object_version_number, -1)
778 THEN
779 x_collection(l_idx).object_version_number := l_task_info.object_version_number;
780 x_collection(l_idx).task_status_id := l_task_info.task_status_id;
781 x_collection(l_idx).task_status := l_task_info.task_status_name;
782 x_collection(l_idx).scheduled_start_date := l_task_info.scheduled_start_date;
783 x_collection(l_idx).scheduled_end_date := l_task_info.scheduled_end_date;
784 x_collection(l_idx).status_schedulable_flag := l_task_info.ts_schedulable_flag;
785 x_collection(l_idx).type_schedulable_flag := l_task_info.tt_schedule_flag;
786 x_collection(l_idx).status_assigned_flag := l_task_info.assigned_flag;
787 x_collection(l_idx).resource_name := l_task_info.resource_name;
788 x_collection(l_idx).task_split_flag := l_task_info.task_split_flag;
789 x_collection(l_idx).parent_task_id := l_task_info.parent_task_id;
790 x_collection(l_idx).updated_flag := 'Y';
791 x_count := x_count + 1;
792 ELSE
793 /* reset updated flag if not different */
794 x_collection (l_idx).updated_flag := 'N';
795 END IF;
796 END IF;
797
798 CLOSE c_task_info;
799 --END IF;
800 l_idx := x_collection.NEXT (l_idx);
801 END LOOP;
802
803 -- standard call to get message count and if count is 1, get message info
804 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
805 EXCEPTION
806 WHEN OTHERS THEN
807 x_return_status := fnd_api.g_ret_sts_unexp_error;
808 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
809 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
810 END IF;
811 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
812 END identify_modified_tasks;
813
814 ---------------------------------------------------------------------------
815 -- validate_planned_dates
816 -- validate start / end, can not be in past, implement same rules as
817 -- Scheduler core:
818 -- 1. when start in past or null, then start becomes sysdate
819 -- 2. when end in past or null, then end becomes start + plan scope
820 --
821 -- x_start : planned start date to be validated and possibly adjusted
822 -- x_end : planned end date
823 ---------------------------------------------------------------------------
824 PROCEDURE validate_planned_dates (x_start IN OUT NOCOPY DATE, x_end IN OUT NOCOPY DATE) IS
825 BEGIN
826 IF x_start < SYSDATE OR x_start IS NULL THEN
827 x_start := SYSDATE;
828 END IF;
829 --
830 IF x_end < SYSDATE OR x_end IS NULL THEN
831 x_end := x_start + g_plan_scope;
832 END IF;
833 END validate_planned_dates;
834
835 PROCEDURE create_task (
836 p_api_version IN NUMBER
837 , p_init_msg_list IN VARCHAR2
838 , p_commit IN VARCHAR2
839 , x_return_status OUT NOCOPY VARCHAR2
840 , x_msg_count OUT NOCOPY NUMBER
841 , x_msg_data OUT NOCOPY VARCHAR2
842 , p_task_id IN NUMBER
843 , p_task_name IN VARCHAR2
844 , p_description IN VARCHAR2
845 , p_task_type_name IN VARCHAR2
846 , p_task_type_id IN NUMBER
847 , p_task_status_name IN VARCHAR2
848 , p_task_status_id IN NUMBER
849 , p_task_priority_name IN VARCHAR2
850 , p_task_priority_id IN NUMBER
851 , p_owner_type_name IN VARCHAR2
852 , p_owner_type_code IN VARCHAR2
853 , p_owner_id IN NUMBER
854 , p_owner_territory_id IN NUMBER
855 , p_owner_status_id IN NUMBER
856 , p_assigned_by_name IN VARCHAR2
857 , p_assigned_by_id IN NUMBER
858 , p_customer_number IN VARCHAR2
859 , p_customer_id IN NUMBER
860 , p_cust_account_number IN VARCHAR2
861 , p_cust_account_id IN NUMBER
862 , p_address_id IN NUMBER
863 , p_address_number IN VARCHAR2
864 , p_location_id IN NUMBER
865 , p_planned_start_date IN DATE
866 , p_planned_end_date IN DATE
867 , p_scheduled_start_date IN DATE
868 , p_scheduled_end_date IN DATE
869 , p_actual_start_date IN DATE
870 , p_actual_end_date IN DATE
871 , p_timezone_id IN NUMBER
872 , p_timezone_name IN VARCHAR2
873 , p_source_object_type_code IN VARCHAR2
874 , p_source_object_id IN NUMBER
875 , p_source_object_name IN VARCHAR2
876 , p_duration IN NUMBER
877 , p_duration_uom IN VARCHAR2
878 , p_planned_effort IN NUMBER
879 , p_planned_effort_uom IN VARCHAR2
880 , p_actual_effort IN NUMBER
881 , p_actual_effort_uom IN VARCHAR2
882 , p_percentage_complete IN NUMBER
883 , p_reason_code IN VARCHAR2
884 , p_private_flag IN VARCHAR2
885 , p_publish_flag IN VARCHAR2
886 , p_restrict_closure_flag IN VARCHAR2
887 , p_multi_booked_flag IN VARCHAR2
888 , p_milestone_flag IN VARCHAR2
889 , p_holiday_flag IN VARCHAR2
890 , p_billable_flag IN VARCHAR2
891 , p_bound_mode_code IN VARCHAR2
892 , p_soft_bound_flag IN VARCHAR2
893 , p_workflow_process_id IN NUMBER
894 , p_notification_flag IN VARCHAR2
895 , p_notification_period IN NUMBER
896 , p_notification_period_uom IN VARCHAR2
897 , p_alarm_start IN NUMBER
898 , p_alarm_start_uom IN VARCHAR2
899 , p_alarm_on IN VARCHAR2
900 , p_alarm_count IN NUMBER
901 , p_alarm_interval IN NUMBER
902 , p_alarm_interval_uom IN VARCHAR2
903 , p_palm_flag IN VARCHAR2
904 , p_wince_flag IN VARCHAR2
905 , p_laptop_flag IN VARCHAR2
906 , p_device1_flag IN VARCHAR2
907 , p_device2_flag IN VARCHAR2
908 , p_device3_flag IN VARCHAR2
909 , p_costs IN NUMBER
910 , p_currency_code IN VARCHAR2
911 , p_escalation_level IN VARCHAR2
912 , p_attribute1 IN VARCHAR2
913 , p_attribute2 IN VARCHAR2
914 , p_attribute3 IN VARCHAR2
915 , p_attribute4 IN VARCHAR2
916 , p_attribute5 IN VARCHAR2
917 , p_attribute6 IN VARCHAR2
918 , p_attribute7 IN VARCHAR2
919 , p_attribute8 IN VARCHAR2
920 , p_attribute9 IN VARCHAR2
921 , p_attribute10 IN VARCHAR2
922 , p_attribute11 IN VARCHAR2
923 , p_attribute12 IN VARCHAR2
924 , p_attribute13 IN VARCHAR2
925 , p_attribute14 IN VARCHAR2
926 , p_attribute15 IN VARCHAR2
927 , p_attribute_category IN VARCHAR2
928 , p_date_selected IN VARCHAR2
929 , p_category_id IN NUMBER
930 , p_show_on_calendar IN VARCHAR2
931 , p_task_assign_tbl IN jtf_tasks_pub.task_assign_tbl
932 , p_task_depends_tbl IN jtf_tasks_pub.task_depends_tbl
933 , p_task_rsrc_req_tbl IN jtf_tasks_pub.task_rsrc_req_tbl
934 , p_task_refer_tbl IN jtf_tasks_pub.task_refer_tbl
935 , p_task_dates_tbl IN jtf_tasks_pub.task_dates_tbl
936 , p_task_notes_tbl IN jtf_tasks_pub.task_notes_tbl
937 , p_task_recur_rec IN jtf_tasks_pub.task_recur_rec
938 , p_task_contacts_tbl IN jtf_tasks_pub.task_contacts_tbl
939 , p_template_id IN NUMBER
940 , p_template_group_id IN NUMBER
941 , p_enable_workflow IN VARCHAR2
942 , p_abort_workflow IN VARCHAR2
943 , p_task_split_flag IN VARCHAR2
944 , p_parent_task_number IN VARCHAR2
945 , p_parent_task_id IN NUMBER
946 , p_child_position IN VARCHAR2
947 , p_child_sequence_num IN NUMBER
948 , x_task_id OUT NOCOPY NUMBER
949 ) IS
950 l_api_version CONSTANT NUMBER := 1.0;
951 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TASK';
952
953 l_task_schedulable BOOLEAN;
954 l_reason_code VARCHAR2(100);
955 BEGIN
956 SAVEPOINT csf_create_task_pub;
957
958 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
959 RAISE fnd_api.g_exc_unexpected_error;
960 END IF;
961
962 IF fnd_api.to_boolean (p_init_msg_list) THEN
963 fnd_msg_pub.initialize;
964 END IF;
965
966 x_return_status := fnd_api.g_ret_sts_success;
967
968 l_task_schedulable := check_schedulable(
969 p_deleted_flag => 'N'
970 , p_planned_start_date => p_planned_start_date
971 , p_planned_end_date => p_planned_end_date
972 , p_planned_effort => p_planned_effort
973 , p_task_type_id => p_task_type_id
974 , p_task_status_id => p_task_status_id
975 , x_reason_code => l_reason_code
976 );
977
978 -- Task is not schedulable.
979 IF l_task_schedulable = FALSE OR x_return_status <> fnd_api.g_ret_sts_success THEN
980 fnd_message.set_name ('CSF', l_reason_code);
981 fnd_msg_pub.ADD;
982 RAISE fnd_api.g_exc_error;
983 END IF;
984
985 jtf_tasks_pub.create_task (
986 p_api_version => p_api_version
987 , p_init_msg_list => fnd_api.g_false
988 , p_commit => fnd_api.g_false
989 , x_return_status => x_return_status
990 , x_msg_count => x_msg_count
991 , x_msg_data => x_msg_data
992 , p_task_id => p_task_id
993 , p_task_name => p_task_name
994 , p_task_type_name => p_task_type_name
995 , p_task_type_id => p_task_type_id
996 , p_description => p_description
997 , p_task_status_name => p_task_status_name
998 , p_task_status_id => p_task_status_id
999 , p_task_priority_name => p_task_priority_name
1000 , p_task_priority_id => p_task_priority_id
1001 , p_owner_type_name => p_owner_type_name
1002 , p_owner_type_code => p_owner_type_code
1003 , p_owner_id => p_owner_id
1004 , p_owner_territory_id => p_owner_territory_id
1005 , p_assigned_by_name => p_assigned_by_name
1006 , p_assigned_by_id => p_assigned_by_id
1007 , p_customer_number => p_customer_number
1008 , p_customer_id => p_customer_id
1009 , p_cust_account_number => p_cust_account_number
1010 , p_cust_account_id => p_cust_account_id
1011 , p_address_id => p_address_id
1012 , p_address_number => p_address_number
1013 , p_location_id => p_location_id
1014 , p_planned_start_date => p_planned_start_date
1015 , p_planned_end_date => p_planned_end_date
1016 , p_scheduled_start_date => p_scheduled_start_date
1017 , p_scheduled_end_date => p_scheduled_end_date
1018 , p_actual_start_date => p_actual_start_date
1019 , p_actual_end_date => p_actual_end_date
1020 , p_timezone_id => p_timezone_id
1021 , p_timezone_name => p_timezone_name
1022 , p_source_object_type_code => p_source_object_type_code
1023 , p_source_object_id => p_source_object_id
1024 , p_source_object_name => p_source_object_name
1025 , p_duration => p_duration
1026 , p_duration_uom => p_duration_uom
1027 , p_planned_effort => p_planned_effort
1028 , p_planned_effort_uom => p_planned_effort_uom
1029 , p_actual_effort => p_actual_effort
1030 , p_actual_effort_uom => p_actual_effort_uom
1031 , p_percentage_complete => p_percentage_complete
1032 , p_reason_code => p_reason_code
1033 , p_private_flag => p_private_flag
1034 , p_publish_flag => p_publish_flag
1035 , p_restrict_closure_flag => p_restrict_closure_flag
1036 , p_multi_booked_flag => p_multi_booked_flag
1037 , p_milestone_flag => p_milestone_flag
1038 , p_holiday_flag => p_holiday_flag
1039 , p_billable_flag => p_billable_flag
1040 , p_bound_mode_code => p_bound_mode_code
1041 , p_soft_bound_flag => p_soft_bound_flag
1042 , p_workflow_process_id => p_workflow_process_id
1043 , p_notification_flag => p_notification_flag
1044 , p_notification_period => p_notification_period
1045 , p_notification_period_uom => p_notification_period_uom
1046 , p_alarm_start => p_alarm_start
1047 , p_alarm_start_uom => p_alarm_start_uom
1048 , p_alarm_on => p_alarm_on
1049 , p_alarm_count => p_alarm_count
1050 , p_alarm_interval => p_alarm_interval
1051 , p_alarm_interval_uom => p_alarm_interval_uom
1052 , p_palm_flag => p_palm_flag
1053 , p_wince_flag => p_wince_flag
1054 , p_laptop_flag => p_laptop_flag
1055 , p_device1_flag => p_device1_flag
1056 , p_device2_flag => p_device2_flag
1057 , p_device3_flag => p_device3_flag
1058 , p_costs => p_costs
1059 , p_currency_code => p_currency_code
1060 , p_escalation_level => p_escalation_level
1061 , p_attribute1 => p_attribute1
1062 , p_attribute2 => p_attribute2
1063 , p_attribute3 => p_attribute3
1064 , p_attribute4 => p_attribute4
1065 , p_attribute5 => p_attribute5
1066 , p_attribute6 => p_attribute6
1067 , p_attribute7 => p_attribute7
1068 , p_attribute8 => p_attribute8
1069 , p_attribute9 => p_attribute9
1070 , p_attribute10 => p_attribute10
1071 , p_attribute11 => p_attribute11
1072 , p_attribute12 => p_attribute12
1073 , p_attribute13 => p_attribute13
1074 , p_attribute14 => p_attribute14
1075 , p_attribute15 => p_attribute15
1076 , p_attribute_category => p_attribute_category
1077 , p_task_assign_tbl => p_task_assign_tbl
1078 , p_task_depends_tbl => p_task_depends_tbl
1079 , p_task_rsrc_req_tbl => p_task_rsrc_req_tbl
1080 , p_task_refer_tbl => p_task_refer_tbl
1081 , p_task_dates_tbl => p_task_dates_tbl
1082 , p_task_notes_tbl => p_task_notes_tbl
1083 , p_task_recur_rec => p_task_recur_rec
1084 , p_task_contacts_tbl => p_task_contacts_tbl
1085 , p_date_selected => p_date_selected
1086 , p_category_id => p_category_id
1087 , p_show_on_calendar => p_show_on_calendar
1088 , p_owner_status_id => p_owner_status_id
1089 , p_template_id => p_template_id
1090 , p_template_group_id => p_template_group_id
1091 , p_enable_workflow => p_enable_workflow
1092 , p_abort_workflow => p_abort_workflow
1093 , p_task_split_flag => p_task_split_flag
1094 , p_parent_task_number => p_parent_task_number
1095 , p_parent_task_id => p_parent_task_id
1096 , p_child_position => p_child_position
1097 , p_child_sequence_num => p_child_sequence_num
1098 , x_task_id => x_task_id
1099 );
1100
1101 IF x_return_status = fnd_api.g_ret_sts_error THEN
1102 RAISE fnd_api.g_exc_error;
1103 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1104 RAISE fnd_api.g_exc_unexpected_error;
1105 END IF;
1106 EXCEPTION
1107 WHEN fnd_api.g_exc_error THEN
1108 ROLLBACK TO csf_create_task_pub;
1109 x_return_status := fnd_api.g_ret_sts_error;
1110 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1111 WHEN fnd_api.g_exc_unexpected_error THEN
1112 ROLLBACK TO csf_create_task_pub;
1113 x_return_status := fnd_api.g_ret_sts_unexp_error;
1114 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1115 WHEN OTHERS THEN
1116 x_return_status := fnd_api.g_ret_sts_unexp_error;
1117 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1118 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1119 END IF;
1120 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1121 ROLLBACK TO csf_create_task_pub;
1122 END create_task;
1123
1124 PROCEDURE delete_task (
1125 p_api_version IN NUMBER
1126 , p_init_msg_list IN VARCHAR2
1127 , p_commit IN VARCHAR2
1128 , x_return_status OUT NOCOPY VARCHAR2
1129 , x_msg_count OUT NOCOPY NUMBER
1130 , x_msg_data OUT NOCOPY VARCHAR2
1131 , p_task_id IN NUMBER
1132 , p_task_number IN VARCHAR2
1133 , p_object_version_number IN NUMBER
1134 , p_delete_future_recurrences IN VARCHAR2
1135 ) IS
1136 BEGIN
1137 jtf_tasks_pub.delete_task (
1138 p_api_version => p_api_version
1139 , p_init_msg_list => p_init_msg_list
1140 , p_commit => p_commit
1141 , x_return_status => x_return_status
1142 , x_msg_count => x_msg_count
1143 , x_msg_data => x_msg_data
1144 , p_task_id => p_task_id
1145 , p_task_number => p_task_number
1146 , p_object_version_number => p_object_version_number
1147 , p_delete_future_recurrences => p_delete_future_recurrences
1148 );
1149 END delete_task;
1150
1151 /**
1152 *
1153 */
1154 PROCEDURE propagate_status_change(
1155 x_return_status OUT NOCOPY VARCHAR2
1156 , x_msg_count OUT NOCOPY NUMBER
1157 , x_msg_data OUT NOCOPY VARCHAR2
1158 , p_task_id IN NUMBER
1159 , p_object_version_number IN OUT NOCOPY NUMBER
1160 , p_new_task_status_id IN NUMBER
1161 , p_new_sts_cancelled_flag IN VARCHAR2
1162 , p_new_sts_closed_flag IN VARCHAR2
1163 ) IS
1164 -- Cursor to get the Task Assignments to be cancelled
1165 CURSOR c_cancel_task_assignments IS
1166 SELECT ta.task_assignment_id
1167 , ta.object_version_number
1168 FROM csf_ct_task_assignments ta
1169 , jtf_task_statuses_b ts
1170 WHERE ta.task_id = p_task_id
1171 AND ts.task_status_id = ta.assignment_status_id
1172 AND ( NVL (ts.working_flag, 'N') = 'Y'
1173 OR NVL (ts.accepted_flag, 'N') = 'Y'
1174 OR NVL (ts.on_hold_flag, 'N') = 'Y'
1175 OR NVL (ts.schedulable_flag, 'N') = 'Y'
1176 OR ( NVL(ts.assigned_flag, 'N') = 'Y'
1177 AND NVL(ts.closed_flag, 'N') <> 'Y'
1178 AND NVL(ts.approved_flag, 'N') <> 'Y'
1179 AND NVL(ts.completed_flag, 'N') <> 'Y'
1180 AND NVL(ts.rejected_flag, 'N') <> 'Y')
1181 );
1182
1183 -- Cursor to get the Closed Task Assignments
1184 CURSOR c_closed_task_assignments IS
1185 SELECT ta.task_assignment_id
1186 , ta.object_version_number
1187 , NVL (ts.closed_flag, 'N') closed_flag
1188 , NVL (ts.cancelled_flag, 'N') cancelled_flag
1189 , NVL (ts.completed_flag, 'N') completed_flag
1190 , NVL (ts.rejected_flag, 'N') rejected_flag
1191 FROM jtf_task_assignments ta, jtf_task_statuses_b ts
1192 WHERE ta.task_id = p_task_id
1193 AND ts.task_status_id = ta.assignment_status_id;
1194
1195 l_task_status_id NUMBER;
1196 l_task_ovn NUMBER;
1197 BEGIN
1198 x_return_status := fnd_api.g_ret_sts_success;
1199
1200 IF p_new_sts_cancelled_flag = 'Y' THEN
1201 -- Cancel all the Open Task Assignments
1202 FOR v_task_assignment IN c_cancel_task_assignments LOOP
1203 csf_task_assignments_pub.update_assignment_status(
1204 p_api_version => 1.0
1205 , x_return_status => x_return_status
1206 , x_msg_count => x_msg_count
1207 , x_msg_data => x_msg_data
1208 , p_task_assignment_id => v_task_assignment.task_assignment_id
1209 , p_object_version_number => v_task_assignment.object_version_number
1210 , p_assignment_status_id => p_new_task_status_id
1211 , p_update_task => fnd_api.g_false
1212 , x_task_object_version_number => l_task_ovn
1213 , x_task_status_id => l_task_status_id
1214 );
1215
1216 IF x_return_status = fnd_api.g_ret_sts_error THEN
1217 RAISE fnd_api.g_exc_error;
1218 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1219 RAISE fnd_api.g_exc_unexpected_error;
1220 END IF;
1221 END LOOP;
1222 ELSIF p_new_sts_closed_flag = 'Y' THEN
1223 FOR v_task_assignment IN c_closed_task_assignments LOOP
1224
1225 /*
1226 * I didnt understand the significance of using CSFW: Update Schedulable Task
1227 * to check whether Debrief should be checked or not. The significance
1228 * of the profile is to govern whether Debrief can be invoked directly
1229 * without Scheduling the Task and not the other way round.
1230 * Therefore removed the logic - venjayar.
1231 */
1232
1233 -- Check whether the Task Assignment is still open.
1234 IF ( v_task_assignment.closed_flag = 'N'
1235 AND v_task_assignment.completed_flag = 'N'
1236 AND v_task_assignment.cancelled_flag = 'N'
1237 AND v_task_assignment.rejected_flag = 'N' )
1238 THEN
1239 fnd_message.set_name ('CSF', 'CSF_CLOSED_TASK');
1240 fnd_msg_pub.ADD;
1241 RAISE fnd_api.g_exc_error;
1242 END IF;
1243
1244 -- Task Assignment is not open. Check for Debrief
1245 IF NOT is_debrief_closed(v_task_assignment.task_assignment_id) THEN
1246 fnd_message.set_name('CSF', 'CSF_DEBRIEF_PENDING');
1247 fnd_msg_pub.ADD;
1248 RAISE fnd_api.g_exc_error;
1249 END IF;
1250
1251 -- All validations done. Close the Task Assignment
1252 jtf_task_assignments_pub.update_task_assignment(
1253 p_api_version => 1.0
1254 , x_return_status => x_return_status
1255 , x_msg_count => x_msg_count
1256 , x_msg_data => x_msg_data
1257 , p_task_assignment_id => v_task_assignment.task_assignment_id
1258 , p_object_version_number => v_task_assignment.object_version_number
1259 , p_assignment_status_id => p_new_task_status_id
1260 );
1261 IF x_return_status = fnd_api.g_ret_sts_error THEN
1262 RAISE fnd_api.g_exc_error;
1263 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1264 RAISE fnd_api.g_exc_unexpected_error;
1265 END IF;
1266 END LOOP;
1267 END IF;
1268 END propagate_status_change;
1269
1270 /**
1271 * Update the status of a Task and propagate to Task Assignments also.
1272 *
1273 * If the new Status of the Task is CANCELLED, then all Assignments which are open
1274 * (Working, Accepted, Assigned, In-Planning, Planned, On-Hold) needs to be
1275 * cancelled too. Other Assignments of the Task will not be updated.
1276 *
1277 * If the new Status of the Task is CLOSED, then we have to validate if the Task
1278 * can be closed. For this, there should not be any Open Task Assignments linked
1279 * to the Task. Moreover, if the Profile "CSFW: Update Schedulable Task" is set to
1280 * Yes, then the debrief linked with the Assignments should have been COMPLETED.
1281 * Otherwise Task cant be closed. If all verifications passes, then Task and the
1282 * open Assignments are closed.
1283 */
1284 PROCEDURE update_task_status (
1285 p_api_version IN NUMBER
1286 , p_init_msg_list IN VARCHAR2
1287 , p_commit IN VARCHAR2
1288 , p_validation_level IN NUMBER
1289 , x_return_status OUT NOCOPY VARCHAR2
1290 , x_msg_count OUT NOCOPY NUMBER
1291 , x_msg_data OUT NOCOPY VARCHAR2
1292 , p_task_id IN NUMBER
1293 , p_task_status_id IN NUMBER
1294 , p_object_version_number IN OUT NOCOPY NUMBER
1295 ) IS
1296 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_TASK_STATUS';
1297 l_api_version CONSTANT NUMBER := 1.0;
1298
1299 -- Fetch the information related to the given Task
1300 CURSOR c_task_info IS
1301 SELECT t.task_status_id
1302 , t.scheduled_start_date
1303 , t.scheduled_end_date
1304 , t.object_version_number
1305 , t.source_object_type_code
1306 FROM jtf_tasks_b t
1307 , jtf_task_statuses_b ts
1308 WHERE task_id = p_task_id
1309 AND ts.task_status_id = t.task_status_id;
1310
1311 -- Fetch the Flags corresponding to the new Task Status.
1312 CURSOR c_task_status_info IS
1313 SELECT NVL (ts.closed_flag, 'N') closed_flag
1314 , NVL (ts.cancelled_flag, 'N') cancelled_flag
1315 FROM jtf_task_statuses_b ts
1316 WHERE ts.task_status_id = p_task_status_id;
1317
1318
1319 l_task_info c_task_info%ROWTYPE;
1320 l_task_status_info c_task_status_info%ROWTYPE;
1321 BEGIN
1322 -- Standard start of API savepoint
1323 SAVEPOINT update_task_status_pub;
1324
1325 -- Standard call to check for call compatibility
1326 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1327 RAISE fnd_api.g_exc_unexpected_error;
1328 END IF;
1329
1330 -- Initialize message list if p_init_msg_list is set to TRUE
1331 IF fnd_api.to_boolean (p_init_msg_list) THEN
1332 fnd_msg_pub.initialize;
1333 END IF;
1334
1335 -- Initialize API return status to success
1336 x_return_status := fnd_api.g_ret_sts_success;
1337
1338 -- Validate if update in necessary and get old status_id just in case
1339 IF p_task_status_id = fnd_api.g_miss_num THEN
1340 RETURN;
1341 END IF;
1342
1343 OPEN c_task_info;
1344 FETCH c_task_info INTO l_task_info;
1345 CLOSE c_task_info;
1346
1347 -- No change in Task Status
1348 IF p_task_status_id = l_task_info.task_status_id THEN
1349 RETURN;
1350 END IF;
1351
1352 IF p_validation_level IS NULL OR p_validation_level = fnd_api.g_valid_level_full THEN
1353 validate_status_change(l_task_info.task_status_id, p_task_status_id);
1354 END IF;
1355
1356 OPEN c_task_status_info;
1357 FETCH c_task_status_info INTO l_task_status_info;
1358 CLOSE c_task_status_info;
1359
1360 IF l_task_status_info.cancelled_flag = 'Y' AND l_task_info.source_object_type_code = 'SR' THEN
1361 l_task_info.scheduled_start_date := NULL;
1362 l_task_info.scheduled_end_date := NULL;
1363 END IF;
1364
1365 -- Update the Task with the new Task Status Information
1366 jtf_tasks_pub.update_task (
1367 p_api_version => 1.0
1368 , x_return_status => x_return_status
1369 , x_msg_count => x_msg_count
1370 , x_msg_data => x_msg_data
1371 , p_task_id => p_task_id
1375 , p_scheduled_end_date => l_task_info.scheduled_end_date
1372 , p_object_version_number => p_object_version_number
1373 , p_task_status_id => p_task_status_id
1374 , p_scheduled_start_date => l_task_info.scheduled_start_date
1376 );
1377
1378 IF x_return_status = fnd_api.g_ret_sts_error THEN
1379 RAISE fnd_api.g_exc_error;
1380 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1381 RAISE fnd_api.g_exc_unexpected_error;
1382 END IF;
1383
1384 -- Propagate the change to other dependent objects
1385 propagate_status_change(
1386 x_return_status => x_return_status
1387 , x_msg_count => x_msg_count
1388 , x_msg_data => x_msg_data
1389 , p_task_id => p_task_id
1390 , p_object_version_number => p_object_version_number
1391 , p_new_task_status_id => p_task_status_id
1392 , p_new_sts_cancelled_flag => l_task_status_info.cancelled_flag
1393 , p_new_sts_closed_flag => l_task_status_info.closed_flag
1394 );
1395
1396 IF x_return_status = fnd_api.g_ret_sts_error THEN
1397 RAISE fnd_api.g_exc_error;
1398 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1399 RAISE fnd_api.g_exc_unexpected_error;
1400 END IF;
1401
1402 -- Standard check of p_commit
1403 IF fnd_api.to_boolean (p_commit) THEN
1404 COMMIT WORK;
1405 END IF;
1406
1407 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1408 EXCEPTION
1409 WHEN fnd_api.g_exc_error THEN
1410 ROLLBACK TO update_task_status_pub;
1411 x_return_status := fnd_api.g_ret_sts_error;
1412 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1413 WHEN fnd_api.g_exc_unexpected_error THEN
1414 ROLLBACK TO update_task_status_pub;
1415 x_return_status := fnd_api.g_ret_sts_unexp_error;
1416 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1417 WHEN OTHERS THEN
1418 x_return_status := fnd_api.g_ret_sts_unexp_error;
1419 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1420 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1421 END IF;
1422 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1423 ROLLBACK TO update_task_status_pub;
1424 END update_task_status;
1425
1426 PROCEDURE update_task(
1427 p_api_version IN NUMBER
1428 , p_init_msg_list IN VARCHAR2
1429 , p_commit IN VARCHAR2
1430 , p_validation_level IN NUMBER DEFAULT NULL
1431 , x_return_status OUT NOCOPY VARCHAR2
1432 , x_msg_count OUT NOCOPY NUMBER
1433 , x_msg_data OUT NOCOPY VARCHAR2
1434 , p_task_id IN NUMBER
1435 , p_object_version_number IN OUT NOCOPY NUMBER
1436 , p_task_number IN VARCHAR2
1437 , p_task_name IN VARCHAR2
1438 , p_description IN VARCHAR2
1439 , p_planned_start_date IN DATE
1440 , p_planned_end_date IN DATE
1441 , p_scheduled_start_date IN DATE
1442 , p_scheduled_end_date IN DATE
1443 , p_actual_start_date IN DATE
1444 , p_actual_end_date IN DATE
1445 , p_timezone_id IN NUMBER
1446 , p_source_object_type_code IN VARCHAR2
1447 , p_source_object_id IN NUMBER
1448 , p_source_object_name IN VARCHAR2
1449 , p_task_status_id IN NUMBER
1450 , p_task_type_id IN NUMBER
1451 , p_task_priority_id IN NUMBER
1452 , p_owner_type_code IN VARCHAR2
1453 , p_owner_id IN NUMBER
1454 , p_owner_territory_id IN NUMBER
1455 , p_owner_status_id IN NUMBER
1456 , p_assigned_by_id IN NUMBER
1457 , p_customer_id IN NUMBER
1458 , p_cust_account_id IN NUMBER
1459 , p_address_id IN NUMBER
1460 , p_location_id IN NUMBER
1461 , p_duration IN NUMBER
1462 , p_duration_uom IN VARCHAR2
1463 , p_planned_effort IN NUMBER
1464 , p_planned_effort_uom IN VARCHAR2
1465 , p_actual_effort IN NUMBER
1466 , p_actual_effort_uom IN VARCHAR2
1467 , p_percentage_complete IN NUMBER
1468 , p_reason_code IN VARCHAR2
1469 , p_private_flag IN VARCHAR2
1470 , p_publish_flag IN VARCHAR2
1471 , p_restrict_closure_flag IN VARCHAR2
1472 , p_attribute1 IN VARCHAR2
1473 , p_attribute2 IN VARCHAR2
1474 , p_attribute3 IN VARCHAR2
1475 , p_attribute4 IN VARCHAR2
1476 , p_attribute5 IN VARCHAR2
1477 , p_attribute6 IN VARCHAR2
1478 , p_attribute7 IN VARCHAR2
1479 , p_attribute8 IN VARCHAR2
1480 , p_attribute9 IN VARCHAR2
1481 , p_attribute10 IN VARCHAR2
1482 , p_attribute11 IN VARCHAR2
1483 , p_attribute12 IN VARCHAR2
1484 , p_attribute13 IN VARCHAR2
1485 , p_attribute14 IN VARCHAR2
1486 , p_attribute15 IN VARCHAR2
1487 , p_attribute_category IN VARCHAR2
1488 , p_date_selected IN VARCHAR2
1489 , p_category_id IN NUMBER
1490 , p_multi_booked_flag IN VARCHAR2
1491 , p_milestone_flag IN VARCHAR2
1492 , p_holiday_flag IN VARCHAR2
1493 , p_billable_flag IN VARCHAR2
1494 , p_bound_mode_code IN VARCHAR2
1495 , p_soft_bound_flag IN VARCHAR2
1496 , p_workflow_process_id IN NUMBER
1497 , p_notification_flag IN VARCHAR2
1498 , p_notification_period IN NUMBER
1499 , p_notification_period_uom IN VARCHAR2
1500 , p_alarm_start IN NUMBER
1501 , p_alarm_start_uom IN VARCHAR2
1502 , p_alarm_on IN VARCHAR2
1503 , p_alarm_count IN NUMBER
1504 , p_alarm_fired_count IN NUMBER
1505 , p_alarm_interval IN NUMBER
1506 , p_alarm_interval_uom IN VARCHAR2
1507 , p_palm_flag IN VARCHAR2
1508 , p_wince_flag IN VARCHAR2
1509 , p_laptop_flag IN VARCHAR2
1510 , p_device1_flag IN VARCHAR2
1511 , p_device2_flag IN VARCHAR2
1512 , p_device3_flag IN VARCHAR2
1513 , p_show_on_calendar IN VARCHAR2
1514 , p_costs IN NUMBER
1515 , p_currency_code IN VARCHAR2
1516 , p_escalation_level IN VARCHAR2
1517 , p_parent_task_id IN NUMBER
1518 , p_parent_task_number IN VARCHAR2
1519 , p_task_split_flag IN VARCHAR2
1520 , p_child_position IN VARCHAR2
1521 , p_child_sequence_num IN NUMBER
1522 , p_enable_workflow IN VARCHAR2
1523 , p_abort_workflow IN VARCHAR2
1524 , p_find_overlap IN VARCHAR2 DEFAULT NULL
1525 ) IS
1526 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_TASK';
1527 l_api_version CONSTANT NUMBER := 1.0;
1528
1529 l_new_start_date DATE;
1530 l_new_end_date DATE;
1531 l_planned_effort NUMBER;
1532 l_planned_effort_uom VARCHAR2(3);
1533 l_planned_effort_minutes NUMBER;
1534
1535 CURSOR c_overlap_tasks(p_trip_id NUMBER, p_start_date DATE, p_end_date DATE) IS
1536 SELECT NVL(TASK_NUMBER,TASK_ID) overlap_task_num
1537 FROM csr_trip_tasks_v
1538 WHERE object_capacity_id = p_trip_id
1539 AND task_id <> p_task_id
1540 AND NVL(actual_end_date,scheduled_end_date) >= p_start_date
1541 AND NVL(actual_start_date,scheduled_start_date) <= p_end_date;
1542
1543 CURSOR c_task_info IS
1544 SELECT t.scheduled_start_date
1545 , t.scheduled_end_date
1546 , CASE WHEN ta.actual_start_date IS NULL AND ta.actual_end_date IS NULL THEN 'N' ELSE 'Y' END is_visited
1547 , ta.resource_id
1548 , ta.resource_type_code
1549 , ta.object_capacity_id
1550 , nvl(ta.actual_effort,t.planned_effort) planned_effort
1551 , nvl(ta.actual_effort_uom,t.planned_effort_uom) planned_effort_uom
1552 , ta.task_assignment_id
1553 , ta.object_version_number
1554 , t.task_status_id
1555 , t.planned_start_date
1556 , t.planned_end_date
1557 , ta.assignment_status_id
1558 , t.task_split_flag
1559 , t.task_number
1560 FROM jtf_tasks_b t,
1561 (SELECT tas.actual_start_date
1562 , tas.actual_end_date
1563 , tas.resource_id
1564 , tas.resource_type_code
1565 , tas.object_capacity_id
1566 , tas.task_assignment_id
1567 , tas.object_version_number
1568 , tas.assignment_status_id
1569 , tas.task_id
1570 , tas.actual_effort
1571 , tas.actual_effort_uom
1572 FROM jtf_task_assignments tas, jtf_task_statuses_b ts
1573 WHERE task_id = p_task_id
1574 AND ts.task_status_id = tas.assignment_status_id
1575 AND NVL(ts.cancelled_flag, 'N') <> 'Y'
1576 AND NVL(ts.closed_flag, 'N') <> 'Y'
1577 AND NVL(ts.completed_flag, 'N') <> 'Y'
1578 AND NVL(ts.rejected_flag, 'N') <> 'Y'
1579 ) ta
1580 WHERE t.task_id = p_task_id
1581 AND t.task_id = ta.task_id(+)
1582 AND NVL(t.deleted_flag, 'N') <> 'Y';
1583
1584 -- Fetch the Flags corresponding to the new Task Status.
1585 CURSOR c_task_status_info IS
1586 SELECT NVL (ts.closed_flag, 'N') closed_flag
1587 , NVL (ts.cancelled_flag, 'N') cancelled_flag
1591
1588 FROM jtf_task_statuses_b ts
1589 WHERE ts.task_status_id = p_task_status_id;
1590
1592 l_task_info c_task_info%ROWTYPE;
1593 l_task_status_info c_task_status_info%ROWTYPE;
1594 l_overlap_tasks VARCHAR2(2000);
1595 l_trip_id NUMBER;
1596 l_task_object_version NUMBER;
1597 l_task_status_id NUMBER;
1598 l_task_number NUMBER;
1599
1600 BEGIN
1601 SAVEPOINT csf_update_task;
1602
1603 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1604 RAISE fnd_api.g_exc_unexpected_error;
1605 END IF;
1606
1607 IF fnd_api.to_boolean (p_init_msg_list) THEN
1608 fnd_msg_pub.initialize;
1609 END IF;
1610
1611 x_return_status := fnd_api.g_ret_sts_success;
1612
1613 OPEN c_task_info;
1614 FETCH c_task_info INTO l_task_info;
1615 CLOSE c_task_info;
1616
1617 l_new_start_date := p_scheduled_start_date;
1618 l_new_end_date := p_scheduled_end_date;
1619
1620 IF p_validation_level = fnd_api.g_valid_level_full AND p_validation_level IS NULL THEN
1621
1622 -- Validate Task Status Change
1623 IF p_task_status_id <> fnd_api.g_miss_num THEN
1624 validate_status_change(l_task_info.task_status_id, p_task_status_id);
1625 END IF;
1626
1627 -- Validate Trip Information corresponding to new Scheduled Dates
1628 IF NVL(l_new_start_date,fnd_api.g_miss_date) <> fnd_api.g_miss_date
1629 OR NVL(l_new_end_date,fnd_api.g_miss_date) <> fnd_api.g_miss_date
1630 THEN
1631
1632 l_planned_effort := p_planned_effort;
1633 l_planned_effort_uom := p_planned_effort_uom;
1634
1635 IF l_planned_effort IS NULL OR l_planned_effort = fnd_api.g_miss_num THEN
1636 l_planned_effort := l_task_info.planned_effort;
1637 END IF;
1638 IF l_planned_effort_uom IS NULL OR l_planned_effort_uom = fnd_api.g_miss_char THEN
1639 l_planned_effort_uom := l_task_info.planned_effort_uom;
1640 END IF;
1641
1642 l_planned_effort_minutes := csf_util_pvt.convert_to_minutes(
1643 l_planned_effort
1644 , l_planned_effort_uom
1645 );
1646
1647 l_task_number := l_task_info.task_number;
1648
1649 IF l_task_info.task_split_flag IS NOT NULL
1650 OR l_planned_effort_minutes > fnd_profile.value('CSR_DEFAULT_SHIFT_DURATION')
1651 THEN
1652 fnd_message.set_name ('CSF', 'CSF_TASK_UPDATE_NOT_ALLOWED');
1653 fnd_message.set_token('TASK_NUMBER',l_task_number);
1654 fnd_msg_pub.add;
1655 RAISE fnd_api.g_exc_error;
1656 END IF;
1657
1658 IF l_task_info.task_assignment_id IS NOT NULL
1659 AND l_task_info.is_visited = 'N'
1660 AND ( l_task_info.scheduled_start_date <> nvl(l_new_start_date,fnd_api.g_miss_date)
1661 OR l_task_info.scheduled_end_date <> nvl(l_new_end_date,fnd_api.g_miss_date) )
1662 THEN
1663 IF l_new_start_date IS NULL OR l_new_start_date = fnd_api.g_miss_date THEN
1664 l_new_start_date := l_new_end_date - l_planned_effort_minutes / (24 * 60);
1665 END IF;
1666 IF l_new_end_date IS NULL OR l_new_end_date = fnd_api.g_miss_date THEN
1667 l_new_end_date := l_new_start_date + l_planned_effort_minutes / (24 * 60);
1668 END IF;
1669
1670 csf_trips_pub.find_trip(
1671 p_api_version => 1
1672 , p_init_msg_list => fnd_api.g_false
1673 , x_return_status => x_return_status
1674 , x_msg_data => x_msg_data
1675 , x_msg_count => x_msg_count
1676 , p_resource_id => l_task_info.resource_id
1677 , p_resource_type => l_task_info.resource_type_code
1678 , p_start_date_time => l_new_start_date
1679 , p_end_date_time => l_new_end_date
1680 , p_overtime_flag => fnd_api.g_true
1681 , x_trip_id => l_trip_id
1682 );
1683
1684 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1685 RAISE fnd_api.g_exc_unexpected_error;
1686 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1687 -- No Trip or Multiple Trips found for the given dates. Make Trip as NULL
1688 l_trip_id := NULL;
1689 END IF;
1690
1691 IF NVL(l_trip_id, -1) <> NVL(l_task_info.object_capacity_id,-1) THEN
1692 csf_task_assignments_pub.update_task_assignment(
1693 p_api_version => p_api_version
1694 , p_init_msg_list => p_init_msg_list
1695 , p_commit => fnd_api.g_false
1696 , p_validation_level => fnd_api.g_valid_level_none
1697 , x_return_status => x_return_status
1698 , x_msg_count => x_msg_count
1699 , x_msg_data => x_msg_data
1700 , p_task_assignment_id => l_task_info.task_assignment_id
1701 , p_object_version_number => l_task_info.object_version_number
1702 , p_object_capacity_id => l_trip_id
1703 , p_update_task => fnd_api.g_false
1704 , x_task_object_version_number => l_task_object_version
1708 IF x_return_status = fnd_api.g_ret_sts_error THEN
1705 , x_task_status_id => l_task_status_id
1706 );
1707
1709 RAISE fnd_api.g_exc_error;
1710 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1711 RAISE fnd_api.g_exc_unexpected_error;
1712 END IF;
1713 END IF;
1714
1715 IF fnd_api.to_boolean(p_find_overlap) THEN
1716 FOR v_overlap_tasks IN c_overlap_tasks(l_trip_id,l_new_start_date,l_new_end_date) LOOP
1717 l_overlap_tasks := l_overlap_tasks || fnd_global.local_chr(10) || v_overlap_tasks.overlap_task_num;
1718 END LOOP;
1719 END IF;
1720 END IF;
1721 END IF;
1722 END IF;
1723
1724 IF p_task_status_id <> fnd_api.g_miss_num AND l_task_info.task_status_id <> p_task_status_id THEN
1725 -- Clear the Scheduled Dates if the Task is Cancelled
1726 OPEN c_task_status_info;
1727 FETCH c_task_status_info INTO l_task_status_info;
1728 CLOSE c_task_status_info;
1729
1730 IF l_task_status_info.cancelled_flag = 'Y' THEN
1731 l_new_start_date := NULL;
1732 l_new_end_date := NULL;
1733 END IF;
1734 END IF;
1735
1736 jtf_tasks_pub.update_task(
1737 p_api_version => p_api_version
1738 , p_init_msg_list => p_init_msg_list
1739 , p_commit => fnd_api.g_false
1740 , x_return_status => x_return_status
1741 , x_msg_count => x_msg_count
1742 , x_msg_data => x_msg_data
1743 , p_task_id => p_task_id
1744 , p_object_version_number => p_object_version_number
1745 , p_task_number => p_task_number
1746 , p_task_name => p_task_name
1747 , p_description => p_description
1748 , p_task_status_id => p_task_status_id
1749 , p_planned_start_date => p_planned_start_date
1750 , p_planned_end_date => p_planned_end_date
1751 , p_scheduled_start_date => l_new_start_date
1752 , p_scheduled_end_date => l_new_end_date
1753 , p_actual_start_date => p_actual_start_date
1754 , p_actual_end_date => p_actual_end_date
1755 , p_timezone_id => p_timezone_id
1756 , p_source_object_type_code => p_source_object_type_code
1757 , p_source_object_id => p_source_object_id
1758 , p_source_object_name => p_source_object_name
1759 , p_task_type_id => p_task_type_id
1760 , p_task_priority_id => p_task_priority_id
1761 , p_owner_type_code => p_owner_type_code
1762 , p_owner_id => p_owner_id
1763 , p_owner_territory_id => p_owner_territory_id
1764 , p_owner_status_id => p_owner_status_id
1765 , p_assigned_by_id => p_assigned_by_id
1766 , p_customer_id => p_customer_id
1767 , p_cust_account_id => p_cust_account_id
1768 , p_address_id => p_address_id
1769 , p_location_id => p_location_id
1770 , p_duration => p_duration
1771 , p_duration_uom => p_duration_uom
1772 , p_planned_effort => p_planned_effort
1773 , p_planned_effort_uom => p_planned_effort_uom
1774 , p_actual_effort => p_actual_effort
1775 , p_actual_effort_uom => p_actual_effort_uom
1776 , p_percentage_complete => p_percentage_complete
1777 , p_reason_code => p_reason_code
1778 , p_private_flag => p_private_flag
1779 , p_publish_flag => p_publish_flag
1780 , p_restrict_closure_flag => p_restrict_closure_flag
1781 , p_attribute1 => p_attribute1
1782 , p_attribute2 => p_attribute2
1783 , p_attribute3 => p_attribute3
1784 , p_attribute4 => p_attribute4
1785 , p_attribute5 => p_attribute5
1786 , p_attribute6 => p_attribute6
1787 , p_attribute7 => p_attribute7
1788 , p_attribute8 => p_attribute8
1789 , p_attribute9 => p_attribute9
1790 , p_attribute10 => p_attribute10
1791 , p_attribute11 => p_attribute11
1792 , p_attribute12 => p_attribute12
1793 , p_attribute13 => p_attribute13
1794 , p_attribute14 => p_attribute14
1795 , p_attribute15 => p_attribute15
1796 , p_attribute_category => p_attribute_category
1797 , p_date_selected => p_date_selected
1798 , p_category_id => p_category_id
1799 , p_multi_booked_flag => p_multi_booked_flag
1800 , p_milestone_flag => p_milestone_flag
1801 , p_holiday_flag => p_holiday_flag
1802 , p_billable_flag => p_billable_flag
1803 , p_bound_mode_code => p_bound_mode_code
1804 , p_soft_bound_flag => p_soft_bound_flag
1805 , p_workflow_process_id => p_workflow_process_id
1806 , p_notification_flag => p_notification_flag
1807 , p_notification_period => p_notification_period
1808 , p_notification_period_uom => p_notification_period_uom
1809 , p_alarm_start => p_alarm_start
1810 , p_alarm_start_uom => p_alarm_start_uom
1811 , p_alarm_on => p_alarm_on
1812 , p_alarm_count => p_alarm_count
1813 , p_alarm_fired_count => p_alarm_fired_count
1814 , p_alarm_interval => p_alarm_interval
1815 , p_alarm_interval_uom => p_alarm_interval_uom
1816 , p_palm_flag => p_palm_flag
1817 , p_wince_flag => p_wince_flag
1818 , p_laptop_flag => p_laptop_flag
1819 , p_device1_flag => p_device1_flag
1820 , p_device2_flag => p_device2_flag
1821 , p_device3_flag => p_device3_flag
1822 , p_show_on_calendar => p_show_on_calendar
1823 , p_costs => p_costs
1824 , p_currency_code => p_currency_code
1825 , p_escalation_level => p_escalation_level
1826 , p_parent_task_id => p_parent_task_id
1827 , p_parent_task_number => p_parent_task_number
1828 , p_task_split_flag => p_task_split_flag
1829 , p_child_position => p_child_position
1830 , p_child_sequence_num => p_child_sequence_num
1831 , p_enable_workflow => p_enable_workflow
1832 , p_abort_workflow => p_abort_workflow
1833 );
1834
1835 IF x_return_status = fnd_api.g_ret_sts_error THEN
1836 RAISE fnd_api.g_exc_error;
1837 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1838 RAISE fnd_api.g_exc_unexpected_error;
1839 END IF;
1840
1841 -- Propagate the Task Status Change to other dependent Objects.
1842 IF p_task_status_id <> fnd_api.g_miss_num THEN
1843 propagate_status_change(
1844 x_return_status => x_return_status
1845 , x_msg_count => x_msg_count
1846 , x_msg_data => x_msg_data
1847 , p_task_id => p_task_id
1848 , p_object_version_number => p_object_version_number
1849 , p_new_task_status_id => p_task_status_id
1850 , p_new_sts_cancelled_flag => l_task_status_info.cancelled_flag
1851 , p_new_sts_closed_flag => l_task_status_info.closed_flag
1852 );
1853 IF x_return_status = fnd_api.g_ret_sts_error THEN
1854 RAISE fnd_api.g_exc_error;
1855 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1856 RAISE fnd_api.g_exc_unexpected_error;
1857 END IF;
1858 END IF;
1859
1860 IF fnd_api.to_boolean (p_commit) THEN
1861 COMMIT WORK;
1862 END IF;
1863
1864 IF l_overlap_tasks IS NOT NULL THEN
1865 fnd_message.set_name('CSR','CSR_TASK_OVERLAP');
1866 fnd_message.set_token('TASKID', l_task_number);
1867 fnd_message.set_token('TASKS',l_overlap_tasks);
1868 fnd_msg_pub.add;
1869 END IF;
1870
1871 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1872 EXCEPTION
1873 WHEN fnd_api.g_exc_error THEN
1874 ROLLBACK TO csf_update_task;
1875 x_return_status := fnd_api.g_ret_sts_error;
1876 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1877 WHEN fnd_api.g_exc_unexpected_error THEN
1878 ROLLBACK TO csf_update_task;
1879 x_return_status := fnd_api.g_ret_sts_unexp_error;
1880 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1881 WHEN OTHERS THEN
1882 x_return_status := fnd_api.g_ret_sts_unexp_error;
1883 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1884 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1885 END IF;
1886 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1887 ROLLBACK TO csf_update_task;
1888 END update_task;
1889
1890 PROCEDURE commit_task (
1891 p_api_version IN NUMBER
1892 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
1893 , p_commit IN VARCHAR2 DEFAULT NULL
1894 , x_return_status OUT NOCOPY VARCHAR2
1895 , x_msg_data OUT NOCOPY VARCHAR2
1896 , x_msg_count OUT NOCOPY NUMBER
1897 , p_task_id IN NUMBER
1898 , p_resource_id IN NUMBER DEFAULT NULL --bug 6647019
1899 , p_resource_type IN VARCHAR2 DEFAULT NULL
1900 ) IS
1901 l_api_name CONSTANT VARCHAR2(30) := 'COMMIT_TASK';
1902 l_api_version CONSTANT NUMBER := 1.0;
1903 l_new_status_id CONSTANT NUMBER := g_assigned;
1904
1905 -- Cursor to get the Task Details
1906 CURSOR c_task_details IS
1907 SELECT t.task_number
1908 , t.task_status_id
1909 , t.object_version_number
1910 , t.scheduled_start_date
1911 , t.scheduled_end_date
1912 , NVL (t.task_confirmation_status, 'N') task_confirmation_status
1913 , ta.task_assignment_id
1914 , ta.object_version_number ta_object_version_number
1915 , ta.object_capacity_id
1916 , ta.assignment_status_id
1917 , cac.status trip_status
1918 FROM jtf_tasks_b t
1919 , jtf_task_assignments ta
1920 , jtf_task_statuses_b ts
1921 , cac_sr_object_capacity cac
1922 WHERE t.task_id = p_task_id
1923 AND ta.task_id = t.task_id
1924 AND ts.task_status_id = ta.assignment_status_id
1925 AND NVL (ts.assigned_flag, 'N') <> 'Y'
1926 AND NVL (ts.working_flag, 'N') <> 'Y'
1927 AND NVL (ts.completed_flag, 'N') <> 'Y'
1928 AND NVL (ts.closed_flag, 'N') <> 'Y'
1929 AND NVL (ts.cancelled_flag, 'N') <> 'Y'
1930 AND cac.object_capacity_id (+) = ta.object_capacity_id;
1931
1932 -- Cursor added for bug 6647019 by modifying cursor c_task_details. Added
1936 SELECT t.task_number
1933 -- check for p_resource_id and p_resource_type
1934 -- Cursor to get the Task Details
1935 CURSOR c_task_details_1 IS
1937 , t.task_status_id
1938 , t.object_version_number
1939 , t.scheduled_start_date
1940 , t.scheduled_end_date
1941 , NVL (t.task_confirmation_status, 'N') task_confirmation_status
1942 , ta.task_assignment_id
1943 , ta.object_version_number ta_object_version_number
1944 , ta.object_capacity_id
1945 , ta.assignment_status_id
1946 , cac.status trip_status
1947 FROM jtf_tasks_b t
1948 , jtf_task_assignments ta
1949 , jtf_task_statuses_b ts
1950 , cac_sr_object_capacity cac
1951 WHERE t.task_id = p_task_id
1952 AND ta.task_id = t.task_id
1953 AND ts.task_status_id = ta.assignment_status_id
1954 AND NVL (ts.assigned_flag, 'N') <> 'Y'
1955 AND NVL (ts.working_flag, 'N') <> 'Y'
1956 AND NVL (ts.completed_flag, 'N') <> 'Y'
1957 AND NVL (ts.closed_flag, 'N') <> 'Y'
1958 AND NVL (ts.cancelled_flag, 'N') <> 'Y'
1959 AND cac.object_capacity_id (+) = ta.object_capacity_id
1960 AND ta.resource_id = p_resource_id
1961 AND ta.resource_type_code = p_resource_type;
1962
1963 l_task_details c_task_details%ROWTYPE;
1964 l_trans_valid VARCHAR2(1);
1965 l_valid_statuses VARCHAR2(2000);
1966 BEGIN
1967 SAVEPOINT csf_commit_task;
1968
1969 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1970 RAISE fnd_api.g_exc_unexpected_error;
1971 END IF;
1972
1973 IF fnd_api.to_boolean (p_init_msg_list) THEN
1974 fnd_msg_pub.initialize;
1975 END IF;
1976
1977 x_return_status := fnd_api.g_ret_sts_success;
1978
1979 -- Fetch the Task Information
1980
1981 IF p_resource_id is null or p_resource_type is null --condition added for bug 6647019
1982 THEN
1983 OPEN c_task_details;
1984 FETCH c_task_details INTO l_task_details;
1985 IF c_task_details%NOTFOUND THEN
1986 CLOSE c_task_details;
1987 fnd_message.set_name ('CSF', 'CSF_AUTO_COMMIT_TASK_STATUS');
1988 fnd_message.set_token ('P_TASK_NUMBER', task_number(p_task_id));
1989 fnd_msg_pub.ADD;
1990 RAISE fnd_api.g_exc_error;
1991 END IF;
1992 CLOSE c_task_details;
1993 ELSE
1994 OPEN c_task_details_1;
1995 FETCH c_task_details_1 INTO l_task_details;
1996 IF c_task_details_1%NOTFOUND THEN
1997 CLOSE c_task_details_1;
1998 fnd_message.set_name ('CSF', 'CSF_AUTO_COMMIT_TASK_STATUS');
1999 fnd_message.set_token ('P_TASK_NUMBER', task_number(p_task_id));
2000 fnd_msg_pub.ADD;
2001 RAISE fnd_api.g_exc_error;
2002 END IF;
2003 CLOSE c_task_details_1;
2004 END IF;
2005
2006 -- Trip should not be in Blocked Status
2007 IF l_task_details.trip_status = csf_trips_pub.g_trip_unavailable THEN
2008 fnd_message.set_name ('CSF', 'CSF_AUTO_COMMIT_TRIP_BLOCK');
2009 fnd_message.set_token ('P_TASK_NUMBER', l_task_details.task_number);
2010 fnd_msg_pub.ADD;
2011 RAISE fnd_api.g_exc_error;
2012 END IF;
2013
2014 -- Validate Status Transition
2015 l_trans_valid := validate_state_transition ('TASK_STATUS', l_task_details.assignment_status_id, l_new_status_id);
2016 IF l_trans_valid = fnd_api.g_false THEN
2017 l_valid_statuses := get_valid_statuses ('TASK_STATUS', l_task_details.assignment_status_id);
2018 IF l_valid_statuses IS NULL THEN
2019 fnd_message.set_name ('CSF', 'CSF_NO_STATE_TRANSITION');
2020 ELSE
2021 fnd_message.set_name ('CSF', 'CSF_INVALID_STATE_TRANSITION');
2022 fnd_message.set_token ('P_VALID_STATUSES', l_valid_statuses);
2023 END IF;
2024 fnd_message.set_token ('P_NEW_STATUS', get_task_status_name (l_new_status_id));
2025 fnd_msg_pub.ADD;
2026 RAISE fnd_api.g_exc_error;
2027 END IF;
2028
2029 -- Check the Customer Confirmation Status - Should be either No or Received
2030 IF l_task_details.task_confirmation_status = 'R' THEN
2031 fnd_message.set_name ('CSF', 'CSF_AUTO_COMMIT_CUST_CONF');
2032 fnd_message.set_token ('P_TASK_NUMBER', l_task_details.task_number);
2033 fnd_msg_pub.ADD;
2034 RAISE fnd_api.g_exc_error;
2035 END IF;
2036
2037 -- Check for Scheduled Dates
2038 IF l_task_details.scheduled_start_date IS NULL THEN
2039 fnd_message.set_name ('CSF', 'CSF_AUTO_COMMIT_TASK_SCHE');
2040 fnd_message.set_token ('P_TASK_NUMBER', l_task_details.task_number);
2041 fnd_msg_pub.ADD;
2042 RAISE fnd_api.g_exc_error;
2043 END IF;
2044
2045 csf_task_assignments_pub.update_assignment_status(
2046 p_api_version => 1.0
2047 , p_validation_level => fnd_api.g_valid_level_none
2048 , p_init_msg_list => fnd_api.g_false
2049 , p_commit => fnd_api.g_false
2050 , x_return_status => x_return_status
2051 , x_msg_count => x_msg_count
2052 , x_msg_data => x_msg_data
2053 , p_task_assignment_id => l_task_details.task_assignment_id
2054 , p_assignment_status_id => l_new_status_id
2055 , p_object_version_number => l_task_details.ta_object_version_number
2059
2056 , x_task_object_version_number => l_task_details.object_version_number
2057 , x_task_status_id => l_task_details.task_status_id
2058 );
2060 IF x_return_status = fnd_api.g_ret_sts_success THEN
2061 -- commented for the bug 6801965
2062 -- Committed Task Message is added to the message stack
2063 -- fnd_message.set_name ('CSF', 'CSF_AUTO_COMMITTED');
2064 -- fnd_message.set_token ('P_TASK_NUMBER', l_task_details.task_number);
2065 -- fnd_msg_pub.ADD;
2066 RETURN;
2067 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2068 RAISE fnd_api.g_exc_error;
2069 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2070 RAISE fnd_api.g_exc_unexpected_error;
2071 END IF;
2072
2073 -- Standard check of p_commit
2074 IF fnd_api.to_boolean (p_commit) THEN
2075 COMMIT WORK;
2076 END IF;
2077
2078 EXCEPTION
2079 WHEN fnd_api.g_exc_error THEN
2080 ROLLBACK TO csf_commit_task;
2081 x_return_status := fnd_api.g_ret_sts_error;
2082 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2083 WHEN fnd_api.g_exc_unexpected_error THEN
2084 ROLLBACK TO csf_commit_task;
2085 x_return_status := fnd_api.g_ret_sts_unexp_error;
2086 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2087 WHEN OTHERS THEN
2088 x_return_status := fnd_api.g_ret_sts_unexp_error;
2089 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2090 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2091 END IF;
2092 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2093 ROLLBACK TO csf_commit_task;
2094 END commit_task;
2095
2096 PROCEDURE commit_schedule (
2097 p_api_version IN NUMBER
2098 , p_init_msg_list IN VARCHAR2
2099 , p_commit IN VARCHAR2
2100 , x_return_status OUT NOCOPY VARCHAR2
2101 , x_msg_count OUT NOCOPY NUMBER
2102 , x_msg_data OUT NOCOPY VARCHAR2
2103 , p_resource_id IN NUMBER
2104 , p_resource_type IN VARCHAR2
2105 , p_scheduled_start_date IN DATE
2106 , p_scheduled_end_date IN DATE
2107 , p_query_id IN NUMBER
2108 , p_trip_id IN NUMBER
2109 , p_task_id IN NUMBER
2110 ) IS
2111 l_api_name CONSTANT VARCHAR2(30) := 'COMMIT_SCHEDULE';
2112 l_api_version CONSTANT NUMBER := 1.0;
2113
2114
2115 TYPE ref_cursor_type IS REF CURSOR;
2116 TYPE task_split_tbl_type IS TABLE OF jtf_tasks_b.task_split_flag%TYPE;
2117
2118 -- REF Cursor to form different query based on different conditions.
2119 c_task_list ref_cursor_type;
2120
2121 -- Cursor to fetch the WHERE Clause corresponding to the chosen Query.
2122 CURSOR c_query_where_clause IS
2123 SELECT where_clause
2124 FROM csf_dc_queries_b
2125 WHERE query_id = p_query_id;
2126
2127 -- Cursor to fetch all Commit Child Candidates of a Parent Task
2128 -- and only those assigned to Resources belonging to the Dispatcher's Territory.
2129 CURSOR c_child_tasks (p_parent_task_id NUMBER) IS
2130 SELECT t.task_id
2131 , cac.status trip_status
2132 FROM jtf_tasks_b t
2133 , jtf_task_assignments ta
2134 , jtf_task_statuses_b ts
2135 , cac_sr_object_capacity cac
2136 WHERE t.parent_task_id = p_parent_task_id
2137 AND ta.task_id = t.task_id
2138 AND ts.task_status_id = ta.assignment_status_id
2139 AND cac.object_capacity_id(+) = ta.object_capacity_id -- made this outer join for bug 6940526
2140 AND NVL(t.deleted_flag, 'N') <> 'Y'
2141 AND NVL(ts.cancelled_flag, 'N') <> 'Y'
2142 ORDER BY 1 DESC;
2143
2144 --Cursor added for bug 6866929
2145 --This cursor +valriable l_cnt was added for checking multiple assignments for
2146 --for given task
2147 CURSOR check_assignments(p_task_id number)
2148 IS
2149 SELECT count(task_id)
2150 FROM jtf_task_assignments a
2151 , jtf_task_statuses_b b
2152 WHERE a.task_id = p_task_id
2153 AND a.assignment_status_id = b.task_status_id
2154 AND nvl(b.cancelled_flag ,'N') <> 'Y';
2155 l_cnt NUMBER :=1;
2156
2157 l_where_clause csf_dc_queries_b.where_clause%TYPE;
2158 l_query VARCHAR2(2000);
2159 l_task_id_tbl jtf_number_table;
2160 l_task_split_flag_tbl task_split_tbl_type;
2161 l_task_num_tbl jtf_number_table := jtf_number_table();
2162 l_child_task_id_tbl jtf_number_table;
2163 l_trip_status_tbl jtf_number_table;
2164 l_processed_count PLS_INTEGER;
2165 l_blocked_trip_found BOOLEAN;
2166 l_all_passed BOOLEAN;
2167
2168 BEGIN
2169 SAVEPOINT csf_commit_schedule;
2170
2171 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2172 RAISE fnd_api.g_exc_unexpected_error;
2173 END IF;
2174
2175 IF fnd_api.to_boolean (p_init_msg_list) THEN
2176 fnd_msg_pub.initialize;
2177 END IF;
2178
2179 x_return_status := fnd_api.g_ret_sts_success;
2180
2181 -- Check whether the required parameters are passed.
2185 fnd_message.set_token('PARAM_NAME', 'P_QUERY_ID');
2182 IF p_query_id IS NULL AND p_resource_id IS NULL AND p_task_id IS NULL AND p_trip_id IS NULL THEN
2183 fnd_message.set_name('CSF', 'CSF_API_INVALID_PARAM');
2184 fnd_message.set_token('API_NAME', g_pkg_name || '.' || l_api_name);
2186 fnd_msg_pub.ADD;
2187 RAISE fnd_api.g_exc_error;
2188 END IF;
2189
2190 IF p_query_id IS NOT NULL THEN
2191 -- Query will be passed when Commit Schedule Functionality is called from
2192 -- Auto Commit Concurrent Program.
2193
2194 -- Fetch the WHERE Clause for the given Query.
2195 OPEN c_query_where_clause;
2196 FETCH c_query_where_clause INTO l_where_clause;
2197 CLOSE c_query_where_clause;
2198
2199 -- Frame the Task List Query
2200
2201 -- TASK_SPLIT_FLAG is queried from JTF_TASKS_B again since the Query might
2202 -- return PARENT_TASK and CHILD_TASK also and because of that DISTINCT might
2203 -- return two rows one beloning to PARENT_TASK and another for CHILD_TASK bcos
2204 -- of TASK_SPLIT_FLAG.
2205
2206 l_query := ' SELECT DISTINCT NVL(csf_ct_tasks.parent_task_id, csf_ct_tasks.task_id) task_id
2207 , (SELECT t1.task_split_flag
2208 FROM jtf_tasks_b t1
2209 WHERE t1.task_id = NVL(csf_ct_tasks.parent_task_id, csf_ct_tasks.task_id)) task_split_flag
2210 FROM csf_ct_tasks
2211 WHERE ' || l_where_clause || ' ORDER BY 1 DESC';
2212
2213 -- Initialize the REF Cursor to point to the actual Task List Query.
2214 OPEN c_task_list FOR l_query;
2215 ELSIF p_resource_id IS NOT NULL and p_trip_id IS NULL THEN --altered condition for bug 6647019
2216 -- Resource Info and Dates will be passed when Commit Schedule Functionality is
2217 -- called from Plan Board or Gantt at a Resource Level.
2218
2219 -- Frame the Task List Query using the given Resource and Schedule Dates info.
2220
2221 -- There is no way for Parent Task to be queried as part of this Query and its
2222 -- sufficient for us to have Child's Task Split Flag alone
2223
2224 l_query := 'SELECT DISTINCT NVL(t.parent_task_id, t.task_id) task_id
2225 , t.task_split_flag
2226 FROM jtf_tasks_b t
2227 , jtf_task_assignments ta
2228 , jtf_task_statuses_b ts
2229 WHERE ta.resource_id = :1
2230 AND ta.resource_type_code = :2
2231 AND ts.task_status_id = ta.assignment_status_id
2232 AND NVL(ts.closed_flag, ''N'') = ''N''
2233 AND NVL(ts.completed_flag, ''N'') = ''N''
2234 AND NVL(ts.cancelled_flag, ''N'') = ''N''
2235 AND ta.booking_start_date BETWEEN :3 and :4
2236 AND t.task_id = ta.task_id
2237 AND t.task_type_id NOT IN (20,21)
2238 AND NVL(t.deleted_flag, ''N'') <> ''Y''
2239 AND t.source_object_type_code = ''SR''
2240 ORDER BY 1 DESC';
2241
2242 -- Initialize the REF Cursor to point to the actual Task List Query.
2243 OPEN c_task_list FOR l_query USING p_resource_id
2244 , p_resource_type
2245 , p_scheduled_start_date
2246 , p_scheduled_end_date;
2247 ELSIF p_task_id IS NOT NULL THEN
2248 -- There is just one task and its sufficient for us to get the TASK_SPLIT_FLAG
2249 -- of that task.
2250 l_query := 'SELECT NVL(t.parent_task_id, t.task_id) task_id
2251 , task_split_flag
2252 FROM jtf_tasks_b t
2253 WHERE t.task_id = :1';
2254
2255 OPEN c_task_list FOR l_query USING p_task_id;
2256 ELSIF p_trip_id IS NOT NULL THEN
2257 l_query := 'SELECT NVL(t.parent_task_id, t.task_id) task_id
2258 , task_split_flag
2259 FROM cac_sr_object_capacity cac
2260 , jtf_task_assignments ta
2261 , jtf_tasks_b t
2262 , jtf_task_statuses_b ts
2263 WHERE cac.object_capacity_id = :1
2264 AND ta.resource_id = cac.object_id
2265 AND ta.resource_type_code = cac.object_type
2266 AND ta.booking_start_date <= (cac.end_date_time + ' || g_overtime || ')
2267 AND ta.booking_end_date >= cac.start_date_time
2268 AND ts.task_status_id = ta.assignment_status_id
2269 AND NVL(ts.closed_flag, ''N'') = ''N''
2270 AND NVL(ts.completed_flag, ''N'') = ''N''
2271 AND NVL(ts.cancelled_flag, ''N'') = ''N''
2272 AND t.task_id = ta.task_id
2273 AND t.task_type_id NOT IN (20,21)
2274 AND NVL(t.deleted_flag, ''N'') <> ''Y''
2275 AND t.source_object_type_code = ''SR''
2276 ORDER BY 1 DESC';
2277
2278 OPEN c_task_list FOR l_query USING p_trip_id;
2279 END IF;
2280
2281 l_processed_count := 0;
2282 l_all_passed := TRUE;
2283 LOOP
2284 FETCH c_task_list BULK COLLECT INTO l_task_id_tbl, l_task_split_flag_tbl LIMIT 100;
2288 fnd_message.set_name('CSF','CSF_NO_TASK_FOR_RESOURCE');
2285
2286 -- Process each Task in the Task List
2287 IF l_task_id_tbl.COUNT = 0 THEN -- if there are no tasks in the trip #bug7146595
2289 fnd_msg_pub.ADD;
2290 END IF; -- end of code for the bug7146595
2291 FOR i IN 1..l_task_id_tbl.COUNT
2292 LOOP
2293 l_processed_count := l_processed_count + 1;
2294
2295 --The following code is added for this bug 6866929
2296 OPEN check_assignments(l_task_id_tbl(i));
2297 FETCH check_assignments into l_cnt;
2298 CLOSE check_assignments;
2299 IF l_cnt > 1
2300 THEN
2301 fnd_message.set_name('CSF','CSF_AUTO_COMMIT_MULTI_RES');
2302 fnd_message.set_token ('TASK', task_number(l_task_id_tbl(i)));
2303 fnd_msg_pub.ADD;
2304 l_all_passed := FALSE;
2305 --End of the code added for this bug 6866929
2306 ELSE
2307 IF l_task_split_flag_tbl(i) IS NOT NULL THEN
2308 -- The current Task is a Parent Task. Fetch the Child Tasks and Commit them
2309 OPEN c_child_tasks(l_task_id_tbl(i));
2310 FETCH c_child_tasks BULK COLLECT INTO l_child_task_id_tbl, l_trip_status_tbl;
2311 CLOSE c_child_tasks;
2312
2313 -- Check whether any of the Trip containing the Child Task is blocked.
2314 l_blocked_trip_found := FALSE;
2315 FOR j IN 1..l_trip_status_tbl.COUNT LOOP
2316 IF l_trip_status_tbl(j) = csf_trips_pub.g_trip_unavailable THEN
2317 fnd_message.set_name ('CSF', 'CSF_AUTO_COMMIT_CHILD_TRIP_BLK');
2318 fnd_message.set_token ('P_TASK_NUMBER', task_number(l_child_task_id_tbl(j)));
2319 fnd_message.set_token ('P_PARENT_TASK', task_number(l_task_id_tbl(i)));
2320 fnd_msg_pub.ADD;
2321 l_blocked_trip_found := TRUE;
2322 l_all_passed := FALSE;
2323 EXIT;
2324 END IF;
2325 END LOOP;
2326
2327 IF NOT l_blocked_trip_found THEN
2328 FOR j IN 1..l_child_task_id_tbl.COUNT LOOP
2329 commit_task (
2330 p_api_version => 1.0
2331 , x_return_status => x_return_status
2332 , x_msg_data => x_msg_data
2333 , x_msg_count => x_msg_count
2334 , p_task_id => l_child_task_id_tbl(j)
2335 );
2336 IF x_return_status = fnd_api.g_ret_sts_error THEN
2337 l_all_passed := FALSE;
2338 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2339 RAISE fnd_api.g_exc_unexpected_error;
2340 END IF;
2341 END LOOP;
2342 END IF;
2343 ELSE
2344 commit_task (
2345 p_api_version => 1.0
2346 , x_return_status => x_return_status
2347 , x_msg_data => x_msg_data
2348 , x_msg_count => x_msg_count
2349 , p_task_id => l_task_id_tbl(i)
2350 , p_resource_id => p_resource_id --bug 6647019
2351 , p_resource_type => p_resource_type
2352 );
2353 IF x_return_status = fnd_api.g_ret_sts_error THEN
2354 l_all_passed := FALSE;
2355 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2356 RAISE fnd_api.g_exc_unexpected_error;
2357 ELSE --ADDED code for the bug 6801965
2358 l_task_num_tbl.extend;
2359 l_task_num_tbl(l_task_num_tbl.last) := l_task_id_tbl(i);
2360 END IF;
2361 END IF;
2362 END IF;--This is endif for checking multiple task assignments.
2363 END LOOP;
2364 EXIT WHEN c_task_list%NOTFOUND;
2365 END LOOP;
2366
2367
2368
2369 IF l_processed_count = 0 AND p_query_id IS NOT NULL THEN
2370 fnd_message.set_name ('CSF', 'CSF_AUTO_COMMIT_NO_TASK');
2371 fnd_msg_pub.ADD;
2372 x_return_status := fnd_api.g_ret_sts_error;
2373 END IF;
2374
2375 IF NOT l_all_passed THEN
2376 x_return_status := fnd_api.g_ret_sts_error;
2377 END IF;
2378 -- added code for the bug 6801965
2379 IF l_task_num_tbl.count > 0 THEN
2380 FOR i in 1..l_task_num_tbl.count
2381 LOOP
2382 fnd_message.set_name ('CSF', 'CSF_AUTO_COMMITTED');
2383 fnd_message.set_token ('P_TASK_NUMBER', task_number(l_task_num_tbl(i)));
2384 fnd_msg_pub.ADD;
2385 END LOOP;
2386 END IF;
2387 -- end of code for the bug 6801965
2388 CLOSE c_task_list;
2389 -- Standard check of p_commit
2390 IF fnd_api.to_boolean (p_commit) THEN
2391 COMMIT WORK;
2392 END IF;
2393
2394 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2395 EXCEPTION
2396 WHEN fnd_api.g_exc_error THEN
2397 ROLLBACK TO csf_commit_schedule;
2398 x_return_status := fnd_api.g_ret_sts_error;
2399 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2400 WHEN fnd_api.g_exc_unexpected_error THEN
2401 ROLLBACK TO csf_commit_schedule;
2402 x_return_status := fnd_api.g_ret_sts_unexp_error;
2403 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2404 WHEN OTHERS THEN
2405 x_return_status := fnd_api.g_ret_sts_unexp_error;
2406 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2407 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2408 END IF;
2409 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2410 ROLLBACK TO csf_commit_schedule;
2411 END commit_schedule;
2412
2413 /**
2414 * Updates the Task Information of the Parent Task by considering the current information
2415 * of all the Children.
2416 * <br>
2417 * The various attributes updated are
2418 * 1. Task Status Propagation from Child Task to Parent Task
2419 * 2. Scheduled Start Date of the Task
2420 * 3. Scheduled End Date of the Task
2421 * 4. Actual Start Date of the Task
2422 * 5. Actual Effort of the Task
2423 * <br>
2424 * <b> Task Status Propagation </b>
2425 * The Bitcodes of each Task Status is defined above. The Bitcodes have been
2426 * carefully chosen so that AND of the Bitcodes of all the Child Tasks will
2427 * give the Bitcode of the Task Status the Parent should ultimately take.
2428 * <br>
2429 * <b> For Example </b>
2430 * Case#1:
2431 * Let us assume there is a Parent Task P with three children C1, C2 and C3.
2432 *
2433 * C1 Task Status = Closed : Bitcode = 11001
2434 * C2 Task Status = Working : Bitcode = 00001
2435 * C3 Task Status = Assigned : Bitcode = 00011
2436 *
2437 * We expect the Parent Task to be in Working Status. BIT AND of all the Child
2438 * Tasks will result in 00001 which translates to Working.
2439 * <br>
2440 * Case#2:
2441 * Let us assume there is a Parent Task P with three children C1, C2 and C3.
2442 *
2443 * C1 Task Status = Closed : Bitcode = 11001
2444 * C2 Task Status = Closed : Bitcode = 11001
2445 * C3 Task Status = Assigned : Bitcode = 00011
2446 *
2447 * Since one of the Child Tasks is already Closed, it means that the Technician has
2448 * started to work on the Parent Task. So the Task Status should be Working. The BIT AND
2449 * of all the child tasks results in the same thing even though none of the child task is
2450 * in Working status.
2451 * <br>
2452 * Case#3:
2453 * Bitcode Transition will fail however when On-Hold comes into picture. If there are
2454 * any Child Tasks in On-Hold Status and all others are in Closed, Cancelled or Completed
2455 * status, then the Parent should be updated to On-Hold status. Even if any one of the
2456 * Child Task is in Working/Assigned/Planned status, then the Parent Task should
2457 * be updated to Working/Assigned/Planned (in the same order of preference). Thus any
2458 * Bitcode assigned to On-Hold will not work and it has to be treated separately.
2459 * <br>
2460 * Since there are Default Task Profiles for Planned, Asssigned, Cancelled and Working
2461 * a Global PLSQL Table is maintained to cache that information. But we might require
2462 * statuses corresponding to Closed, Completed and On-Hold. These are retrieved from the
2463 * Child Tasks and so another Local Table is also maintained to store these information
2464 * which will go out of scope once the procedure completes. Note that In-Planning
2465 * is not used as a task cant be a Parent if its in In-Planning.
2466 *
2467 * For more information refer to Bug#4032201.
2468 *
2469 * <br>
2470 * Scheduled Start Date will the minimum start date of all the children.
2471 * Scheduled End Date will the maximum end date of all the children.
2472 * Actual Start Date will the minimum start date of all the children.
2473 * Actual End Date will the maximum end date of all the children.
2474 * Actual Effort will be the sum of all the Actuals of Children after converting
2475 * to minutes.
2476 *
2477 * @param p_api_version API Version (1.0)
2478 * @param p_init_msg_list Initialize Message List
2479 * @param p_commit Commit the Work
2480 * @param x_return_status Return Status of the Procedure.
2481 * @param x_msg_count Number of Messages in the Stack.
2482 * @param x_msg_data Stack of Error Messages.
2483 * @param p_parent_task_id Task Identifier of the Parent Task.
2484 * @param p_parent_version_number Object Version of Parent Task
2485 * @param p_planned_start_date Planned start date of Parent Task.
2486 * @param p_planned_end_date Planned end date of Parent Task.
2487 */
2488 PROCEDURE sync_parent_with_child(
2489 p_api_version IN NUMBER
2490 , p_init_msg_list IN VARCHAR2
2491 , p_commit IN VARCHAR2
2492 , x_return_status OUT NOCOPY VARCHAR2
2493 , x_msg_count OUT NOCOPY NUMBER
2494 , x_msg_data OUT NOCOPY VARCHAR2
2495 , p_parent_task_id IN NUMBER
2496 , p_parent_version_number IN OUT NOCOPY NUMBER
2497 , p_planned_start_date IN DATE
2498 , p_planned_end_date IN DATE
2499 ) IS
2500 l_api_name CONSTANT VARCHAR2(30) := 'SYNC_PARENT_WITH_CHILD';
2501 l_api_version CONSTANT NUMBER := 1.0;
2502
2503 CURSOR c_curr_parent_info IS
2504 SELECT t.task_status_id
2505 , t.actual_start_date
2506 , t.actual_end_date
2507 , t.scheduled_start_date
2508 , t.scheduled_end_date
2509 , t.planned_start_date
2510 , t.planned_end_date
2511 , csf_util_pvt.convert_to_minutes(t.actual_effort, t.actual_effort_uom) actual_effort
2512 FROM jtf_tasks_b t
2513 WHERE t.task_id = p_parent_task_id
2514 AND NVL(t.deleted_flag, 'N') <> 'Y';
2515
2516 CURSOR c_new_parent_info IS
2517 SELECT g_inplanning task_status_id
2518 , MIN(t.scheduled_start_date) scheduled_start_date
2519 , MAX(t.scheduled_end_date) scheduled_end_date
2520 , MIN(t.actual_start_date) actual_start_date
2521 , MAX(t.actual_end_date) actual_end_date
2522 , SUM(csf_util_pvt.convert_to_minutes(t.actual_effort, t.actual_effort_uom)) actual_effort
2523 FROM jtf_tasks_b t
2524 , jtf_task_statuses_b ts
2525 WHERE t.parent_task_id = p_parent_task_id
2526 AND NVL(t.deleted_flag, 'N') <> 'Y'
2527 AND ts.task_status_id = t.task_status_id
2528 AND NVL(ts.cancelled_flag, 'N') <> 'Y';
2529
2530 CURSOR c_child_tasks IS
2531 SELECT t.task_id
2532 , t.task_status_id
2533 , NVL(ts.schedulable_flag, 'N') schedulable_flag
2534 , NVL(ts.assigned_flag, 'N') assigned_flag
2535 , NVL(ts.working_flag, 'N') working_flag
2536 , NVL(ts.completed_flag, 'N') completed_flag
2537 , NVL(ts.closed_flag, 'N') closed_flag
2538 , NVL(ts.on_hold_flag, 'N') on_hold_flag
2539 , NVL(ts.rejected_flag, 'N') rejected_flag
2540 , NVL(ts.cancelled_flag, 'N') cancelled_flag
2541 , NVL(ts.accepted_flag, 'N') accepted_flag
2542 , NVL(ts.assignment_status_flag, 'N') assignment_status_flag
2543 , 0 status_bitcode
2544 FROM jtf_tasks_b t
2545 , jtf_task_statuses_b ts
2546 WHERE t.parent_task_id = p_parent_task_id
2547 AND ts.task_status_id = t.task_status_id
2548 AND NVL(t.deleted_flag, 'N') <> 'Y'
2549 ORDER BY t.task_id;
2550
2551 l_status_bitcode_map_tbl number_tbl_type;
2552
2553 l_curr_parent_info c_curr_parent_info%ROWTYPE;
2554 l_new_parent_info c_new_parent_info%ROWTYPE;
2555 l_pri_sts_bitcode NUMBER;
2556 l_sec_sts_bitcode NUMBER;
2557 l_update_parent BOOLEAN;
2558 l_actual_effort_uom VARCHAR2(3);
2559 --*********** added for bug 6646890************
2560 l_update BOOLEAN := FALSE;
2561 l_child_status NUMBER;
2562 i NUMBER := 1;
2563 --*********** added for bug 6646890************
2564
2565
2566 FUNCTION get_status_bitcode(p_task c_child_tasks%ROWTYPE)
2567 RETURN NUMBER IS
2568 l_status_bitcode NUMBER;
2569 BEGIN
2570 l_status_bitcode := g_start_bitcode;
2571
2572 IF p_task.cancelled_flag = 'N' AND p_task.rejected_flag = 'N' AND p_task.on_hold_flag = 'N' THEN
2573 IF p_task.closed_flag = 'Y' THEN
2574 l_status_bitcode := g_closed_bitcode;
2575 ELSIF p_task.completed_flag = 'Y' THEN
2576 l_status_bitcode := g_completed_bitcode;
2577 ELSIF p_task.working_flag = 'Y' THEN
2578 l_status_bitcode := g_working_bitcode;
2579 --*********** added for bug 6646890************
2580 ELSIF p_task.accepted_flag = 'Y' THEN
2581 l_status_bitcode := g_accepted_bitcode;
2582 --*********** added for bug 6646890************
2583 ELSIF p_task.assigned_flag = 'Y' THEN
2584 l_status_bitcode := g_assigned_bitcode;
2585 ELSIF p_task.schedulable_flag = 'Y' THEN
2586 l_status_bitcode := g_planned_bitcode;
2587 END IF;
2588 --RETURN l_status_bitcode + 480; -- 480 stands for 111100000
2589 ELSE
2590 IF p_task.cancelled_flag = 'Y' THEN
2591 l_status_bitcode := g_cancelled_bitcode;
2592 ELSIF p_task.rejected_flag = 'Y' THEN
2593 l_status_bitcode := g_rejected_bitcode;
2594 ELSE
2595 l_status_bitcode := g_onhold_bitcode;
2596 END IF;
2597 --RETURN l_status_bitcode + 31; -- 31 stands for 000011111
2598 END IF;
2599
2600 RETURN l_status_bitcode;
2601 END get_status_bitcode;
2602 BEGIN
2603 SAVEPOINT csf_sync_parent_with_child;
2604
2605 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2606 RAISE fnd_api.g_exc_unexpected_error;
2607 END IF;
2608
2609 IF fnd_api.to_boolean (p_init_msg_list) THEN
2610 fnd_msg_pub.initialize;
2611 END IF;
2612
2613 x_return_status := fnd_api.g_ret_sts_success;
2614
2615 OPEN c_curr_parent_info;
2616 FETCH c_curr_parent_info INTO l_curr_parent_info;
2617 CLOSE c_curr_parent_info;
2618
2619 OPEN c_new_parent_info;
2620 FETCH c_new_parent_info INTO l_new_parent_info;
2621 CLOSE c_new_parent_info;
2622
2623 /****************************************************************************
2624 * Propagating the Child Task's Status to the Parent *
2625 *****************************************************************************/
2626 -- Initialize the Finite Automata.
2627 l_pri_sts_bitcode := g_start_bitcode;
2628 l_sec_sts_bitcode := g_start_bitcode;
2629
2630 -- If we have Child Tasks one in Planned and another in Closed, we have to
2631 -- move the Parent to Working Status. But none of the Children would have
2632 -- given the Working Status ID. So take it from Default Value.
2633 l_status_bitcode_map_tbl(g_working_bitcode) := g_working;
2634
2635 FOR v_child IN c_child_tasks LOOP
2636 -- Compute the Bit Code of the Current Child Task.
2637 --*********** added for bug 6646890************
2638 IF i=1 THEN
2639 l_child_status := v_child.task_status_id ;
2640 i := i+1;
2641 END IF;
2645 l_update := FALSE;
2642 IF l_child_status = v_child.task_status_id THEN
2643 l_update := TRUE;
2644 ELSE
2646 END IF;
2647 --*********** added for bug 6646890************
2648
2649 v_child.status_bitcode := get_status_bitcode(v_child);
2650
2651 IF v_child.status_bitcode <> g_start_bitcode THEN
2652 IF BITAND (v_child.status_bitcode, 63) BETWEEN 1 AND 62 THEN
2653 l_pri_sts_bitcode := BITAND(l_pri_sts_bitcode, v_child.status_bitcode);
2654 l_status_bitcode_map_tbl(v_child.status_bitcode) := v_child.task_status_id;
2655 ELSIF BITAND (v_child.status_bitcode, 448) BETWEEN 63 AND 510 THEN
2656 l_sec_sts_bitcode := BITAND(l_sec_sts_bitcode, v_child.status_bitcode);
2657 l_status_bitcode_map_tbl(v_child.status_bitcode) := v_child.task_status_id;
2658 END IF;
2659 END IF;
2660 END LOOP;
2661
2662 -- If we have a valid Primary Status for Parent, then we have to use that status.
2663 -- Otherwise we have to try using Secondary Status.
2664 -- (l_pri_sts_bitcode in (17,49) and l_sec_sts_bitcode=g_onhold_bitcode ) has been added for the bug for the following
2665 -- scenario:
2666 -- Suppose there are two child tasks T1,T2.T1 is in onhold status and T2 in Completed/Closed Status . Then the parent task
2667 -- status should be Onhold.
2668 IF (l_pri_sts_bitcode in (17,49) and l_sec_sts_bitcode=g_onhold_bitcode ) or l_pri_sts_bitcode >= 63 THEN
2669 l_pri_sts_bitcode := l_sec_sts_bitcode;
2670 END IF;
2671
2672 IF l_status_bitcode_map_tbl.EXISTS(l_pri_sts_bitcode) and not (l_update) THEN
2673 l_new_parent_info.task_status_id := l_status_bitcode_map_tbl(l_pri_sts_bitcode);
2674 --*********** added for bug 6646890************
2675 ELSIF l_update THEN
2676 l_new_parent_info.task_status_id := l_child_status;
2677 END IF;
2678 --*********** added for bug 6646890************
2679
2680 /****************************************************************************
2681 * Finding out whether Parent's Data has Changed *
2682 *****************************************************************************/
2683 l_update_parent :=
2684 l_curr_parent_info.task_status_id <> l_new_parent_info.task_status_id
2685 OR ( NVL(l_curr_parent_info.scheduled_start_date, fnd_api.g_miss_date)
2686 <> NVL(l_new_parent_info.scheduled_start_date, fnd_api.g_miss_date) )
2687 OR ( NVL(l_curr_parent_info.scheduled_end_date, fnd_api.g_miss_date)
2688 <> NVL(l_new_parent_info.scheduled_end_date, fnd_api.g_miss_date) )
2689 OR ( NVL(l_curr_parent_info.actual_start_date, fnd_api.g_miss_date)
2690 <> NVL(l_new_parent_info.actual_start_date, fnd_api.g_miss_date) )
2691 OR ( NVL(l_curr_parent_info.actual_end_date, fnd_api.g_miss_date)
2692 <> NVL(l_new_parent_info.actual_end_date, fnd_api.g_miss_date) )
2693 OR ( NVL(l_curr_parent_info.planned_start_date, fnd_api.g_miss_date)
2694 <> NVL(p_planned_start_date, fnd_api.g_miss_date) )
2695 OR ( NVL(l_curr_parent_info.planned_end_date, fnd_api.g_miss_date)
2696 <> NVL(p_planned_end_date, fnd_api.g_miss_date) )
2697 OR ( NVL(l_curr_parent_info.actual_effort, -1)
2698 <> NVL(l_new_parent_info.actual_effort, -1) );
2699
2700
2701 /****************************************************************************
2702 * Updating the Parent Task Information *
2703 *****************************************************************************/
2704 IF l_update_parent THEN
2705 IF l_new_parent_info.actual_effort IS NOT NULL THEN
2706 l_actual_effort_uom := csf_util_pvt.get_uom_minutes;
2707 END IF;
2708
2709 jtf_tasks_pub.update_task (
2710 p_api_version => 1.0
2711 , p_init_msg_list => p_init_msg_list
2712 , p_commit => fnd_api.g_false
2713 , x_return_status => x_return_status
2714 , x_msg_count => x_msg_count
2715 , x_msg_data => x_msg_data
2716 , p_task_id => p_parent_task_id
2717 , p_object_version_number => p_parent_version_number
2718 , p_task_status_id => l_new_parent_info.task_status_id
2719 , p_scheduled_start_date => l_new_parent_info.scheduled_start_date
2720 , p_scheduled_end_date => l_new_parent_info.scheduled_end_date
2721 , p_planned_start_date => p_planned_start_date
2722 , p_planned_end_date => p_planned_end_date
2723 , p_actual_start_date => l_new_parent_info.actual_start_date
2724 , p_actual_end_date => l_new_parent_info.actual_end_date
2725 , p_actual_effort => l_new_parent_info.actual_effort
2726 , p_actual_effort_uom => l_actual_effort_uom
2727 , p_task_split_flag => 'M'
2728 , p_enable_workflow => fnd_api.g_miss_char
2729 , p_abort_workflow => fnd_api.g_miss_char
2730 );
2731
2732 IF x_return_status = fnd_api.g_ret_sts_error THEN
2733 RAISE fnd_api.g_exc_error;
2734 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2735 RAISE fnd_api.g_exc_unexpected_error;
2736 END IF;
2737 END IF;
2738
2739 -- Standard check of p_commit
2740 IF fnd_api.to_boolean (p_commit) THEN
2741 COMMIT WORK;
2745 ROLLBACK TO csf_sync_parent_with_child;
2742 END IF;
2743 EXCEPTION
2744 WHEN fnd_api.g_exc_error THEN
2746 x_return_status := fnd_api.g_ret_sts_error;
2747 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2748 WHEN fnd_api.g_exc_unexpected_error THEN
2749 ROLLBACK TO csf_sync_parent_with_child;
2750 x_return_status := fnd_api.g_ret_sts_unexp_error;
2751 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2752 WHEN OTHERS THEN
2753 x_return_status := fnd_api.g_ret_sts_unexp_error;
2754 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2755 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2756 END IF;
2757 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2758 ROLLBACK TO csf_sync_parent_with_child;
2759 END sync_parent_with_child;
2760
2761 /**
2762 * Updates the Attributes of the Child Tasks by considering the Parent Task.
2763 *
2764 * @param p_api_version API Version (1.0)
2765 * @param p_init_msg_list Initialize Message List
2766 * @param p_commit Commit the Work
2767 * @param x_return_status Return Status of the Procedure.
2768 * @param x_msg_count Number of Messages in the Stack.
2769 * @param x_msg_data Stack of Error Messages.
2770 * @param p_parent_task_id Task Identifier of the Parent Task.
2771 */
2772 PROCEDURE sync_child_from_parent(
2773 p_api_version IN NUMBER
2774 , p_init_msg_list IN VARCHAR2
2775 , p_commit IN VARCHAR2
2776 , x_return_status OUT NOCOPY VARCHAR2
2777 , x_msg_count OUT NOCOPY NUMBER
2778 , x_msg_data OUT NOCOPY VARCHAR2
2779 , p_parent_task_id IN NUMBER
2780 ) IS
2781 l_api_name CONSTANT VARCHAR2(30) := 'SYNC_CHILD_FROM_PARENT';
2782 l_api_version CONSTANT NUMBER := 1.0;
2783
2784 CURSOR c_child_tasks IS
2785 SELECT t.task_id
2786 , t.object_version_number
2787 , NVL(t.child_position, '@@') child_position
2788 , NVL(t.child_sequence_num, -1) child_sequence_num
2789 , RANK() OVER (ORDER BY t.scheduled_start_date, t.scheduled_end_date,nvl(t.child_sequence_num,-1)) correct_seq_num
2790 , LEAD (t.task_id) OVER (ORDER BY t.scheduled_start_date, t.scheduled_end_date,nvl(t.child_sequence_num,-1)) next_task_id
2791 FROM jtf_tasks_b t ,jtf_task_statuses_b ts
2792 WHERE t.parent_task_id = p_parent_task_id
2793 AND NVL(t.deleted_flag, 'N') <> 'Y'
2794 AND ts.task_status_id = t.task_status_id
2795 AND NVL(ts.cancelled_flag, 'N') <> 'Y';
2796
2797 l_child_position jtf_tasks_b.child_position%TYPE;
2798 BEGIN
2799 SAVEPOINT csf_sync_child_from_parent;
2800
2801 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2802 RAISE fnd_api.g_exc_unexpected_error;
2803 END IF;
2804
2805 IF fnd_api.to_boolean (p_init_msg_list) THEN
2806 fnd_msg_pub.initialize;
2807 END IF;
2808
2809 x_return_status := fnd_api.g_ret_sts_success;
2810
2811 l_child_position := 'F';
2812 FOR v_child_task IN c_child_tasks LOOP
2813 IF v_child_task.next_task_id IS NULL AND v_child_task.correct_seq_num <> 1 THEN
2814 l_child_position := 'L';
2815 END IF;
2816
2817 IF ( (v_child_task.child_sequence_num <> v_child_task.correct_seq_num)
2818 OR (v_child_task.child_position <> l_child_position) )
2819 THEN
2820 -- Update the Child Task
2821 jtf_tasks_pub.update_task(
2822 p_api_version => 1.0
2823 , x_return_status => x_return_status
2824 , x_msg_count => x_msg_count
2825 , x_msg_data => x_msg_data
2826 , p_task_id => v_child_task.task_id
2827 , p_task_split_flag => fnd_api.g_miss_char
2828 , p_object_version_number => v_child_task.object_version_number
2829 , p_child_sequence_num => v_child_task.correct_seq_num
2830 , p_child_position => l_child_position
2831 , p_enable_workflow => fnd_api.g_miss_char
2832 , p_abort_workflow => fnd_api.g_miss_char
2833 );
2834
2835 IF x_return_status = fnd_api.g_ret_sts_error THEN
2836 RAISE fnd_api.g_exc_error;
2837 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2838 RAISE fnd_api.g_exc_unexpected_error;
2839 END IF;
2840 END IF;
2841
2842 l_child_position := 'M';
2843 END LOOP;
2844
2845 -- Standard check of p_commit
2846 IF fnd_api.to_boolean (p_commit) THEN
2847 COMMIT WORK;
2848 END IF;
2849 EXCEPTION
2850 WHEN fnd_api.g_exc_error THEN
2851 ROLLBACK TO csf_sync_child_from_parent;
2852 x_return_status := fnd_api.g_ret_sts_error;
2853 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2854 WHEN fnd_api.g_exc_unexpected_error THEN
2855 ROLLBACK TO csf_sync_child_from_parent;
2856 x_return_status := fnd_api.g_ret_sts_unexp_error;
2857 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2858 WHEN OTHERS THEN
2859 x_return_status := fnd_api.g_ret_sts_unexp_error;
2860 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2861 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2862 END IF;
2863 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2864 ROLLBACK TO csf_sync_child_from_parent;
2865 END sync_child_from_parent;
2866
2867 PROCEDURE assign_task(
2868 p_api_version IN NUMBER
2869 , p_init_msg_list IN VARCHAR2
2870 , p_commit IN VARCHAR2
2871 , x_return_status OUT NOCOPY VARCHAR2
2872 , x_msg_count OUT NOCOPY NUMBER
2873 , x_msg_data OUT NOCOPY VARCHAR2
2874 , p_task_id IN NUMBER
2875 , p_object_version_number IN OUT NOCOPY NUMBER
2876 , p_task_status_id IN NUMBER
2877 , p_scheduled_start_date IN DATE
2878 , p_scheduled_end_date IN DATE
2879 , p_planned_start_date IN DATE
2880 , p_planned_end_date IN DATE
2881 , p_old_task_assignment_id IN NUMBER
2882 , p_old_ta_object_version IN NUMBER
2883 , p_assignment_status_id IN NUMBER
2884 , p_resource_id IN NUMBER
2885 , p_resource_type IN VARCHAR2
2886 , p_object_capacity_id IN NUMBER
2887 , p_sched_travel_distance IN NUMBER
2888 , p_sched_travel_duration IN NUMBER
2889 , p_sched_travel_duration_uom IN VARCHAR2
2890 , p_planned_effort IN NUMBER
2891 , p_planned_effort_uom IN VARCHAR2
2892 , x_task_assignment_id OUT NOCOPY NUMBER
2893 , x_ta_object_version_number OUT NOCOPY NUMBER
2894 ) IS
2895 l_api_name CONSTANT VARCHAR2(30) := 'ASSIGN_TASK';
2896 l_api_version CONSTANT NUMBER := 1.0;
2897
2898 CURSOR c_task_info IS
2899 SELECT t.task_id
2900 , t.task_status_id
2901 , t.task_split_flag
2902 , t.object_version_number
2903 , t.scheduled_start_date
2904 , t.scheduled_end_date
2905 , NVL( ( SELECT 'Y'
2906 FROM jtf_task_assignments ta, jtf_task_statuses_b ats
2907 WHERE ta.task_id = p_task_id
2908 AND ta.assignment_status_id = ats.task_status_id
2909 AND NVL(ats.cancelled_flag, 'N') <> 'Y'
2910 AND ROWNUM = 1
2911 ), 'N'
2912 ) is_scheduled
2913 FROM jtf_tasks_b t
2914 WHERE t.task_id = p_task_id;
2915
2916 CURSOR c_task_assignment_info IS
2917 SELECT ta.resource_id
2918 , ta.resource_type_code
2919 FROM jtf_task_assignments ta
2920 WHERE ta.task_assignment_id = p_old_task_assignment_id;
2921
2922 l_task_info c_task_info%ROWTYPE;
2923 l_task_assignment_info c_task_assignment_info%ROWTYPE;
2924 l_planned_effort NUMBER;
2925 l_planned_effort_uom VARCHAR2(3);
2926 l_create_assignment BOOLEAN;
2927 l_assignment_status_id NUMBER;
2928 BEGIN
2929 SAVEPOINT csf_assign_task;
2930
2931 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2932 RAISE fnd_api.g_exc_unexpected_error;
2933 END IF;
2934
2935 IF fnd_api.to_boolean(p_init_msg_list) THEN
2936 fnd_msg_pub.initialize;
2937 END IF;
2938
2939 x_return_status := fnd_api.g_ret_sts_success;
2940
2941 -- Get the Task Information
2942 OPEN c_task_info;
2943 FETCH c_task_info INTO l_task_info;
2944 CLOSE c_task_info;
2945
2946 IF l_task_info.task_id IS NULL THEN
2947 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'NO_DATA_FOUND JTF_TASKS_B.TASK_ID = ' || p_task_id);
2948 RAISE fnd_api.g_exc_unexpected_error;
2949 END IF;
2950
2951 -- If the Task is already Scheduled, then the Task Should be treated to be
2952 -- in Unscheduled Task Status as the task should have been unscheduled
2953 -- before rescheduling. Since we are avoiding unnecessary unscheduling,
2954 -- lets assume the old task status to be Unscheduled Task Status.
2955 IF l_task_info.is_scheduled = 'Y' THEN
2956 l_task_info.task_status_id := g_unscheduled;
2957 END IF;
2958
2959 -- Find out whether the new Task Status is valid.
2960 validate_status_change(l_task_info.task_status_id, p_task_status_id);
2961
2962 -- If the Old Assignment specified is linked to the same resource as that
2963 -- of the New Assignment, then there is no need to cancel the Old Assignment.
2964 -- Rather just update the Old Assignment with the new Travel Times.
2965 l_create_assignment := TRUE;
2966
2967 IF p_old_task_assignment_id IS NOT NULL THEN
2968 x_ta_object_version_number := p_old_ta_object_version;
2969 l_assignment_status_id := g_cancelled;
2970
2971 OPEN c_task_assignment_info;
2972 FETCH c_task_assignment_info INTO l_task_assignment_info;
2973 CLOSE c_task_assignment_info;
2974
2975 IF l_task_assignment_info.resource_id = p_resource_id
2979 l_assignment_status_id := p_assignment_status_id;
2976 AND l_task_assignment_info.resource_type_code = p_resource_type
2977 THEN
2978 l_create_assignment := FALSE;
2980 x_task_assignment_id := p_old_task_assignment_id;
2981 END IF;
2982
2983 csf_task_assignments_pub.update_task_assignment (
2984 p_api_version => 1.0
2985 , p_validation_level => fnd_api.g_valid_level_none
2986 , x_return_status => x_return_status
2987 , x_msg_count => x_msg_count
2988 , x_msg_data => x_msg_data
2989 , p_task_assignment_id => p_old_task_assignment_id
2990 , p_object_version_number => x_ta_object_version_number
2991 , p_assignment_status_id => l_assignment_status_id
2992 , p_object_capacity_id => p_object_capacity_id
2993 , p_sched_travel_distance => p_sched_travel_distance
2994 , p_sched_travel_duration => p_sched_travel_duration
2995 , p_sched_travel_duration_uom => p_sched_travel_duration_uom
2996 , p_update_task => fnd_api.g_false
2997 , x_task_object_version_number => l_task_info.object_version_number
2998 , x_task_status_id => l_task_info.task_status_id
2999 );
3000 IF x_return_status = fnd_api.g_ret_sts_error THEN
3001 RAISE fnd_api.g_exc_error;
3002 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3003 RAISE fnd_api.g_exc_unexpected_error;
3004 END IF;
3005 END IF;
3006
3007 l_planned_effort := fnd_api.g_miss_num;
3008 l_planned_effort_uom := fnd_api.g_miss_char;
3009
3010 IF (l_task_info.task_split_flag = 'D') THEN
3011 l_planned_effort := p_planned_effort;
3012 l_planned_effort_uom := p_planned_effort_uom;
3013 END IF;
3014
3015 -- Update the Task
3016 jtf_tasks_pub.update_task(
3017 p_api_version => 1.0
3018 , x_return_status => x_return_status
3019 , x_msg_count => x_msg_count
3020 , x_msg_data => x_msg_data
3021 , p_task_id => p_task_id
3022 , p_object_version_number => p_object_version_number
3023 , p_task_status_id => p_task_status_id
3024 , p_scheduled_start_date => p_scheduled_start_date
3025 , p_scheduled_end_date => p_scheduled_end_date
3026 , p_planned_start_date => p_planned_start_date
3027 , p_planned_end_date => p_planned_end_date
3028 , p_planned_effort => l_planned_effort
3029 , p_planned_effort_uom => l_planned_effort_uom
3030 , p_enable_workflow => fnd_api.g_miss_char
3031 , p_abort_workflow => fnd_api.g_miss_char
3032 );
3033 IF x_return_status = fnd_api.g_ret_sts_error THEN
3034 RAISE fnd_api.g_exc_error;
3035 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3036 RAISE fnd_api.g_exc_unexpected_error;
3037 END IF;
3038
3039 -- Create the Task Assignment
3040 IF l_create_assignment THEN
3041 csf_task_assignments_pub.create_task_assignment(
3042 p_api_version => 1.0
3043 , p_validation_level => fnd_api.g_valid_level_none
3044 , x_return_status => x_return_status
3045 , x_msg_count => x_msg_count
3046 , x_msg_data => x_msg_data
3047 , p_task_id => p_task_id
3048 , p_resource_id => p_resource_id
3049 , p_resource_type_code => p_resource_type
3050 , p_assignment_status_id => p_assignment_status_id
3051 , p_object_capacity_id => p_object_capacity_id
3052 , p_sched_travel_distance => p_sched_travel_distance
3053 , p_sched_travel_duration => p_sched_travel_duration
3054 , p_sched_travel_duration_uom => p_sched_travel_duration_uom
3055 , p_update_task => fnd_api.g_false
3056 , x_task_assignment_id => x_task_assignment_id
3057 , x_ta_object_version_number => x_ta_object_version_number
3058 , x_task_object_version_number => p_object_version_number
3059 , x_task_status_id => l_task_info.task_status_id
3060 );
3061
3062 IF x_return_status = fnd_api.g_ret_sts_error THEN
3063 RAISE fnd_api.g_exc_error;
3064 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3065 RAISE fnd_api.g_exc_unexpected_error;
3066 END IF;
3067 END IF;
3068
3069 IF fnd_api.to_boolean(p_commit) THEN
3070 COMMIT WORK;
3071 END IF;
3072
3073 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3074
3075 EXCEPTION
3076 WHEN fnd_api.g_exc_error THEN
3077 ROLLBACK TO csf_assign_task;
3078 x_return_status := fnd_api.g_ret_sts_error;
3079 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3080 WHEN fnd_api.g_exc_unexpected_error THEN
3081 ROLLBACK TO csf_assign_task;
3082 x_return_status := fnd_api.g_ret_sts_unexp_error;
3083 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3084 WHEN OTHERS THEN
3085 x_return_status := fnd_api.g_ret_sts_unexp_error;
3086 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3087 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3091 END assign_task;
3088 END IF;
3089 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3090 ROLLBACK TO csf_assign_task;
3092
3093 PROCEDURE unassign_task(
3094 p_api_version IN NUMBER
3095 , p_init_msg_list IN VARCHAR2
3096 , p_commit IN VARCHAR2
3097 , x_return_status OUT NOCOPY VARCHAR2
3098 , x_msg_count OUT NOCOPY NUMBER
3099 , x_msg_data OUT NOCOPY VARCHAR2
3100 , p_task_id IN NUMBER
3101 , p_object_version_number IN OUT NOCOPY NUMBER
3102 , p_task_status_id IN NUMBER
3103 , p_task_assignment_id IN NUMBER
3104 , p_ta_object_version_number IN OUT NOCOPY NUMBER
3105 , p_assignment_status_id IN NUMBER
3106 ) IS
3107 l_api_name CONSTANT VARCHAR2(30) := 'UNASSIGN_TASK';
3108 l_api_version CONSTANT NUMBER := 1.0;
3109
3110 CURSOR c_task_info IS
3111 SELECT t.task_id
3112 , t.task_status_id
3113 , t.task_split_flag
3114 , source_object_type_code
3115 , scheduled_start_date
3116 , scheduled_end_date
3117 , ta.assignment_status_id
3118 , ta.object_capacity_id
3119 FROM jtf_tasks_b t , jtf_task_assignments ta
3120 WHERE t.task_id = p_task_id
3121 AND ta.task_id = t.task_id
3122 AND ta.task_assignment_id = p_task_assignment_id;
3123
3124 -- Fetch the Flags corresponding to the new Task Status.
3125 CURSOR c_task_status_info IS
3126 SELECT NVL (ts.closed_flag, 'N') closed_flag
3127 , NVL (ts.cancelled_flag, 'N') cancelled_flag
3128 FROM jtf_task_statuses_b ts
3129 WHERE ts.task_status_id = p_task_status_id;
3130
3131 l_task_info c_task_info%ROWTYPE;
3132 l_task_status_info c_task_status_info%ROWTYPE;
3133 l_task_status_id NUMBER;
3134 BEGIN
3135 SAVEPOINT csf_unassign_task;
3136
3137 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3138 RAISE fnd_api.g_exc_unexpected_error;
3139 END IF;
3140
3141 IF fnd_api.to_boolean(p_init_msg_list) THEN
3142 fnd_msg_pub.initialize;
3143 END IF;
3144
3145 x_return_status := fnd_api.g_ret_sts_success;
3146
3147 OPEN c_task_info;
3148 FETCH c_task_info INTO l_task_info;
3149 CLOSE c_task_info;
3150
3151 IF nvl(l_task_info.assignment_status_id, -1) <> NVL(p_assignment_status_id, g_cancelled)
3152 OR l_task_info.object_capacity_id IS NOT NULL THEN
3153 -- Cancel the Task Assignment
3154 -- P_OBJECT_CAPACITY_ID is passed as NULL so that when UPDATE_ASSIGNMENT_STATUS
3155 -- Queries the Task Information, there will not be any Trip Information and
3156 -- Update is avoided as Scheduler will take care of the update.
3157 csf_task_assignments_pub.update_task_assignment (
3158 p_api_version => 1.0
3159 , p_validation_level => fnd_api.g_valid_level_none
3160 , x_return_status => x_return_status
3161 , x_msg_count => x_msg_count
3162 , x_msg_data => x_msg_data
3163 , p_task_assignment_id => p_task_assignment_id
3164 , p_assignment_status_id => NVL(p_assignment_status_id, g_cancelled)
3165 , p_object_version_number => p_ta_object_version_number
3166 , p_object_capacity_id => NULL
3167 , p_update_task => fnd_api.g_false
3168 , x_task_object_version_number => p_object_version_number
3169 , x_task_status_id => l_task_status_id
3170 );
3171 IF x_return_status = fnd_api.g_ret_sts_error THEN
3172 RAISE fnd_api.g_exc_error;
3173 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3174 RAISE fnd_api.g_exc_unexpected_error;
3175 END IF;
3176 END IF;
3177
3178 IF NVL(p_task_status_id,-1) <> nvl(l_task_info.task_status_id,-1)
3179 OR ( l_task_info.source_object_type_code = 'SR'
3180 AND ( l_task_info.scheduled_start_date IS NOT NULL
3181 OR l_task_info.scheduled_end_date IS NOT NULL ) ) THEN
3182
3183 -- Validate the Task Status Transition
3184 validate_status_change(l_task_info.task_status_id, p_task_status_id);
3185
3186 IF l_task_info.source_object_type_code = 'SR' THEN
3187 l_task_info.scheduled_start_date := NULL;
3188 l_task_info.scheduled_end_date := NULL;
3189 END IF;
3190
3191 -- Update the Task Information.
3192 jtf_tasks_pub.update_task(
3193 p_api_version => 1.0
3194 , x_return_status => x_return_status
3195 , x_msg_count => x_msg_count
3196 , x_msg_data => x_msg_data
3197 , p_task_id => p_task_id
3198 , p_object_version_number => p_object_version_number
3199 , p_task_status_id => p_task_status_id
3200 , p_scheduled_start_date => l_task_info.scheduled_start_date
3201 , p_scheduled_end_date => l_task_info.scheduled_end_date
3202 , p_enable_workflow => fnd_api.g_miss_char
3203 , p_abort_workflow => fnd_api.g_miss_char
3204 );
3205 IF x_return_status = fnd_api.g_ret_sts_error THEN
3206 RAISE fnd_api.g_exc_error;
3210 END IF;
3207 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3208 RAISE fnd_api.g_exc_unexpected_error;
3209 END IF;
3211
3212 -- if the task is a child task and is being cancelled, delete(logically) the task
3213 IF l_task_info.task_split_flag = 'D' THEN
3214
3215 OPEN c_task_status_info;
3216 FETCH c_task_status_info INTO l_task_status_info;
3217 CLOSE c_task_status_info;
3218
3219 IF l_task_status_info.cancelled_flag = 'Y' THEN
3220 csf_tasks_pub.delete_task (
3221 p_api_version => 1.0
3222 , x_return_status => x_return_status
3223 , x_msg_count => x_msg_count
3224 , x_msg_data => x_msg_data
3225 , p_task_id => p_task_id
3226 , p_object_version_number => p_object_version_number
3227 );
3228
3229 IF x_return_status = fnd_api.g_ret_sts_error THEN
3230 RAISE fnd_api.g_exc_error;
3231 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3232 RAISE fnd_api.g_exc_unexpected_error;
3233 END IF;
3234 END IF;
3235 END IF;
3236
3237 IF fnd_api.to_boolean(p_commit) THEN
3238 COMMIT WORK;
3239 END IF;
3240 EXCEPTION
3241 WHEN fnd_api.g_exc_error THEN
3242 ROLLBACK TO csf_unassign_task;
3243 x_return_status := fnd_api.g_ret_sts_error;
3244 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3245 WHEN fnd_api.g_exc_unexpected_error THEN
3246 ROLLBACK TO csf_unassign_task;
3247 x_return_status := fnd_api.g_ret_sts_unexp_error;
3248 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3249 WHEN OTHERS THEN
3250 x_return_status := fnd_api.g_ret_sts_unexp_error;
3251 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3252 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3253 END IF;
3254 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3255 ROLLBACK TO csf_unassign_task;
3256 END unassign_task;
3257
3258 PROCEDURE update_task_and_assignment(
3259 p_api_version IN NUMBER
3260 , p_init_msg_list IN VARCHAR2
3261 , p_commit IN VARCHAR2
3262 , x_return_status OUT NOCOPY VARCHAR2
3263 , x_msg_count OUT NOCOPY NUMBER
3264 , x_msg_data OUT NOCOPY VARCHAR2
3265 , p_task_id IN NUMBER
3266 , p_object_version_number IN OUT NOCOPY NUMBER
3267 , p_scheduled_start_date IN DATE
3268 , p_scheduled_end_date IN DATE
3269 , p_task_assignment_id IN NUMBER
3270 , p_ta_object_version_number IN OUT NOCOPY NUMBER
3271 , p_sched_travel_distance IN NUMBER
3272 , p_sched_travel_duration IN NUMBER
3273 , p_sched_travel_duration_uom IN VARCHAR2
3274 ) IS
3275 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TASK_AND_ASSIGNMENT';
3276 l_api_version CONSTANT NUMBER := 1.0;
3277 l_scheduled_start DATE;
3278 l_scheduled_end DATE;
3279 l_distance NUMBER;
3280 l_duration NUMBER;
3281 l_duration_uom VARCHAR2(3);
3282
3283 BEGIN
3284 SAVEPOINT csf_update_task_and_assignment;
3285
3286 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3287 RAISE fnd_api.g_exc_unexpected_error;
3288 END IF;
3289
3290 IF fnd_api.to_boolean(p_init_msg_list) THEN
3291 fnd_msg_pub.initialize;
3292 END IF;
3293
3294 x_return_status := fnd_api.g_ret_sts_success;
3295
3296 -- Update the Task Assignment if any columns are changing
3297 IF p_sched_travel_distance IS NOT NULL
3298 OR p_sched_travel_duration IS NOT NULL
3299 OR p_sched_travel_duration_uom IS NOT NULL
3300 THEN
3301 jtf_task_assignments_pub.update_task_assignment(
3302 p_api_version => 1.0
3303 , x_return_status => x_return_status
3304 , x_msg_count => x_msg_count
3305 , x_msg_data => x_msg_data
3306 , p_task_assignment_id => p_task_assignment_id
3307 , p_object_version_number => p_ta_object_version_number
3308 , p_sched_travel_distance => p_sched_travel_distance
3309 , p_sched_travel_duration => p_sched_travel_duration
3310 , p_sched_travel_duration_uom => p_sched_travel_duration_uom
3311 , p_enable_workflow => fnd_api.g_miss_char
3312 , p_abort_workflow => fnd_api.g_miss_char
3313 );
3314 IF x_return_status = fnd_api.g_ret_sts_error THEN
3315 RAISE fnd_api.g_exc_error;
3316 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3317 RAISE fnd_api.g_exc_unexpected_error;
3318 END IF;
3319 END IF;
3320
3321 -- Update the Task if any columns are changing
3322 IF p_scheduled_start_date IS NOT NULL OR p_scheduled_end_date IS NOT NULL THEN
3323 jtf_tasks_pub.update_task(
3324 p_api_version => 1.0
3325 , x_return_status => x_return_status
3326 , x_msg_count => x_msg_count
3327 , x_msg_data => x_msg_data
3331 , p_scheduled_end_date => p_scheduled_end_date
3328 , p_task_id => p_task_id
3329 , p_object_version_number => p_object_version_number
3330 , p_scheduled_start_date => p_scheduled_start_date
3332 , p_enable_workflow => fnd_api.g_miss_char
3333 , p_abort_workflow => fnd_api.g_miss_char
3334 );
3335
3336 IF x_return_status = fnd_api.g_ret_sts_error THEN
3337 RAISE fnd_api.g_exc_error;
3338 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3339 RAISE fnd_api.g_exc_unexpected_error;
3340 END IF;
3341 END IF;
3342
3343 IF fnd_api.to_boolean(p_commit) THEN
3344 COMMIT WORK;
3345 END IF;
3346 EXCEPTION
3347 WHEN fnd_api.g_exc_error THEN
3348 ROLLBACK TO csf_update_task_and_assignment;
3349 x_return_status := fnd_api.g_ret_sts_error;
3350 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3351 WHEN fnd_api.g_exc_unexpected_error THEN
3352 ROLLBACK TO csf_update_task_and_assignment;
3353 x_return_status := fnd_api.g_ret_sts_unexp_error;
3354 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3355 WHEN OTHERS THEN
3356 x_return_status := fnd_api.g_ret_sts_unexp_error;
3357 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3358 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3359 END IF;
3360 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3361 ROLLBACK TO csf_update_task_and_assignment;
3362 END update_task_and_assignment;
3363
3364 PROCEDURE update_task_longer_than_shift(
3365 p_api_version IN NUMBER
3366 , p_init_msg_list IN VARCHAR2
3367 , p_commit IN VARCHAR2
3368 , x_return_status OUT NOCOPY VARCHAR2
3369 , x_msg_count OUT NOCOPY NUMBER
3370 , x_msg_data OUT NOCOPY VARCHAR2
3371 , p_task_id IN NUMBER
3372 , p_object_version_number IN OUT NOCOPY NUMBER
3373 , p_planned_start_date IN DATE
3374 , p_planned_end_date IN DATE
3375 , p_action IN PLS_INTEGER
3376 , p_task_status_id IN NUMBER
3377 ) IS
3378 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TASK_LONGER_THAN_SHIFT';
3379 l_api_version CONSTANT NUMBER := 1.0;
3380
3381 CURSOR c_parent_task_info IS
3382 SELECT t.task_id
3383 , t.task_status_id
3384 , t.scheduled_start_date
3385 , t.scheduled_end_date
3386 , t.planned_effort
3387 , t.planned_effort_uom
3388 , t.task_split_flag
3389 FROM jtf_tasks_b t
3390 WHERE t.task_id = p_task_id;
3391
3392 CURSOR c_child_tasks IS
3393 SELECT t.task_id
3394 , t.object_version_number task_ovn
3395 , t.task_status_id
3396 , ta.task_assignment_id
3397 , ta.object_version_number task_assignment_ovn
3398 , ta.assignment_status_id
3399 FROM jtf_tasks_b t ,jtf_task_statuses_b ts ,jtf_task_assignments ta
3400 WHERE t.parent_task_id = p_task_id
3401 AND NVL(t.deleted_flag, 'N') <> 'Y'
3402 AND ts.task_status_id = t.task_status_id
3403 AND NVL(ts.cancelled_flag, 'N') <> 'Y'
3404 AND t.task_id = ta.task_id
3405 AND ta.assignment_status_id = ts.task_status_id;
3406
3407 l_parent_task_info c_parent_task_info%ROWTYPE;
3408 l_scheduled_start DATE;
3409 l_scheduled_end DATE;
3410 l_task_split_flag VARCHAR2(1);
3411 BEGIN
3412 SAVEPOINT update_task_longer_than_shift;
3413
3414 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3415 RAISE fnd_api.g_exc_unexpected_error;
3416 END IF;
3417
3418 IF fnd_api.to_boolean(p_init_msg_list) THEN
3419 fnd_msg_pub.initialize;
3420 END IF;
3421
3422 x_return_status := fnd_api.g_ret_sts_success;
3423
3424 -- Get the Task Information
3425 OPEN c_parent_task_info;
3426 FETCH c_parent_task_info INTO l_parent_task_info;
3427 CLOSE c_parent_task_info;
3428
3429 IF l_parent_task_info.task_id IS NULL THEN
3430 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'NO_DATA_FOUND JTF_TASKS_B.TASK_ID = ' || p_task_id);
3431 RAISE fnd_api.g_exc_unexpected_error;
3432 END IF;
3433
3434 -- Find out whether the new Task Status is valid.
3435 IF p_task_status_id <> fnd_api.g_miss_num THEN
3436 validate_status_change(l_parent_task_info.task_status_id, p_task_status_id);
3437 END IF;
3438
3439 IF p_action = g_action_normal_to_parent THEN
3440 -- Correct the Parent Task Information based on current Child Tasks
3441 sync_parent_with_child(
3442 p_api_version => 1.0
3443 , p_init_msg_list => fnd_api.g_false
3444 , p_commit => fnd_api.g_false
3445 , x_return_status => x_return_status
3446 , x_msg_count => x_msg_count
3447 , x_msg_data => x_msg_data
3448 , p_parent_task_id => p_task_id
3449 , p_parent_version_number => p_object_version_number
3450 , p_planned_start_date => p_planned_start_date
3451 , p_planned_end_date => p_planned_end_date
3452 );
3456 RAISE fnd_api.g_exc_unexpected_error;
3453 IF x_return_status = fnd_api.g_ret_sts_error THEN
3454 RAISE fnd_api.g_exc_error;
3455 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3457 END IF;
3458
3459 -- Correct the Child Task's Information
3460 sync_child_from_parent(
3461 p_api_version => 1.0
3462 , p_init_msg_list => fnd_api.g_false
3463 , p_commit => fnd_api.g_false
3464 , x_return_status => x_return_status
3465 , x_msg_count => x_msg_count
3466 , x_msg_data => x_msg_data
3467 , p_parent_task_id => p_task_id
3468 );
3469 IF x_return_status = fnd_api.g_ret_sts_error THEN
3470 RAISE fnd_api.g_exc_error;
3471 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3472 RAISE fnd_api.g_exc_unexpected_error;
3473 END IF;
3474 ELSIF p_action = g_action_parent_to_normal THEN
3475 jtf_tasks_pub.update_task(
3476 p_api_version => 1.0
3477 , x_return_status => x_return_status
3478 , x_msg_count => x_msg_count
3479 , x_msg_data => x_msg_data
3480 , p_task_id => p_task_id
3481 , p_object_version_number => p_object_version_number
3482 , p_task_status_id => p_task_status_id
3483 , p_scheduled_start_date => NULL
3484 , p_scheduled_end_date => NULL
3485 , p_task_split_flag => NULL
3486 , p_actual_start_date => NULL
3487 , p_actual_end_date => NULL
3488 , p_actual_effort => NULL
3489 , p_actual_effort_uom => NULL
3490 , p_enable_workflow => fnd_api.g_miss_char
3491 , p_abort_workflow => fnd_api.g_miss_char
3492 );
3493
3494 IF x_return_status = fnd_api.g_ret_sts_error THEN
3495 RAISE fnd_api.g_exc_error;
3496 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3497 RAISE fnd_api.g_exc_unexpected_error;
3498 END IF;
3499
3500 -- cancel all children if parent is changed back to normal.
3501 FOR child_task IN c_child_tasks LOOP
3502 unassign_task(
3503 p_api_version => 1.0
3504 , p_init_msg_list => fnd_api.g_false
3505 , p_commit => fnd_api.g_false
3506 , x_return_status => x_return_status
3507 , x_msg_count => x_msg_count
3508 , x_msg_data => x_msg_data
3509 , p_task_id => child_task.task_id
3510 , p_object_version_number => child_task.task_ovn
3511 , p_task_status_id => g_cancelled
3512 , p_task_assignment_id => child_task.task_assignment_id
3513 , p_ta_object_version_number => child_task.task_assignment_ovn
3514 , p_assignment_status_id => g_cancelled
3515 );
3516 IF x_return_status = fnd_api.g_ret_sts_error THEN
3517 RAISE fnd_api.g_exc_error;
3518 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3519 RAISE fnd_api.g_exc_unexpected_error;
3520 END IF;
3521 END LOOP;
3522 END IF;
3523
3524 IF fnd_api.to_boolean(p_commit) THEN
3525 COMMIT WORK;
3526 END IF;
3527 EXCEPTION
3528 WHEN fnd_api.g_exc_error THEN
3529 ROLLBACK TO update_task_longer_than_shift;
3530 x_return_status := fnd_api.g_ret_sts_error;
3531 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3532 WHEN fnd_api.g_exc_unexpected_error THEN
3533 ROLLBACK TO update_task_longer_than_shift;
3534 x_return_status := fnd_api.g_ret_sts_unexp_error;
3535 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3536 WHEN OTHERS THEN
3537 x_return_status := fnd_api.g_ret_sts_unexp_error;
3538 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3539 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3540 END IF;
3541 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3542 ROLLBACK TO update_task_longer_than_shift;
3543 END update_task_longer_than_shift;
3544
3545 PROCEDURE create_child_task(
3546 p_api_version IN NUMBER
3547 , p_init_msg_list IN VARCHAR2
3548 , p_commit IN VARCHAR2
3549 , x_return_status OUT NOCOPY VARCHAR2
3550 , x_msg_count OUT NOCOPY NUMBER
3551 , x_msg_data OUT NOCOPY VARCHAR2
3552 , p_parent_task_id IN NUMBER
3553 , p_task_status_id IN NUMBER
3554 , p_planned_effort IN NUMBER
3555 , p_planned_effort_uom IN VARCHAR2
3556 , p_bound_mode_code IN VARCHAR2
3557 , p_soft_bound_flag IN VARCHAR2
3558 , p_scheduled_start_date IN DATE
3559 , p_scheduled_end_date IN DATE
3560 , p_assignment_status_id IN NUMBER
3561 , p_resource_id IN NUMBER
3562 , p_resource_type IN VARCHAR2
3563 , p_object_capacity_id IN NUMBER
3564 , p_sched_travel_distance IN NUMBER
3568 , p_child_sequence_num IN NUMBER
3565 , p_sched_travel_duration IN NUMBER
3566 , p_sched_travel_duration_uom IN VARCHAR2
3567 , p_child_position IN VARCHAR2
3569 , x_task_id OUT NOCOPY NUMBER
3570 , x_object_version_number OUT NOCOPY NUMBER
3571 , x_task_assignment_id OUT NOCOPY NUMBER
3572 ) IS
3573 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_CHILD_TASK';
3574 l_api_version CONSTANT NUMBER := 1.0;
3575
3576 CURSOR c_parent_task_info IS
3577 SELECT t.task_name
3578 , t.description
3579 , t.task_type_id
3580 , t.task_priority_id
3581 , t.address_id
3582 , t.customer_id
3583 , t.source_object_type_code
3584 , t.source_object_id
3585 , t.source_object_name
3586 , t.owner_type_code
3587 , t.owner_id
3588 , t.task_confirmation_status
3589 , t.task_confirmation_counter
3590 , t.cust_account_id
3591 , t.planned_effort_uom
3592 FROM jtf_tasks_vl t
3593 WHERE t.task_id = p_parent_task_id;
3594
3595 l_parent_task_info c_parent_task_info%ROWTYPE;
3596 BEGIN
3597 SAVEPOINT csf_create_child_task;
3598
3599 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3600 RAISE fnd_api.g_exc_unexpected_error;
3601 END IF;
3602
3603 IF fnd_api.to_boolean(p_init_msg_list) THEN
3604 fnd_msg_pub.initialize;
3605 END IF;
3606
3607 x_return_status := fnd_api.g_ret_sts_success;
3608
3609 -- Get the Parent Task Information
3610 OPEN c_parent_task_info;
3611 FETCH c_parent_task_info INTO l_parent_task_info;
3612 IF c_parent_task_info%NOTFOUND THEN
3613 CLOSE c_parent_task_info;
3614 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'NO_DATA_FOUND JTF_TASKS_B.TASK_ID = ' || p_parent_task_id);
3615 RAISE fnd_api.g_exc_unexpected_error;
3616 END IF;
3617 CLOSE c_parent_task_info;
3618
3619 -- Create the Child Task using Parent Task Information
3620 -- (Set Zero Length Planned Window at Scheduled Start, Bound Mode code in BTS)
3621 jtf_tasks_pub.create_task(
3622 p_api_version => 1.0
3623 , p_init_msg_list => fnd_api.g_false
3624 , p_commit => fnd_api.g_false
3625 , x_return_status => x_return_status
3626 , x_msg_count => x_msg_count
3627 , x_msg_data => x_msg_data
3628 , p_task_name => l_parent_task_info.task_name
3629 , p_description => l_parent_task_info.description
3630 , p_task_type_id => l_parent_task_info.task_type_id
3631 , p_task_status_id => p_task_status_id
3632 , p_task_priority_id => l_parent_task_info.task_priority_id
3633 , p_owner_id => l_parent_task_info.owner_id
3634 , p_owner_type_code => l_parent_task_info.owner_type_code
3635 , p_customer_id => l_parent_task_info.customer_id
3636 , p_address_id => l_parent_task_info.address_id
3637 , p_planned_start_date => p_scheduled_start_date
3638 , p_planned_end_date => p_scheduled_start_date
3639 , p_scheduled_start_date => p_scheduled_start_date
3640 , p_scheduled_end_date => p_scheduled_end_date
3641 , p_source_object_type_code => l_parent_task_info.source_object_type_code
3642 , p_source_object_id => l_parent_task_info.source_object_id
3643 , p_source_object_name => l_parent_task_info.source_object_name
3644 , p_planned_effort => p_planned_effort
3645 , p_planned_effort_uom => p_planned_effort_uom
3646 , p_bound_mode_code => p_bound_mode_code
3647 , p_soft_bound_flag => p_soft_bound_flag
3648 , p_parent_task_id => p_parent_task_id
3649 , p_cust_account_id => l_parent_task_info.cust_account_id
3650 , p_enable_workflow => NULL
3651 , p_abort_workflow => NULL
3652 , p_task_split_flag => 'D'
3653 , p_child_position => NVL(p_child_position, 'N')
3654 , p_child_sequence_num => p_child_sequence_num
3655 , x_task_id => x_task_id
3656 );
3657 IF x_return_status = fnd_api.g_ret_sts_error THEN
3658 RAISE fnd_api.g_exc_error;
3659 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3660 RAISE fnd_api.g_exc_unexpected_error;
3661 END IF;
3662 x_object_version_number := 1;
3663
3664 -- Copy Task Confirmation Values.
3665 IF l_parent_task_info.task_confirmation_status = 'N' THEN
3666 NULL;
3667 -- JTF automatically creates Task with Confirmation Status as N and
3668 -- Counter as ZERO. Thus there is no need for another uncessary update.
3669 ELSIF l_parent_task_info.task_confirmation_status = 'R' THEN
3670 jtf_task_confirmation_pub.set_confirmation_required(
3671 p_api_version => 1.0
3672 , p_init_msg_list => fnd_api.g_false
3673 , p_commit => fnd_api.g_false
3674 , x_return_status => x_return_status
3675 , x_msg_count => x_msg_count
3679 );
3676 , x_msg_data => x_msg_data
3677 , p_task_id => x_task_id
3678 , p_object_version_number => x_object_version_number
3680 IF x_return_status = fnd_api.g_ret_sts_error THEN
3681 RAISE fnd_api.g_exc_error;
3682 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3683 RAISE fnd_api.g_exc_unexpected_error;
3684 END IF;
3685 ELSIF l_parent_task_info.task_confirmation_status = 'C' THEN
3686 jtf_task_confirmation_pub.set_confirmation_confirmed(
3687 p_api_version => 1.0
3688 , p_init_msg_list => fnd_api.g_false
3689 , p_commit => fnd_api.g_false
3690 , x_return_status => x_return_status
3691 , x_msg_count => x_msg_count
3692 , x_msg_data => x_msg_data
3693 , p_task_id => x_task_id
3694 , p_object_version_number => x_object_version_number
3695 );
3696 IF x_return_status = fnd_api.g_ret_sts_error THEN
3697 RAISE fnd_api.g_exc_error;
3698 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3699 RAISE fnd_api.g_exc_unexpected_error;
3700 END IF;
3701 IF l_parent_task_info.task_confirmation_counter > 0 THEN
3702 -- This is one horrible way of incrementing the counter. JTF has not given
3703 -- a API to set it directly. This way will increase the Object Version
3704 -- Number for each increase..
3705 FOR k IN 1 .. l_parent_task_info.task_confirmation_counter LOOP
3706 jtf_task_confirmation_pub.increase_counter(
3707 p_api_version => 1.0
3708 , p_init_msg_list => fnd_api.g_false
3709 , p_commit => fnd_api.g_false
3710 , x_return_status => x_return_status
3711 , x_msg_count => x_msg_count
3712 , x_msg_data => x_msg_data
3713 , p_task_id => x_task_id
3714 , p_object_version_number => x_object_version_number
3715 );
3716 IF x_return_status = fnd_api.g_ret_sts_error THEN
3717 RAISE fnd_api.g_exc_error;
3718 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3719 RAISE fnd_api.g_exc_unexpected_error;
3720 END IF;
3721 END LOOP;
3722 END IF;
3723 END IF;
3724
3725 -- Create the Task Assignment
3726 jtf_task_assignments_pub.create_task_assignment(
3727 p_api_version => 1.0
3728 , x_return_status => x_return_status
3729 , x_msg_count => x_msg_count
3730 , x_msg_data => x_msg_data
3731 , p_task_id => x_task_id
3732 , p_resource_id => p_resource_id
3733 , p_resource_type_code => p_resource_type
3734 , p_assignment_status_id => p_assignment_status_id
3735 , p_object_capacity_id => p_object_capacity_id
3736 , p_sched_travel_distance => p_sched_travel_distance
3737 , p_sched_travel_duration => p_sched_travel_duration
3738 , p_sched_travel_duration_uom => p_sched_travel_duration_uom
3739 , p_enable_workflow => NULL
3740 , p_abort_workflow => NULL
3741 , p_free_busy_type => NULL
3742 , x_task_assignment_id => x_task_assignment_id
3743 );
3744
3745 IF x_return_status = fnd_api.g_ret_sts_error THEN
3746 RAISE fnd_api.g_exc_error;
3747 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3748 RAISE fnd_api.g_exc_unexpected_error;
3749 END IF;
3750
3751 IF fnd_api.to_boolean(p_commit) THEN
3752 COMMIT WORK;
3753 END IF;
3754
3755 EXCEPTION
3756 WHEN fnd_api.g_exc_error THEN
3757 ROLLBACK TO csf_create_child_task;
3758 x_return_status := fnd_api.g_ret_sts_error;
3759 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3760 WHEN fnd_api.g_exc_unexpected_error THEN
3761 ROLLBACK TO csf_create_child_task;
3762 x_return_status := fnd_api.g_ret_sts_unexp_error;
3763 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3764 WHEN OTHERS THEN
3765 x_return_status := fnd_api.g_ret_sts_unexp_error;
3766 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3767 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3768 END IF;
3769 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3770 ROLLBACK TO csf_create_child_task;
3771 END create_child_task;
3772
3773 /**
3774 * Updates the customer confirmation for normal/child/parent task
3775 *
3776 * @param p_api_version API Version (1.0)
3777 * @param p_init_msg_list Initialize Message List
3778 * @param p_commit Commit the Work
3779 * @param x_return_status Return Status of the Procedure
3780 * @param x_msg_count Number of Messages in the Stack
3781 * @param x_msg_data Stack of Error Messages
3782 * @param p_task_id Task to be processed
3783 * @param p_object_version_number Object version of input task
3784 * @param p_action Whether Required/Received/Not Required
3788 PROCEDURE update_cust_confirmation(
3785 * @param p_initiated Whether Customer or Dispatcher
3786 */
3787
3789 p_api_version IN NUMBER
3790 , p_init_msg_list IN VARCHAR2
3791 , p_commit IN VARCHAR2
3792 , x_return_status OUT NOCOPY VARCHAR2
3793 , x_msg_count OUT NOCOPY NUMBER
3794 , x_msg_data OUT NOCOPY VARCHAR2
3795 , p_task_id IN NUMBER
3796 , p_object_version_number IN OUT NOCOPY NUMBER
3797 , p_action IN PLS_INTEGER
3798 , p_initiated IN PLS_INTEGER
3799 ) IS
3800 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CUST_CONFIRMATION';
3801 l_api_version CONSTANT NUMBER := 1.0;
3802 i PLS_INTEGER := 1;
3803
3804 CURSOR c_task_info (p_task_id NUMBER) IS
3805 SELECT t.task_id
3806 , t.task_split_flag
3807 , t.parent_task_id
3808 , t.task_confirmation_status
3809 FROM jtf_tasks_b t
3810 WHERE t.task_id = p_task_id;
3811
3812 CURSOR c_parent_child_tasks (p_task_id NUMBER) IS
3813 SELECT jtb.task_id
3814 , jtb.object_version_number
3815 FROM jtf_task_statuses_vl ts, jtf_tasks_b jtb
3816 WHERE jtb.parent_task_id = p_task_id
3817 AND ts.task_status_id = jtb.task_status_id
3818 AND jtb.task_split_flag = 'D'
3819 AND ( NVL(ts.on_hold_flag, 'N') = 'Y'
3820 OR NVL(ts.working_flag, 'N') = 'Y'
3821 OR NVL(ts.schedulable_flag, 'N') = 'Y'
3822 OR ( NVL(ts.assigned_flag, 'N') = 'Y'
3823 AND NVL(ts.closed_flag, 'N') <> 'Y'
3824 AND NVL(ts.approved_flag, 'N') <> 'Y'
3825 AND NVL(ts.completed_flag, 'N') <> 'Y'
3826 AND NVL(ts.rejected_flag, 'N') <> 'Y' ))
3827 UNION
3828 SELECT t.task_id
3829 , t.object_version_number
3830 FROM jtf_tasks_b t
3831 WHERE task_id = p_task_id;
3832
3833 l_cust_task_tbl jtf_number_table := jtf_number_table();
3834 l_cust_objver_tbl jtf_number_table := jtf_number_table();
3835 l_task_info c_task_info%ROWTYPE;
3836 BEGIN
3837 SAVEPOINT csf_update_cust_confirmation;
3838
3839 x_return_status := fnd_api.g_ret_sts_success;
3840
3841 IF fnd_api.to_boolean (p_init_msg_list) THEN
3842 fnd_msg_pub.initialize;
3843 END IF;
3844
3845 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3846 RAISE fnd_api.g_exc_unexpected_error;
3847 END IF;
3848
3849 OPEN c_task_info(p_task_id);
3850 FETCH c_task_info INTO l_task_info;
3851 CLOSE c_task_info;
3852
3853 IF ( l_task_info.task_split_flag IS NULL ) THEN
3854 l_cust_task_tbl.extend();
3855 l_cust_objver_tbl.extend();
3856 l_cust_task_tbl(l_cust_task_tbl.last) := p_task_id;
3857 l_cust_objver_tbl(l_cust_objver_tbl.last) := p_object_version_number;
3858 ELSIF ( l_task_info.task_split_flag = 'M' ) THEN
3859 OPEN c_parent_child_tasks(l_task_info.task_id);
3860 FETCH c_parent_child_tasks BULK COLLECT INTO l_cust_task_tbl, l_cust_objver_tbl;
3861 CLOSE c_parent_child_tasks;
3862 ELSIF ( l_task_info.task_split_flag = 'D' ) THEN
3863 OPEN c_parent_child_tasks(l_task_info.parent_task_id);
3864 FETCH c_parent_child_tasks BULK COLLECT INTO l_cust_task_tbl, l_cust_objver_tbl;
3865 CLOSE c_parent_child_tasks;
3866 END IF;
3867
3868 i:= l_cust_task_tbl.first;
3869 WHILE i IS NOT null
3870 LOOP
3871 IF p_action = csf_tasks_pub.g_action_conf_to_received THEN
3872 jtf_task_confirmation_pub.set_confirmation_confirmed(
3873 p_api_version => 1.0
3874 , p_init_msg_list => fnd_api.g_false
3875 , p_commit => fnd_api.g_false
3876 , x_return_status => x_return_status
3877 , x_msg_count => x_msg_count
3878 , x_msg_data => x_msg_data
3879 , p_task_id => l_cust_task_tbl(i)
3880 , p_object_version_number => l_cust_objver_tbl(i)
3881 );
3882 ELSIF p_action = csf_tasks_pub.g_action_conf_to_required THEN
3883 jtf_task_confirmation_pub.set_confirmation_required(
3884 p_api_version => 1.0
3885 , p_init_msg_list => fnd_api.g_false
3886 , p_commit => fnd_api.g_false
3887 , x_return_status => x_return_status
3888 , x_msg_count => x_msg_count
3889 , x_msg_data => x_msg_data
3890 , p_task_id => l_cust_task_tbl(i)
3891 , p_object_version_number => l_cust_objver_tbl(i)
3892 );
3893 IF x_return_status = fnd_api.g_ret_sts_success THEN
3894 IF l_task_info.task_confirmation_status = 'C' THEN
3895 IF p_initiated = csf_tasks_pub.g_dispatcher_initiated THEN
3896 jtf_task_confirmation_pub.increase_counter(
3897 p_api_version => 1.0
3898 , p_init_msg_list => fnd_api.g_false
3899 , p_commit => fnd_api.g_false
3903 , p_task_id => l_cust_task_tbl(i)
3900 , x_return_status => x_return_status
3901 , x_msg_count => x_msg_count
3902 , x_msg_data => x_msg_data
3904 , p_object_version_number => l_cust_objver_tbl(i)
3905 );
3906 ELSIF p_initiated = csf_tasks_pub.g_customer_initiated THEN
3907 jtf_task_confirmation_pub.reset_counter(
3908 p_api_version => 1.0
3909 , p_commit => fnd_api.g_false
3910 , p_init_msg_list => fnd_api.g_false
3911 , p_object_version_number => l_cust_objver_tbl(i)
3912 , p_task_id => l_cust_task_tbl(i)
3913 , x_return_status => x_return_status
3914 , x_msg_count => x_msg_count
3915 , x_msg_data => x_msg_data
3916 );
3917 END IF;
3918 END IF;
3919 END IF;
3920 ELSIF p_action = csf_tasks_pub.g_action_conf_not_required THEN
3921 jtf_task_confirmation_pub.reset_confirmation_status(
3922 p_api_version => 1.0
3923 , p_init_msg_list => fnd_api.g_false
3924 , p_commit => fnd_api.g_false
3925 , x_return_status => x_return_status
3926 , x_msg_count => x_msg_count
3927 , x_msg_data => x_msg_data
3928 , p_task_id => l_cust_task_tbl(i)
3929 , p_object_version_number => l_cust_objver_tbl(i)
3930 );
3931 END IF;
3932
3933 IF x_return_status = fnd_api.g_ret_sts_error THEN
3934 RAISE fnd_api.g_exc_error;
3935 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3936 RAISE fnd_api.g_exc_unexpected_error;
3937 END IF;
3938
3939 IF ( p_task_id = l_cust_task_tbl(i) ) THEN
3940 p_object_version_number := l_cust_objver_tbl(i);
3941 END IF;
3942
3943 i := l_cust_task_tbl.next(i);
3944 END LOOP;
3945
3946 -- Standard check of p_commit
3947 IF fnd_api.to_boolean (p_commit) THEN
3948 COMMIT WORK;
3949 END IF;
3950
3951 EXCEPTION
3952 WHEN fnd_api.g_exc_error THEN
3953 ROLLBACK TO csf_update_cust_confirmation;
3954 x_return_status := fnd_api.g_ret_sts_error;
3955 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3956 WHEN fnd_api.g_exc_unexpected_error THEN
3957 ROLLBACK TO csf_update_cust_confirmation;
3958 x_return_status := fnd_api.g_ret_sts_unexp_error;
3959 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3960 WHEN OTHERS THEN
3961 x_return_status := fnd_api.g_ret_sts_unexp_error;
3962 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3963 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3964 END IF;
3965 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3966 ROLLBACK TO csf_update_cust_confirmation;
3967 END update_cust_confirmation;
3968
3969 FUNCTION get_task_location_id (
3970 p_task_id IN NUMBER
3971 , p_party_site_id IN NUMBER
3972 , p_location_id IN NUMBER
3973 ) RETURN NUMBER
3974 IS
3975 l_location_id NUMBER;
3976
3977 CURSOR c_ps_location IS
3978 SELECT ps.location_id
3979 FROM hz_party_sites ps
3980 WHERE ps.party_site_id = p_party_site_id;
3981
3982 CURSOR c_task_location IS
3983 SELECT NVL(t.location_id, ps.location_id)
3984 FROM jtf_tasks_b t
3985 , hz_party_sites ps
3986 WHERE t.task_id = p_task_id
3987 AND ps.party_site_id(+) = t.address_id;
3988
3989 BEGIN
3990 IF p_location_id IS NOT NULL THEN
3991 l_location_id := p_location_id;
3992 ELSIF p_party_site_id IS NOT NULL THEN
3993 OPEN c_ps_location;
3994 FETCH c_ps_location INTO l_location_id;
3995 CLOSE c_ps_location;
3996 ELSE
3997 OPEN c_task_location;
3998 FETCH c_task_location INTO l_location_id;
3999 CLOSE c_task_location;
4000 END IF;
4001
4002 RETURN l_location_id;
4003 EXCEPTION
4004 WHEN OTHERS THEN
4005 RETURN -1;
4006 END get_task_location_id;
4007
4008 FUNCTION get_task_address (
4009 p_task_id IN NUMBER
4010 , p_party_site_id IN NUMBER
4011 , p_location_id IN NUMBER
4012 , p_short_flag IN VARCHAR2
4013 ) RETURN VARCHAR2
4014 IS
4015 CURSOR c_location_info IS
4016 SELECT l.address1
4017 , l.address2
4018 , l.address3
4019 , l.address4
4020 , l.postal_code
4021 , l.city
4022 , l.state
4023 , l.province
4024 , l.country
4025 FROM hz_locations l
4026 WHERE l.location_id = p_location_id;
4027
4028 CURSOR c_ps_location_info IS
4029 SELECT l.address1
4030 , l.address2
4031 , l.address3
4032 , l.address4
4033 , l.postal_code
4034 , l.city
4035 , l.state
4036 , l.province
4037 , l.country
4038 FROM hz_party_sites ps
4042
4039 , hz_locations l
4040 WHERE ps.party_site_id = p_party_site_id
4041 AND l.location_id = ps.location_id;
4043 CURSOR c_task_location_info IS
4044 SELECT l.address1
4045 , l.address2
4046 , l.address3
4047 , l.address4
4048 , l.postal_code
4049 , l.city
4050 , l.state
4051 , l.province
4052 , l.country
4053 FROM jtf_tasks_b t
4054 , hz_party_sites ps
4055 , hz_locations l
4056 WHERE t.task_id = p_task_id
4057 AND ps.party_site_id(+) = t.address_id
4058 AND l.location_id = NVL(t.location_id, ps.location_id);
4059
4060 l_address VARCHAR2(1300);
4061 l_location_rec c_location_info%ROWTYPE;
4062 BEGIN
4063 IF p_location_id IS NOT NULL THEN
4064 OPEN c_location_info;
4065 FETCH c_location_info INTO l_location_rec;
4066 CLOSE c_location_info;
4067 ELSIF p_party_site_id IS NOT NULL THEN
4068 OPEN c_ps_location_info;
4069 FETCH c_ps_location_info INTO l_location_rec;
4070 CLOSE c_ps_location_info;
4071 ELSE
4072 OPEN c_task_location_info;
4073 FETCH c_task_location_info INTO l_location_rec;
4074 CLOSE c_task_location_info;
4075 END IF;
4076
4077 IF p_short_flag = 'Y' THEN
4078 IF l_location_rec.postal_code IS NOT NULL THEN
4079 l_address := l_location_rec.postal_code;
4080 ELSE
4081 l_address := l_location_rec.address1;
4082 END IF;
4083
4084 IF l_location_rec.city IS NOT NULL THEN
4085 l_address := l_address || ',' || l_location_rec.city;
4086 END IF;
4087
4088 IF l_location_rec.state IS NOT NULL THEN
4089 l_address := l_address || ',' || l_location_rec.state;
4090 ELSIF l_location_rec.province IS NOT NULL THEN
4091 l_address := l_address || ',' || l_location_rec.province;
4092 END IF;
4093 ELSE
4094 l_address := l_location_rec.address1;
4095 IF l_location_rec.address2 IS NOT NULL THEN
4096 l_address := l_address || ',' || l_location_rec.address2;
4097 END IF;
4098
4099 IF l_location_rec.address3 IS NOT NULL THEN
4100 l_address := l_address || ',' || l_location_rec.address3;
4101 END IF;
4102
4103 IF l_location_rec.address4 IS NOT NULL THEN
4104 l_address := l_address || ',' || l_location_rec.address4;
4105 END IF;
4106
4107 IF l_location_rec.postal_code IS NOT NULL THEN
4108 l_address := l_address || ',' || l_location_rec.postal_code;
4109 END IF;
4110
4111 IF l_location_rec.city IS NOT NULL THEN
4112 l_address := l_address || ',' || l_location_rec.city;
4113 END IF;
4114
4115 IF l_location_rec.state IS NOT NULL THEN
4116 l_address := l_address || ',' || l_location_rec.state;
4117 ELSIF l_location_rec.province IS NOT NULL THEN
4118 l_address := l_address || ',' || l_location_rec.province;
4119 END IF;
4120
4121 l_address := l_address || ',' || l_location_rec.country;
4122 END IF;
4123
4124 RETURN l_address;
4125 EXCEPTION
4126 WHEN OTHERS THEN
4127 RETURN NULL;
4128 END get_task_address;
4129
4130 /**
4131 * Gets the Task Effort conditionally converted to the Default UOM as given by
4132 * the profile CSF: Default Effort UOM by calling
4133 * CSF_UTIL_PVT.GET_EFFORT_IN_DEFAULT_UOM function.
4134 * <br>
4135 * All parameters are optional. If Planned Effort, Planned Effort UOM and Task
4136 * Split Flag are passed, then it helps in better performance as JTF_TASKS_B
4137 * wont be queried to get those information. In case of better flexibility,
4138 * the caller can just pass the Task ID and the API will fetch the required
4139 * information. If case none of the required parameters are passed, the API returns
4140 * NULL.
4141 * <br>
4142 * Parent Task / Normal Tasks are created by the Teleservice Operators and therefore
4143 * its always better to represent them in the UOM they had given initially. Tasks
4144 * created as part of the Background processes like Child Tasks are always created
4145 * in Minutes by Scheduler and therefore it is incumbent upon us to represent
4146 * them in a proper UOM. Thus this API will convert the Planned Effort to the default
4147 * UOM only for Child Tasks and will merely act as a Concatenation Operator for
4148 * other Tasks. If you want to overrule this and want conversion to Default UOM
4149 * to take place for all Tasks, pass p_always_convert as FND_API.G_TRUE
4150 *
4151 * Also refer to the documentation on CSF_UTIL_PVT.GET_EFFORT_IN_DEFAULT_UOM.
4152 * <br>
4153 *
4154 * @param p_planned_effort Planned Effort to be converted
4155 * @param p_planned_effort_uom UOM of the above Effort
4156 * @param p_task_split_flag Determines whether the Task is Child / Other
4157 * @param p_task_id Task ID of the Task whose effort is to be converted
4158 * @param p_always_convert Overrule the condition and convert for all Tasks.
4159 *
4160 * @result Planned Effort appro converted to Default UOM.
4161 */
4162 FUNCTION get_task_effort_in_default_uom(
4163 p_planned_effort NUMBER
4164 , p_planned_effort_uom VARCHAR2
4168 )
4165 , p_task_split_flag VARCHAR2
4166 , p_task_id NUMBER
4167 , p_always_convert VARCHAR2
4169 RETURN VARCHAR2 IS
4170
4171 l_effort NUMBER;
4172 l_effort_uom jtf_tasks_b.planned_effort_uom%TYPE;
4173 l_task_split_flag jtf_tasks_b.task_split_flag%TYPE;
4174
4175 CURSOR c_task_info IS
4176 SELECT NVL(p_planned_effort, planned_effort) planned_effort
4177 , NVL(p_planned_effort_uom, planned_effort_uom) planned_effort_uom
4178 , decode(p_task_split_flag, '@', task_split_flag, p_task_split_flag) task_split_flag
4179 FROM jtf_tasks_b
4180 WHERE task_id = p_task_id;
4181 BEGIN
4182 l_effort := p_planned_effort;
4183 l_effort_uom := p_planned_effort_uom;
4184 l_task_split_flag := p_task_split_flag;
4185
4186 IF l_effort IS NULL
4187 OR l_effort_uom IS NULL
4188 OR ( l_task_split_flag = '@' AND NVL(p_always_convert, fnd_api.g_false) = fnd_api.g_false)
4189 THEN
4190 IF p_task_id IS NOT NULL THEN
4191 OPEN c_task_info;
4192 FETCH c_task_info INTO l_effort, l_effort_uom, l_task_split_flag;
4193 CLOSE c_task_info;
4194 END IF;
4195 END IF;
4196
4197 IF l_effort IS NULL OR l_effort_uom IS NULL THEN
4198 RETURN NULL;
4199 END IF;
4200
4201 IF NVL(l_task_split_flag, 'M') IN ('M', '@')
4202 AND NVL(p_always_convert, fnd_api.g_false) = fnd_api.g_false
4203 THEN
4204 RETURN l_effort || ' ' || csf_util_pvt.get_uom(l_effort_uom);
4205 END IF;
4206
4207 RETURN csf_util_pvt.get_effort_in_default_uom(l_effort, l_effort_uom);
4208 END get_task_effort_in_default_uom;
4209
4210 PROCEDURE get_contact_details(
4211 p_incident_id IN NUMBER
4212 , p_task_id IN NUMBER
4213 , x_last_name OUT NOCOPY VARCHAR2
4214 , x_first_name OUT NOCOPY VARCHAR2
4215 , x_title OUT NOCOPY VARCHAR2
4216 , x_phone OUT NOCOPY VARCHAR2
4217 , x_phone_ext OUT NOCOPY VARCHAR2
4218 , x_email_address OUT NOCOPY VARCHAR2
4219 ) IS
4220 l_contact_source CONSTANT VARCHAR2(10) := fnd_profile.value('CSF_DFLT_SOURCE_FOR_CONTACT');
4221
4222 l_contact_type cs_sr_contact_points_v.contact_type%TYPE;
4223 l_contact_point_id cs_sr_contact_points_v.contact_point_id%TYPE;
4224 l_party_id cs_sr_contact_points_v.party_id%TYPE;
4225
4226 -- Cursor to fetch the Task Contact Points
4227 CURSOR c_task_contact_points IS
4228 SELECT pc.person_last_name last_name
4229 , pc.person_first_name first_name
4230 , pc.person_title title
4231 , tp.phone_id
4232 FROM jtf_task_contacts tc
4233 , jtf_party_all_contacts_v pc
4234 , jtf_task_phones_v tp
4235 WHERE tc.task_id = p_task_id
4236 AND tc.contact_id IN (pc.party_id, pc.subject_party_id)
4237 AND tp.task_contact_id (+) = tc.task_contact_id;
4238
4239 -- Cursor to fetch the Service Request Contact Points
4240 CURSOR c_sr_contact_points IS
4241 SELECT sub_last_name last_name
4242 , sub_first_name first_name
4243 , sub_title title
4244 , contact_point_id
4245 , party_id
4246 , contact_type
4247 FROM cs_sr_contact_points_v
4248 WHERE incident_id = p_incident_id
4249 AND primary_flag = 'Y';
4250
4251 -- Cursor to fetch the Phone Number of Contacts
4252 CURSOR c_contact_phone IS
4253 SELECT cp.contact_point_type
4254 , DECODE(cp.phone_country_code, '', '', NULL, '', cp.phone_country_code || '-' )
4255 || DECODE(cp.phone_area_code, '', '', NULL, '', cp.phone_area_code || '-')
4256 || cp.phone_number phone
4257 , cp.phone_extension
4258 , cp.email_address
4259 FROM hz_contact_points cp
4260 , ar_lookups ar
4261 WHERE cp.contact_point_id = l_contact_point_id
4262 AND cp.contact_point_type IN ('EMAIL', 'PHONE')
4263 AND cp.phone_line_type = ar.lookup_code (+)
4264 AND ar.lookup_type(+) = 'PHONE_LINE_TYPE';
4265
4266 -- Cursor to fetch information regarding HRMS Employees
4267 -- We require joining again with cs_hz_sr_contact_points so that the OUTER
4268 -- Join on Phone ID works properly. If its a constant, it expects a NOT NULL
4269 -- Value.
4270 CURSOR c_emp_info IS
4271 SELECT p.last_name
4272 , p.first_name
4273 , p.title
4274 , pp.phone_number
4275 , p.email_address
4276 FROM cs_hz_sr_contact_points sr_cp
4277 , per_all_people_f p
4278 , per_phones pp
4279 , hr_lookups hrl
4280 WHERE sr_cp.incident_id = p_incident_id
4281 AND sr_cp.primary_flag = 'Y'
4282 AND p.person_id = sr_cp.party_id
4283 AND pp.phone_id(+) = sr_cp.contact_point_id
4284 AND pp.parent_table(+) = 'PER_ALL_PEOPLE_F'
4285 AND hrl.lookup_code(+) = pp.phone_type
4286 AND hrl.lookup_type(+) = 'PHONE_TYPE'
4287 ORDER BY p.effective_end_date desc;
4288
4289
4290 BEGIN
4291
4292 IF l_contact_source = 'TASK' THEN
4293 -- Fetch the Contact Points from the Task Data Model
4294 OPEN c_task_contact_points;
4295 FETCH c_task_contact_points INTO x_last_name, x_first_name, x_title, l_contact_point_id;
4296 CLOSE c_task_contact_points;
4297
4298 FOR v IN c_contact_phone LOOP
4299 IF v.contact_point_type = 'EMAIL' THEN
4300 x_email_address := v.email_address;
4301 ELSE
4302 x_phone := v.phone;
4303 x_phone_ext := v.phone_extension;
4304 END IF;
4305 END LOOP;
4306 ELSE
4307 -- Fetch the Contact Points from the SR Data Model
4308 OPEN c_sr_contact_points;
4309 FETCH c_sr_contact_points INTO x_last_name, x_first_name, x_title, l_contact_point_id, l_party_id, l_contact_type;
4310 CLOSE c_sr_contact_points;
4311
4312 IF l_contact_type = 'EMPLOYEE' THEN
4313 OPEN c_emp_info;
4314 FETCH c_emp_info INTO x_last_name, x_first_name, x_title, x_phone, x_email_address;
4315 CLOSE c_emp_info;
4316 ELSE
4317 FOR v IN c_contact_phone LOOP
4318 IF v.contact_point_type = 'EMAIL' THEN
4319 x_email_address := v.email_address;
4320 ELSE
4321 x_phone := v.phone;
4322 x_phone_ext := v.phone_extension;
4323 END IF;
4324 END LOOP;
4325 END IF;
4326 END IF;
4327
4328 END get_contact_details;
4329
4330 FUNCTION get_contact_details(p_incident_id NUMBER, p_task_id NUMBER)
4331 RETURN VARCHAR2 IS
4332 l_title VARCHAR2(60);
4333 l_first_name VARCHAR2(150);
4334 l_last_name VARCHAR2(150);
4335 l_phone VARCHAR2(50);
4336 l_extension VARCHAR2(20);
4337 l_email_address VARCHAR2(2000);
4338
4339 l_name VARCHAR2(500);
4340 BEGIN
4341 get_contact_details(
4342 p_incident_id => p_incident_id
4343 , p_task_id => p_task_id
4344 , x_last_name => l_last_name
4345 , x_first_name => l_first_name
4346 , x_title => l_title
4347 , x_phone => l_phone
4348 , x_phone_ext => l_extension
4349 , x_email_address => l_email_address
4350 );
4351
4352 l_name := '';
4353 IF l_title IS NOT NULL THEN
4354 l_name := l_title || ' ';
4355 END IF;
4356 IF l_first_name IS NOT NULL THEN
4357 l_name := l_name || l_first_name || ' ';
4358 END IF;
4359 IF l_last_name IS NOT NULL THEN
4360 l_name := l_name || l_last_name;
4361 END IF;
4362
4363 RETURN l_name || '@@' || l_phone || '@@' || l_extension || '@@' || l_email_address;
4364 END get_contact_details;
4365
4366
4367 procedure create_personal_task(
4368 p_api_version in number
4369 , p_init_msg_list in varchar2
4370 , p_commit in varchar2
4371 , p_task_name in varchar2
4372 , p_description in varchar2
4373 , p_task_type_name in varchar2
4374 , p_task_type_id in number
4375 , p_task_status_name in varchar2
4376 , p_task_status_id in number
4377 , p_task_priority_name in varchar2
4378 , p_task_priority_id in number
4379 , p_owner_id in number
4380 , p_owner_type_code in varchar2
4381 , p_address_id in number
4382 , p_customer_id in number
4383 , p_planned_start_date in date
4384 , p_planned_end_date in date
4385 , p_scheduled_start_date in date
4386 , p_scheduled_end_date in date
4387 , p_source_object_type_code in varchar2
4388 , p_planned_effort in number
4389 , p_planned_effort_uom in varchar2
4390 , p_bound_mode_code in varchar2
4391 , p_soft_bound_flag in varchar2
4392 , p_task_assign_tbl jtf_tasks_pub.task_assign_tbl
4393 , p_type in varchar2
4397 , x_msg_data out nocopy varchar2
4394 , p_trip in number
4395 , x_return_status out nocopy varchar2
4396 , x_msg_count out nocopy number
4398 , x_task_id out nocopy number
4399 )
4400 is
4401 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_PERSONAL_TASK';
4402 l_api_version CONSTANT NUMBER := 1.0;
4403
4404 l_location number;
4405 l_obj number;
4406 x_object_version_number number;
4407 l_task_id number;
4408 l_task_assignment_id number;
4409 l_obj_number number;
4410 l_task_ovn number;
4411 l_ts number;
4412
4413 begin
4414 SAVEPOINT csf_create_per_task;
4415
4416 x_return_status := fnd_api.g_ret_sts_success;
4417
4418 IF fnd_api.to_boolean (p_init_msg_list) THEN
4419 fnd_msg_pub.initialize;
4420 END IF;
4421
4422 if p_type = 'ONETIME'
4423 then
4424 l_location := p_address_id;
4425 end if;
4426
4427 csf_tasks_pub.create_task(
4428 p_api_version => p_api_version
4429 , p_init_msg_list => p_init_msg_list
4430 , p_commit => p_commit
4431 , p_task_name => p_task_name
4432 , p_description => p_description
4433 , p_task_type_name => p_task_type_name
4434 , p_task_type_id => p_task_type_id
4435 , p_task_status_name => p_task_status_name
4436 , p_task_status_id => p_task_status_id
4437 , p_task_priority_name => p_task_priority_name
4438 , p_task_priority_id => p_task_priority_id
4439 , p_owner_id => p_owner_id
4440 , p_owner_type_code => p_owner_type_code
4441 , p_address_id => p_address_id
4442 , p_customer_id => p_customer_id
4443 , p_location_id => l_location
4444 , p_planned_start_date => p_planned_start_date
4445 , p_planned_end_date => p_planned_end_date
4446 , p_scheduled_start_date => p_scheduled_start_date
4447 , p_scheduled_end_date => p_scheduled_end_date
4448 , p_source_object_type_code => p_source_object_type_code
4449 , p_planned_effort => p_planned_effort
4450 , p_planned_effort_uom => p_planned_effort_uom
4451 , p_bound_mode_code => p_bound_mode_code
4452 , p_soft_bound_flag => p_soft_bound_flag
4453 , p_task_assign_tbl => p_task_assign_tbl
4454 , x_return_status => x_return_status
4455 , x_msg_count => x_msg_count
4456 , x_msg_data => x_msg_data
4457 , x_task_id => x_task_id
4458 );
4459 IF x_return_status = fnd_api.g_ret_sts_error THEN
4460 RAISE fnd_api.g_exc_error;
4461 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4462 RAISE fnd_api.g_exc_unexpected_error;
4463 END IF;
4464
4465 l_task_id := x_task_id;
4466
4467 csf_task_assignments_pub.create_task_assignment(
4468 p_api_version => p_api_version
4469 , p_init_msg_list =>p_init_msg_list
4470 , p_commit =>p_commit
4471 , x_return_status =>x_return_status
4472 , x_msg_count =>x_msg_count
4473 , x_msg_data =>x_msg_data
4474 , p_task_id => l_task_id
4475 , p_task_name =>p_task_name
4476 , p_resource_type_code => p_owner_type_code
4477 , p_resource_id =>p_owner_id
4478 , p_actual_start_date => p_scheduled_start_date
4479 , p_actual_end_date => p_scheduled_end_date
4480 , p_assignment_status_id=>p_task_status_id
4481 , p_object_capacity_id => p_trip
4482 , x_task_assignment_id => l_task_assignment_id
4483 , x_ta_object_version_number =>l_obj_number
4484 , x_task_object_version_number => l_task_ovn
4485 , x_task_status_id => l_ts);
4486
4487 IF x_return_status = fnd_api.g_ret_sts_error THEN
4488 RAISE fnd_api.g_exc_error;
4489 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4490 RAISE fnd_api.g_exc_unexpected_error;
4491 END IF;
4492
4493 IF fnd_api.to_boolean (p_commit) THEN
4494 COMMIT WORK;
4495 END IF;
4496
4497 Exception
4498 WHEN fnd_api.g_exc_error THEN
4499 ROLLBACK TO csf_create_per_task;
4500 x_return_status := fnd_api.g_ret_sts_error;
4501 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4502 WHEN fnd_api.g_exc_unexpected_error THEN
4503 ROLLBACK TO csf_create_per_task;
4504 x_return_status := fnd_api.g_ret_sts_unexp_error;
4505 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4506 WHEN OTHERS THEN
4507 x_return_status := fnd_api.g_ret_sts_unexp_error;
4508 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4509 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4510 END IF;
4511 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4512 ROLLBACK TO csf_create_per_task;
4513 end;
4514
4515 procedure update_personal_task(
4516 p_api_version in number
4517 , p_init_msg_list in varchar2
4521 , x_version in out nocopy number
4518 , p_commit in varchar2
4519 , p_task_id in varchar2
4520 , p_task_name in varchar2
4522 , p_description in number
4523 , p_task_type_id in number
4524 , p_task_status_id in number
4525 , p_task_priority_id in number
4526 , p_owner_id in number
4527 , p_owner_type_code in varchar2
4528 , p_address_id in number
4529 , p_customer_id in number
4530 , p_planned_start_date in date
4531 , p_planned_end_date in date
4532 , p_scheduled_start_date in date
4533 , p_scheduled_end_date in date
4534 , p_source_object_type_code in varchar2
4535 , p_planned_effort in number
4536 , p_planned_effort_uom in varchar2
4537 , p_bound_mode_code in varchar2
4538 , p_soft_bound_flag in varchar2
4539 , p_type in varchar2
4540 , p_trip in number
4541 , x_return_status out nocopy varchar2
4542 , x_msg_count out nocopy number
4543 , x_msg_data out nocopy varchar2
4544 )
4545 is
4546 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PERSONAL_TASK';
4547 l_api_version CONSTANT NUMBER := 1.0;
4548
4549 cursor c_obj(p_task number)
4550 is
4551 select object_version_number
4552 from jtf_tasks_b
4553 where task_id =p_task;
4554
4555 cursor c_task_ass(p_task_id number)
4556 is
4557 select jta.task_assignment_id
4558 from jtf_task_assignments jta,jtf_tasks_b jt
4559 where jt.task_id=p_task_id
4560 and jta.task_id=jt.task_id
4561 and jt.source_object_type_code = 'TASK'
4562 and jt.task_type_id not in (20,21);
4563
4564 l_location number;
4565 l_obj number;
4566 x_object_version_number number;
4567 l_obj_task number;
4568 l_ts number;
4569 l_task_assignment_id number;
4570
4571
4572 begin
4573
4574 SAVEPOINT csf_update_per_task;
4575
4576 x_return_status := fnd_api.g_ret_sts_success;
4577
4578 IF fnd_api.to_boolean (p_init_msg_list) THEN
4579 fnd_msg_pub.initialize;
4580 END IF;
4581
4582 open c_obj(p_task_id);
4583 fetch c_obj into l_obj;
4584 close c_obj;
4585
4586 if p_type = 'ONETIME'
4587 then
4588 l_location := p_address_id;
4589 end if;
4590
4591 csf_tasks_pub.update_task(
4592 p_api_version => p_api_version
4593 , p_init_msg_list => p_init_msg_list
4594 , p_commit => p_commit
4595 ,p_task_id => p_task_id
4596 ,p_object_version_number => x_version
4597 , p_task_name => p_task_name
4598 , p_description => p_description
4599 , p_task_type_id => p_task_type_id
4600 , p_task_status_id => p_task_status_id
4601 , p_task_priority_id => p_task_priority_id
4602 , p_owner_id => p_owner_id
4603 , p_owner_type_code => p_owner_type_code
4604 , p_address_id => p_address_id
4605 , p_customer_id => p_customer_id
4606 , p_location_id => l_location
4607 , p_planned_start_date => p_planned_start_date
4608 , p_planned_end_date => p_planned_end_date
4609 , p_scheduled_start_date => p_scheduled_start_date
4610 , p_scheduled_end_date => p_scheduled_end_date
4611 , p_source_object_type_code => p_source_object_type_code
4612 , p_planned_effort => p_planned_effort
4613 , p_planned_effort_uom => p_planned_effort_uom
4614 , p_bound_mode_code => p_bound_mode_code
4615 , p_soft_bound_flag => p_soft_bound_flag
4616 , x_return_status => x_return_status
4617 , x_msg_count => x_msg_count
4618 , x_msg_data => x_msg_data
4619 );
4620 IF x_return_status = fnd_api.g_ret_sts_error THEN
4621 RAISE fnd_api.g_exc_error;
4622 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4623 RAISE fnd_api.g_exc_unexpected_error;
4624 END IF;
4625
4626 open c_task_ass(p_task_id);
4627 fetch c_task_ass into l_task_assignment_id;
4628 close c_task_ass;
4629
4630
4631 csf_task_assignments_pub.update_task_assignment(
4632 p_api_version =>p_api_version
4633 , p_init_msg_list => p_init_msg_list
4634 , p_commit =>p_commit
4635 , x_return_status =>x_return_status
4636 , x_msg_count =>x_msg_count
4637 , x_msg_data =>x_msg_data
4638 , p_task_assignment_id =>l_task_assignment_id
4639 , p_object_version_number => x_version
4640 , p_task_id =>p_task_id
4641 , p_resource_type_code =>p_owner_type_code
4642 , p_resource_id =>p_owner_id
4643 , p_assignment_status_id => p_task_status_id
4644 , p_actual_start_date =>p_scheduled_start_date
4645 , p_actual_end_date =>p_scheduled_end_date
4646 , p_object_capacity_id => p_trip
4647 , p_task_name =>p_task_name
4648 , x_task_object_version_number => l_obj_task
4649 , x_task_status_id =>l_ts);
4650
4651 IF x_return_status = fnd_api.g_ret_sts_error THEN
4652 RAISE fnd_api.g_exc_error;
4653 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4654 RAISE fnd_api.g_exc_unexpected_error;
4655 END IF;
4656
4657 IF fnd_api.to_boolean (p_commit) THEN
4658 COMMIT WORK;
4659 END IF;
4660
4661 Exception
4662 WHEN fnd_api.g_exc_error THEN
4663 ROLLBACK TO csf_update_per_task;
4664 x_return_status := fnd_api.g_ret_sts_error;
4665 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4666 WHEN fnd_api.g_exc_unexpected_error THEN
4667 ROLLBACK TO csf_update_per_task;
4668 x_return_status := fnd_api.g_ret_sts_unexp_error;
4669 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4670 WHEN OTHERS THEN
4671 x_return_status := fnd_api.g_ret_sts_unexp_error;
4672 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4673 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4674 END IF;
4675 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4676 ROLLBACK TO csf_update_per_task;
4677 end;
4678
4679 END csf_tasks_pub;