[Home] [Help]
PACKAGE BODY: APPS.CSF_TASKS_PUB
Source
1 PACKAGE BODY CSF_TASKS_PUB AS
2 /* $Header: CSFPTSKB.pls 120.99.12020000.8 2013/04/09 09:52:00 hemasha 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 := CSR_SCHEDULER_PUB.GET_SCH_PARAMETER_VALUE('spPlanScope');
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(CSR_SCHEDULER_PUB.GET_SCH_PARAMETER_VALUE('spMaxOvertime'), 0) / (24 * 60);
50 g_uom_hours VARCHAR2(60):= fnd_profile.value('CSF_UOM_HOURS');
51
52 TYPE number_tbl_type IS TABLE OF NUMBER
53 INDEX BY BINARY_INTEGER;
54
55
56
57 /*******************************************************************************
58 * Private Functions and Procedures *
59 ********************************************************************************/
60 function convert_to_days
61 ( p_duration number
62 , p_uom varchar2
63 , p_uom_hours varchar2
64 )
65 return number
66 is
67 l_value number;
68 begin
69 l_value := inv_convert.inv_um_convert
70 ( item_id => 0
71 , precision => 20
72 , from_quantity => p_duration
73 , from_unit => p_uom
74 , to_unit => p_uom_hours
75 , from_name => null
76 , to_name => null
77 );
78 return l_value/24;
79 end convert_to_days;
80
81 function return_primary_phone
82 (
83 party_id IN number
84 ) return varchar2 is
85 l_phone varchar2(50);
86 begin
87
88 select decode(phone_country_code,'','',phone_country_code || '-' ) ||
89 decode(phone_area_code,'','',phone_area_code || '-' ) || phone_number
90 into l_phone
91 from (select phone_number, phone_area_code, phone_country_code
92 from hz_contact_points
93 where owner_table_id = party_id
94 and owner_table_name ='HZ_PARTY_SITES'
95 and contact_point_type = 'PHONE'
96 order by primary_flag desc, creation_date asc)
97 where rownum = 1;
98
99 return l_phone;
100
101 exception
102 when NO_DATA_FOUND then
103 l_phone := null;
104 return l_phone;
105 end return_primary_phone;
106
107 FUNCTION is_cancel_status (p_status_id jtf_task_statuses_b.task_status_id%TYPE)
108 RETURN BOOLEAN IS
109 CURSOR c_cancelled_flag IS
110 SELECT task_status_id
111 FROM jtf_task_statuses_b
112 WHERE task_status_id = p_status_id
113 AND NVL (cancelled_flag, 'N') = 'Y';
114 BEGIN
115 FOR v_cancelled_flag IN c_cancelled_flag LOOP
116 RETURN TRUE;
117 END LOOP;
118 RETURN FALSE;
119 END is_cancel_status;
120
121 FUNCTION has_field_service_rule (p_task_type_id NUMBER)
122 RETURN VARCHAR2 IS
123 CURSOR c_task_type IS
124 SELECT task_type_id
125 FROM jtf_task_types_b
126 WHERE rule = 'DISPATCH'
127 AND NVL (schedule_flag, 'N') = 'Y'
128 AND task_type_id = p_task_type_id;
129 BEGIN
130 FOR v_task_type IN c_task_type LOOP
131 RETURN fnd_api.g_true;
132 END LOOP;
133 RETURN fnd_api.g_false;
134 END has_field_service_rule;
135
136 FUNCTION has_schedulable_status (p_task_status_id NUMBER)
137 RETURN BOOLEAN IS
138 CURSOR c_task_status IS
139 SELECT task_status_id
140 FROM jtf_task_statuses_b
141 WHERE NVL (schedulable_flag, 'N') = 'Y'
142 AND task_status_id = p_task_status_id;
143 BEGIN
144 FOR v_task_status IN c_task_status LOOP
145 RETURN TRUE;
146 END LOOP;
147 RETURN FALSE;
148 END has_schedulable_status;
149
150 FUNCTION task_number (p_task_id IN NUMBER)
151 RETURN VARCHAR2 IS
152 CURSOR c_number IS
153 SELECT task_number
154 FROM jtf_tasks_b
155 WHERE task_id = p_task_id;
156 l_task_number jtf_tasks_b.task_number%TYPE;
157 BEGIN
158 OPEN c_number;
159 FETCH c_number INTO l_task_number;
160 CLOSE c_number;
161 RETURN l_task_number;
162 END task_number;
163
164 FUNCTION is_debrief_closed(p_task_assignment_id NUMBER)
165 RETURN BOOLEAN IS
166 CURSOR c_debrief_status IS
167 SELECT NVL (cdh.processed_flag, 'PENDING') debrief_status
168 FROM csf_debrief_headers cdh
169 WHERE cdh.task_assignment_id = p_task_assignment_id;
170 BEGIN
171 FOR v_debrief_status IN c_debrief_status LOOP
172 IF v_debrief_status.debrief_status <> 'COMPLETED' THEN
173 RETURN FALSE;
174 END IF;
175 END LOOP;
176 RETURN TRUE;
177 END is_debrief_closed;
178
179 FUNCTION check_schedulable(
180 p_deleted_flag IN VARCHAR2
181 , p_planned_start_date IN DATE
182 , p_planned_end_date IN DATE
183 , p_planned_effort IN NUMBER
184 , p_task_type_id IN NUMBER
185 , p_task_status_id IN NUMBER
186 , x_reason_code OUT NOCOPY VARCHAR2
187 ) RETURN BOOLEAN IS
188 BEGIN
189 x_reason_code := NULL;
190 IF p_deleted_flag = 'Y' THEN
191 x_reason_code := 'CSF_DELETED_TASK';
192 ELSIF has_field_service_rule (p_task_type_id) = fnd_api.g_false THEN
193 x_reason_code := 'CSF_NON_FS_TASK';
194 ELSIF (p_planned_start_date IS NULL OR p_planned_end_date IS NULL) THEN
195 x_reason_code := 'CSF_PLANNED_DATE_NOT_SET';
196 ELSIF p_planned_effort IS NULL THEN
197 x_reason_code := 'CSF_PLANNED_EFFORT_NOT_SET';
198 ELSIF NOT has_schedulable_status (p_task_status_id) THEN
199 x_reason_code := 'CSF_STATUS_NOT_SCHEDULABLE';
200 END IF;
201 RETURN x_reason_code IS NULL;
202 END check_schedulable;
203
204
205 /*******************************************************************************
206 * Public Functions and Procedures *
207 ********************************************************************************/
208
209 FUNCTION get_task_status_name (p_task_status_id NUMBER)
210 RETURN VARCHAR2 IS
211 l_return_value VARCHAR2 (30);
212
213 CURSOR c_name IS
214 SELECT NAME
215 FROM jtf_task_statuses_vl
216 WHERE task_status_id = p_task_status_id;
217 BEGIN
218 OPEN c_name;
219 FETCH c_name INTO l_return_value;
220 CLOSE c_name;
221
222 RETURN l_return_value;
223 END get_task_status_name;
224
225 FUNCTION get_dep_task_type_id RETURN NUMBER IS
226 BEGIN
227 RETURN g_dep_task_type_id;
228 END get_dep_task_type_id;
229
230 FUNCTION get_arr_task_type_id RETURN NUMBER IS
231 BEGIN
232 RETURN g_arr_task_type_id;
233 END get_arr_task_type_id;
234
235 -- Validate Field Service State Transitions
236 FUNCTION validate_state_transition (
237 p_state_type VARCHAR2
238 , p_old_status_id NUMBER
239 , p_new_status_id NUMBER
240 )
241 RETURN VARCHAR2 IS
242 -- Validation when new object
243 CURSOR c_valid_new_trans IS
244 SELECT NULL
245 FROM jtf_state_responsibilities re
246 , jtf_state_rules_b ru
247 , jtf_state_transitions tr
248 WHERE (re.responsibility_id = fnd_global.resp_id OR fnd_global.resp_id = -1)
249 AND re.rule_id = ru.rule_id
250 AND ru.state_type = p_state_type
251 AND ru.rule_id = tr.rule_id
252 AND tr.initial_state_id = p_new_status_id;
253
254 -- Validation when existing object
255 CURSOR c_valid_existing_trans IS
256 SELECT NULL
257 FROM jtf_state_responsibilities re
258 , jtf_state_rules_b ru
259 , jtf_state_transitions tr
260 WHERE (re.responsibility_id = fnd_global.resp_id OR fnd_global.resp_id = -1)
261 AND re.rule_id = ru.rule_id
262 AND ru.state_type = p_state_type
263 AND ru.rule_id = tr.rule_id
264 AND tr.initial_state_id = p_old_status_id
265 AND tr.final_state_id = p_new_status_id;
266
267 l_dummy VARCHAR2(1);
268 l_transition_valid VARCHAR2(1);
269 BEGIN
270 l_transition_valid := fnd_api.g_false;
271
272 -- If the new Status eqauls the old Status... return Valid.
273 IF p_new_status_id = p_old_status_id THEN
274 l_transition_valid := fnd_api.g_true;
275 ELSIF p_old_status_id IS NULL THEN
276 OPEN c_valid_new_trans;
277 FETCH c_valid_new_trans INTO l_dummy;
278 IF c_valid_new_trans%FOUND THEN
279 l_transition_valid := fnd_api.g_true;
280 END IF;
281 CLOSE c_valid_new_trans;
282 ELSE
283 OPEN c_valid_existing_trans;
284 FETCH c_valid_existing_trans INTO l_dummy;
285 IF c_valid_existing_trans%FOUND THEN
286 l_transition_valid := fnd_api.g_true;
287 END IF;
288 CLOSE c_valid_existing_trans;
289 END IF;
290 RETURN l_transition_valid;
291 END validate_state_transition;
292
293 /**
294 * Used to retrieve the list of valid Task Statuses the Task can take either from
295 * from its current status or when it is created anew. It gives a list of Task
296 * Status Names rather than Task Status IDs.
297 */
298 FUNCTION get_valid_statuses (
299 p_state_type VARCHAR2
300 , p_old_status_id NUMBER
301 )
302 RETURN VARCHAR2 IS
303 l_return_value VARCHAR2 (2000);
304
305 -- Get valid statuses when the object is creeted for the first time
306 CURSOR c_valid_new_trans IS
307 SELECT DISTINCT tr.initial_state_id, ts.name
308 FROM jtf_state_responsibilities re
309 , jtf_state_rules_b ru
310 , jtf_state_transitions tr
311 , jtf_task_statuses_tl ts
312 WHERE (re.responsibility_id = fnd_global.resp_id OR fnd_global.resp_id = -1)
313 AND re.rule_id = ru.rule_id
314 AND ru.state_type = p_state_type
315 AND ru.rule_id = tr.rule_id
316 AND ts.task_status_id = tr.initial_state_id
317 AND ts.language = userenv('LANG');
318
319 -- Get valid statuses from an existing status
320 CURSOR c_valid_existing_trans IS
321 SELECT DISTINCT tr.final_state_id, ts.name
322 FROM jtf_state_responsibilities re
323 , jtf_state_rules_b ru
324 , jtf_state_transitions tr
325 , jtf_task_statuses_tl ts
326 WHERE (re.responsibility_id = fnd_global.resp_id OR fnd_global.resp_id = -1)
327 AND re.rule_id = ru.rule_id
328 AND ru.state_type = p_state_type
329 AND ru.rule_id = tr.rule_id
330 AND tr.initial_state_id = p_old_status_id
331 AND ts.task_status_id = tr.final_state_id
332 AND ts.language = userenv('LANG');
333 BEGIN
334 IF p_old_status_id IS NULL THEN
335 FOR v_valid_new_trans IN c_valid_new_trans LOOP
336 l_return_value := l_return_value || fnd_global.local_chr(10) || v_valid_new_trans.name;
337 END LOOP;
338 ELSE
339 FOR v_valid_existing_trans IN c_valid_existing_trans LOOP
340 l_return_value := l_return_value || fnd_global.local_chr(10) || v_valid_existing_trans.name;
341 END LOOP;
342 END IF;
343
344 RETURN l_return_value;
345 END get_valid_statuses;
346
347 -- Clubs the operation of the above functions validate_state_transition and
348 -- get_valid_statuses into one procedure.
349 PROCEDURE validate_status_change(p_old_status_id NUMBER, p_new_status_id NUMBER) IS
350 l_trans_valid VARCHAR2(1);
351 l_valid_statuses VARCHAR2(2000);
352 BEGIN
353 IF p_new_status_id IS NULL THEN
354 RETURN;
355 END IF;
356
357 IF p_new_status_id = p_old_status_id THEN
358 RETURN;
359 END IF;
360
361 l_trans_valid := validate_state_transition ('TASK_STATUS', p_old_status_id, p_new_status_id);
362 IF l_trans_valid = fnd_api.g_false THEN
363 l_valid_statuses := get_valid_statuses ('TASK_STATUS', p_old_status_id);
364 IF l_valid_statuses IS NULL THEN
365 fnd_message.set_name ('CSF', 'CSF_NO_STATE_TRANSITION');
366 ELSE
367 fnd_message.set_name ('CSF', 'CSF_INVALID_STATE_TRANSITION');
368 fnd_message.set_token ('P_VALID_STATUSES', l_valid_statuses);
369 END IF;
370 fnd_message.set_token ('P_NEW_STATUS', get_task_status_name (p_new_status_id));
371 fnd_msg_pub.ADD;
372 RAISE fnd_api.g_exc_error;
373 END IF;
374 END validate_status_change;
375
376 /**
377 * Checks whether the given Task is closable.
378 * @returns TRUE If Task is closable
379 * @returns FALSE If Task is not closable
380 */
381 FUNCTION is_task_closable (
382 x_return_status OUT NOCOPY VARCHAR2
383 , x_msg_count OUT NOCOPY NUMBER
384 , x_msg_data OUT NOCOPY VARCHAR2
385 , p_task_id IN NUMBER
386 )
387 RETURN BOOLEAN IS
388 l_api_name CONSTANT VARCHAR2 (30) := 'IS_TASK_CLOSABLE';
389
390 CURSOR c_task_details IS
391 SELECT task_status_id
392 FROM jtf_tasks_b
393 WHERE task_id = p_task_id;
394
395 -- Cursor to get all the Task Assignments for the Task to be closed.
396 CURSOR c_task_assignments IS
397 SELECT ta.task_assignment_id
398 , t.scheduled_start_date
399 , t.scheduled_end_date
400 , NVL (ts.closed_flag, 'N') closed_flag
401 , NVL (ts.cancelled_flag, 'N') cancelled_flag
402 , NVL (ts.completed_flag, 'N') completed_flag
403 , NVL (ts.rejected_flag, 'N') rejected_flag
404 FROM jtf_task_assignments ta, jtf_tasks_b t, jtf_task_statuses_b ts
405 WHERE ta.task_id = t.task_id
406 AND t.task_id = p_task_id
407 AND assignment_status_id = ts.task_status_id;
408
409 l_old_status_id NUMBER;
410 l_close_status_id NUMBER;
411 l_valid_statuses VARCHAR2 (2000);
412 l_valid_status BOOLEAN := FALSE;
413 l_update_schedulable_task VARCHAR2(3);
414 l_task_closure Varchar2(3) := fnd_profile.value_specific('CSF: Enforce_Task_Closure', fnd_global.user_id);
415 BEGIN
416 -- Initialize API return status to success
417 x_return_status := fnd_api.g_ret_sts_success;
418
419 OPEN c_task_details;
420 FETCH c_task_details INTO l_old_status_id;
421 IF c_task_details%NOTFOUND THEN
422 CLOSE c_task_details;
423 RAISE NO_DATA_FOUND;
424 END IF;
425 CLOSE c_task_details;
426
427 -- Before a Task can be closed, there are some checks that needs to be done
428
429 -- Check whether the State Transition is valid.
430 l_close_status_id := fnd_profile.VALUE ('CSF_DFLT_AUTO_CLOSE_TASK_STATUS');
431 IF validate_state_transition ('TASK_STATUS', l_old_status_id, l_close_status_id) = fnd_api.g_false THEN
432 l_valid_statuses := get_valid_statuses ('TASK_STATUS', l_old_status_id);
433 IF l_valid_statuses IS NULL THEN
434 fnd_message.set_name ('CSF', 'CSF_NO_STATE_TRANSITION');
435 ELSE
436 fnd_message.set_name ('CSF', 'CSF_INVALID_STATE_TRANSITION');
437 fnd_message.set_token ('P_VALID_STATUSES', l_valid_statuses);
438 END IF;
439 fnd_message.set_token ('P_NEW_STATUS', get_task_status_name (l_close_status_id));
440 fnd_msg_pub.ADD;
441 RAISE fnd_api.g_exc_error;
442 END IF;
443
444 -- Check whether the Assignments and the associated Debriefs have been closed
445 l_update_schedulable_task := NVL(fnd_profile.value('CSFW_UPD_SCHEDULABLE'), 'NO');
446 -- added an if codetion for task closure for the bug 8282570
447 if nvl(l_task_closure,'N') ='Y'
448 then
449 if nvl(l_update_schedulable_task,'NO') = 'NO'
450 then
451 FOR v_task_assignment IN c_task_assignments
452 LOOP
453 -- Check whether the Task Assignment is still Open.
454 IF v_task_assignment.closed_flag = 'N' AND
455 v_task_assignment.cancelled_flag = 'N' AND
456 v_task_assignment.completed_flag = 'N' AND
457 v_task_assignment.rejected_flag = 'N'
458 THEN
459 l_valid_status := TRUE;
460 EXIT;
461 END IF;
462 END LOOP;
463 end if;
464 -- Check whether the Debrief is closed if Task Assignment is not open
465 -- and only when the profile "CSFW: Update Schedulable Task" is set to Yes
466 IF l_update_schedulable_task = 'YES'
467 THEN
468 FOR v_task_assignment IN c_task_assignments
469 LOOP
470 -- Check whether the Task Assignment is still Open.
471 IF v_task_assignment.closed_flag = 'N' AND
472 v_task_assignment.cancelled_flag = 'N' AND
473 v_task_assignment.completed_flag = 'N' AND
474 v_task_assignment.rejected_flag = 'N'
475 THEN
476 l_valid_status := TRUE;
477 EXIT;
478 END IF;
479 IF NOT is_debrief_closed(v_task_assignment.task_assignment_id)
480 THEN
481 fnd_message.set_name('CSF', 'CSF_DEBRIEF_PENDING');
482 fnd_msg_pub.ADD;
483 RAISE fnd_api.g_exc_error;
484 END IF;
485 END LOOP;
486 END IF;
487 -- added the following code for bug 8282570
488 IF l_valid_status = false
489 THEN
490 RETURN TRUE;
491 ELSIF l_valid_status = TRUE
492 THEN
493 FND_MESSAGE.Set_Name('CSF', 'CSF_CLOSED_TASK');
494 fnd_msg_pub.add;
495 raise fnd_api.g_exc_error;
496 END IF;
497 ---- end of code for the bug 8282570
498 ELSE
499 RETURN TRUE;
500 END IF;
501 EXCEPTION
502 WHEN fnd_api.g_exc_error THEN
503 x_return_status := fnd_api.g_ret_sts_error;
504 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
505 RETURN FALSE;
506 WHEN fnd_api.g_exc_unexpected_error THEN
507 x_return_status := fnd_api.g_ret_sts_unexp_error;
508 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
509 RETURN FALSE;
510 WHEN OTHERS THEN
511 x_return_status := fnd_api.g_ret_sts_unexp_error;
512 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
513 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
514 END IF;
515 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
516 RETURN FALSE;
517 END is_task_closable;
518
519 /**
520 * Checks whether the given Task can be closed and returns True or False
521 * accordingly.
522 * @deprecated Use IS_TASK_CLOSABLE (SR Team is still calling this version)
523 */
524 FUNCTION task_is_closable (
525 x_return_status OUT NOCOPY VARCHAR2
526 , x_msg_count OUT NOCOPY NUMBER
527 , x_msg_data OUT NOCOPY VARCHAR2
528 , p_task_id IN NUMBER
529 )
530 RETURN BOOLEAN IS
531 BEGIN
532 RETURN is_task_closable(
533 x_return_status => x_return_status
534 , x_msg_count => x_msg_count
535 , x_msg_data => x_msg_data
536 , p_task_id => p_task_id
537 );
538 END task_is_closable;
539
540
541
542 FUNCTION is_task_schedulable (
543 x_return_status OUT NOCOPY VARCHAR2
544 , x_msg_count OUT NOCOPY NUMBER
545 , x_msg_data OUT NOCOPY VARCHAR2
546 , p_task_id IN NUMBER
547 )
548 RETURN BOOLEAN IS
549 l_api_name CONSTANT VARCHAR2(30) := 'IS_TASK_SCHEDULABLE';
550
551 CURSOR c_task_details IS
552 SELECT task_type_id
553 , task_status_id
554 , planned_start_date
555 , planned_end_date
556 , planned_effort
557 , address_id
558 , deleted_flag
559 FROM jtf_tasks_b
560 WHERE task_id = p_task_id;
561
562 l_task_details c_task_details%ROWTYPE;
563 l_schedulable BOOLEAN;
564 l_message_name VARCHAR2(100);
565 BEGIN
566 -- Initialize API return status to success
567 x_return_status := fnd_api.g_ret_sts_success;
568
569 -- Fetching the Task Details
570 OPEN c_task_details;
571 FETCH c_task_details INTO l_task_details;
572 IF c_task_details%NOTFOUND THEN
573 CLOSE c_task_details;
574 RAISE NO_DATA_FOUND;
575 END IF;
576 CLOSE c_task_details;
577
578 l_schedulable := check_schedulable(
579 p_deleted_flag => l_task_details.deleted_flag
580 , p_planned_start_date => l_task_details.planned_start_date
581 , p_planned_end_date => l_task_details.planned_end_date
582 , p_planned_effort => l_task_details.planned_effort
583 , p_task_type_id => l_task_details.task_type_id
584 , p_task_status_id => l_task_details.task_status_id
585 , x_reason_code => l_message_name
586 );
587
588 IF NOT l_schedulable THEN
589 fnd_message.set_name('CSF', l_message_name);
590 fnd_msg_pub.ADD;
591 RAISE fnd_api.g_exc_error;
592 END IF;
593
594 RETURN TRUE;
595 EXCEPTION
596 WHEN fnd_api.g_exc_error THEN
597 x_return_status := fnd_api.g_ret_sts_error;
598 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
599 RETURN FALSE;
600 WHEN fnd_api.g_exc_unexpected_error THEN
601 x_return_status := fnd_api.g_ret_sts_unexp_error;
602 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
603 RETURN FALSE;
604 WHEN OTHERS THEN
605 x_return_status := fnd_api.g_ret_sts_unexp_error;
606 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
607 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
608 END IF;
609 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
610 RETURN FALSE;
611 END is_task_schedulable;
612
613 FUNCTION is_task_scheduled (
614 x_return_status OUT NOCOPY VARCHAR2
615 , x_msg_count OUT NOCOPY NUMBER
616 , x_msg_data OUT NOCOPY VARCHAR2
617 , p_task_id IN NUMBER
618 )
619 RETURN BOOLEAN IS
620 l_api_name CONSTANT VARCHAR2(30) := 'IS_TASK_SCHEDULED';
621
622 CURSOR c_task_ta_det IS
623 SELECT t.scheduled_start_date
624 , t.scheduled_end_date
625 , t.task_split_flag
626 , t.task_status_id
627 , ta.resource_id
628 FROM jtf_tasks_b t, jtf_task_assignments ta
629 WHERE ta.task_id = t.task_id AND t.task_id = p_task_id;
630
631 l_sched_start jtf_tasks_b.scheduled_start_date%TYPE;
632 l_sched_end jtf_tasks_b.scheduled_end_date%TYPE;
633 l_resource_id jtf_task_assignments.resource_id%TYPE;
634 l_split_flag jtf_tasks_b.task_split_flag%TYPE;
635 l_status_id jtf_task_statuses_b.task_status_id%TYPE;
636 BEGIN
637 -- Initialize API return status to success
638 x_return_status := fnd_api.g_ret_sts_success;
639
640 OPEN c_task_ta_det;
641 FETCH c_task_ta_det
642 INTO l_sched_start, l_sched_end, l_split_flag, l_status_id, l_resource_id;
643 IF c_task_ta_det%NOTFOUND THEN
644 CLOSE c_task_ta_det;
645 RAISE NO_DATA_FOUND;
646 END IF;
647 CLOSE c_task_ta_det;
648
649 IF l_split_flag IS NULL OR l_split_flag = 'D' THEN
650 IF (l_resource_id IS NOT NULL) AND NOT (is_cancel_status (l_status_id)) THEN
651 IF l_sched_start IS NOT NULL AND l_sched_end IS NOT NULL THEN
652 RETURN TRUE;
653 END IF;
654 END IF;
655 ELSE -- task_split_flag is 'M'
656 -- put the additional logic here asked from Max.
657 RETURN TRUE;
658 END IF;
659 RETURN FALSE;
660 EXCEPTION
661 WHEN OTHERS THEN
662 x_return_status := fnd_api.g_ret_sts_unexp_error;
663 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
664 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
665 END IF;
666 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
667 RETURN FALSE;
668 END is_task_scheduled;
669
670 /**
671 * Determines whether the given Task is escalated or not
672 */
673 FUNCTION is_task_escalated(p_task_id NUMBER)
674 RETURN BOOLEAN IS
675 l_ref_task_id NUMBER;
676 l_escalated NUMBER;
677
678 CURSOR c_task_ref IS
679 SELECT task_id
680 FROM jtf_task_references_b r
681 WHERE r.reference_code = 'ESC'
682 AND r.object_type_code = 'TASK'
683 AND r.object_id = p_task_id;
684
685 CURSOR c_esc(b_task_id NUMBER) IS
686 SELECT 1
687 FROM jtf_tasks_b t
688 , jtf_task_statuses_b s
689 WHERE t.task_id = b_task_id
690 AND t.task_type_id = g_esc_task_type_id
691 AND s.task_status_id = t.task_status_id
692 AND NVL(s.closed_flag, 'N') <> 'Y'
693 AND NVL(t.deleted_flag, 'N') <> 'Y';
694 BEGIN
695 -- Get the Reference Task to the given Task
696 OPEN c_task_ref;
697 FETCH c_task_ref INTO l_ref_task_id;
698 CLOSE c_task_ref;
699
700 IF l_ref_task_id IS NULL THEN
701 RETURN FALSE;
702 END IF;
703
704 -- Check whether the Reference object is an Escalation Task
705 OPEN c_esc(l_ref_task_id);
706 FETCH c_esc INTO l_escalated;
707 CLOSE c_esc;
708
709 RETURN (l_escalated IS NOT NULL);
710 EXCEPTION
711 WHEN OTHERS THEN
712 IF c_task_ref%ISOPEN THEN
713 CLOSE c_task_ref;
714 END IF;
715 IF c_esc%ISOPEN THEN
716 CLOSE c_esc;
717 END IF;
718 RETURN FALSE;
719 END is_task_escalated;
720
721 /**
722 * Closes an existing task
723 */
724 PROCEDURE close_task (
725 p_api_version IN NUMBER
726 , p_init_msg_list IN VARCHAR2
727 , p_commit IN VARCHAR2
728 , x_return_status OUT NOCOPY VARCHAR2
729 , x_msg_count OUT NOCOPY NUMBER
730 , x_msg_data OUT NOCOPY VARCHAR2
731 , p_task_id IN NUMBER
732 ) IS
733 l_api_version CONSTANT NUMBER := 1.0;
734 l_api_name CONSTANT VARCHAR2(30) := 'CLOSE_TASK';
735
736 l_close_status_id NUMBER;
737 l_object_version_number NUMBER;
738 BEGIN
739 -- Standard start of API savepoint
740 SAVEPOINT close_task_pub;
741
742 -- Standard call to check for call compatibility
743 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
744 RAISE fnd_api.g_exc_unexpected_error;
745 END IF;
746
747 -- Initialize message list if p_init_msg_list is set to TRUE
748 IF fnd_api.to_boolean (p_init_msg_list) THEN
749 fnd_msg_pub.initialize;
750 END IF;
751
752 -- Initialize API return status to success
753 x_return_status := fnd_api.g_ret_sts_success;
754
755 l_close_status_id := fnd_profile.VALUE ('CSF_DFLT_AUTO_CLOSE_TASK_STATUS');
756 update_task_status (
757 p_api_version => 1.0
758 , x_return_status => x_return_status
759 , x_msg_count => x_msg_count
760 , x_msg_data => x_msg_data
761 , p_task_id => p_task_id
762 , p_task_status_id => l_close_status_id
763 , p_object_version_number => l_object_version_number
764 );
765
766 IF x_return_status = fnd_api.g_ret_sts_error THEN
767 RAISE fnd_api.g_exc_error;
768 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
769 RAISE fnd_api.g_exc_unexpected_error;
770 END IF;
771
772 -- Standard check of p_commit
773 IF fnd_api.to_boolean (p_commit) THEN
774 COMMIT WORK;
775 END IF;
776 -- Standard call to get message count and if count is 1, get message info
777 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
778 EXCEPTION
779 WHEN fnd_api.g_exc_error THEN
780 ROLLBACK TO close_task_pub;
781 x_return_status := fnd_api.g_ret_sts_error;
782 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
783 WHEN fnd_api.g_exc_unexpected_error THEN
784 ROLLBACK TO close_task_pub;
785 x_return_status := fnd_api.g_ret_sts_unexp_error;
786 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
787 WHEN OTHERS THEN
788 x_return_status := fnd_api.g_ret_sts_unexp_error;
789 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
790 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
791 END IF;
792 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
793 ROLLBACK TO close_task_pub;
794 END close_task;
795
796 -- Determines which tasks in a PL/SQL table are modified
797 PROCEDURE identify_modified_tasks (
798 p_api_version IN NUMBER
799 , p_init_msg_list IN VARCHAR2
800 , x_return_status OUT NOCOPY VARCHAR2
801 , x_msg_count OUT NOCOPY NUMBER
802 , x_msg_data OUT NOCOPY VARCHAR2
803 , x_collection IN OUT NOCOPY tasks_tbl_type
804 , x_count OUT NOCOPY NUMBER
805 ) IS
806 l_api_version CONSTANT NUMBER := 1.0;
807 l_api_name CONSTANT VARCHAR2(30) := 'IDENTIFY_MODIFIED_TASKS';
808 l_idx PLS_INTEGER;
809
810 CURSOR c_task_info (p_row_id VARCHAR) IS
811 SELECT t.object_version_number
812 , t.task_status_id
813 , ts.name task_status_name
814 , t.scheduled_start_date
815 , t.scheduled_end_date
816 , t.planned_start_date
817 , t.planned_end_date
818 , t.planned_effort
819 , t.planned_effort_uom
820 , t.task_split_flag
821 , t.parent_task_id
822 , ts.schedulable_flag ts_schedulable_flag
823 , ts.assigned_flag
824 , tt.schedule_flag tt_schedule_flag
825 , ta.resource_name
826 FROM jtf_tasks_b t
827 , csf_ct_task_assignments ta
828 , jtf_task_statuses_vl ts
829 , jtf_task_types_b tt
830 WHERE t.ROWID = CHARTOROWID (p_row_id)
831 AND ts.task_status_id = t.task_status_id
832 AND tt.task_type_id = t.task_type_id
833 AND ta.task_id (+) = t.task_id;
834
835 l_task_info c_task_info%ROWTYPE;
836 BEGIN
837 -- standard call to check for call compatibility
838 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
839 RAISE fnd_api.g_exc_unexpected_error;
840 END IF;
841
842 -- initialize message list if p_init_msg_list is set to true
843 IF fnd_api.to_boolean (p_init_msg_list) THEN
844 fnd_msg_pub.initialize;
845 END IF;
846
847 -- initialize api return status to success
848 x_return_status := fnd_api.g_ret_sts_success;
849
850 -- start processing
851 x_count := 0;
852 l_idx := x_collection.FIRST;
853 WHILE l_idx IS NOT NULL LOOP
854 -- take only schedulable tasks into account
855 -- IF ( NVL (x_collection(l_idx).status_schedulable_flag, 'N') = 'Y' --commented for ER 6360530
856 -- OR NVL (x_collection(l_idx).status_assigned_flag, 'N') = 'Y'
857 -- )
858 -- AND NVL (x_collection(l_idx).type_schedulable_flag, 'N') = 'Y' THEN
859 OPEN c_task_info (x_collection (l_idx).row_id);
860 FETCH c_task_info INTO l_task_info;
861
862 IF c_task_info%FOUND THEN
863 IF NVL (l_task_info.object_version_number, -1)
864 <> NVL(x_collection(l_idx).object_version_number, -1)
865 THEN
866 x_collection(l_idx).object_version_number := l_task_info.object_version_number;
867 x_collection(l_idx).task_status_id := l_task_info.task_status_id;
868 x_collection(l_idx).task_status := l_task_info.task_status_name;
869 x_collection(l_idx).scheduled_start_date := l_task_info.scheduled_start_date;
870 x_collection(l_idx).scheduled_end_date := l_task_info.scheduled_end_date;
871 x_collection(l_idx).planned_start_date := l_task_info.planned_start_date;
872 x_collection(l_idx).planned_end_date := l_task_info.planned_end_date;
873 x_collection(l_idx).planned_effort := l_task_info.planned_effort;
874 x_collection(l_idx).planned_effort_uom := l_task_info.planned_effort_uom;
875 x_collection(l_idx).status_schedulable_flag := l_task_info.ts_schedulable_flag;
876 x_collection(l_idx).type_schedulable_flag := l_task_info.tt_schedule_flag;
877 x_collection(l_idx).status_assigned_flag := l_task_info.assigned_flag;
878 x_collection(l_idx).resource_name := l_task_info.resource_name;
879 x_collection(l_idx).task_split_flag := l_task_info.task_split_flag;
880 x_collection(l_idx).parent_task_id := l_task_info.parent_task_id;
881 x_collection(l_idx).updated_flag := 'Y';
882 x_count := x_count + 1;
883 ELSE
884 /* reset updated flag if not different */
885 x_collection (l_idx).updated_flag := 'N';
886 END IF;
887 END IF;
888
889 CLOSE c_task_info;
890 --END IF;
891 l_idx := x_collection.NEXT (l_idx);
892 END LOOP;
893
894 -- standard call to get message count and if count is 1, get message info
895 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
896 EXCEPTION
897 WHEN OTHERS THEN
898 x_return_status := fnd_api.g_ret_sts_unexp_error;
899 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
900 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
901 END IF;
902 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
903 END identify_modified_tasks;
904
905 ---------------------------------------------------------------------------
906 -- validate_planned_dates
907 -- validate start / end, can not be in past, implement same rules as
908 -- Scheduler core:
909 -- 1. when start in past or null, then start becomes sysdate
910 -- 2. when end in past or null, then end becomes start + plan scope
911 --
912 -- x_start : planned start date to be validated and possibly adjusted
913 -- x_end : planned end date
914 ---------------------------------------------------------------------------
915 PROCEDURE validate_planned_dates (x_start IN OUT NOCOPY DATE, x_end IN OUT NOCOPY DATE) IS
916 BEGIN
917 IF x_start < SYSDATE OR x_start IS NULL THEN
918 x_start := SYSDATE;
919 END IF;
920 --
921 IF x_end < SYSDATE OR x_end IS NULL THEN
922 x_end := x_start + g_plan_scope;
923 END IF;
924 END validate_planned_dates;
925
926 PROCEDURE create_task (
927 p_api_version IN NUMBER
928 , p_init_msg_list IN VARCHAR2
929 , p_commit IN VARCHAR2
930 , x_return_status OUT NOCOPY VARCHAR2
931 , x_msg_count OUT NOCOPY NUMBER
932 , x_msg_data OUT NOCOPY VARCHAR2
933 , p_task_id IN NUMBER
934 , p_task_name IN VARCHAR2
935 , p_description IN VARCHAR2
936 , p_task_type_name IN VARCHAR2
937 , p_task_type_id IN NUMBER
938 , p_task_status_name IN VARCHAR2
939 , p_task_status_id IN NUMBER
940 , p_task_priority_name IN VARCHAR2
941 , p_task_priority_id IN NUMBER
942 , p_owner_type_name IN VARCHAR2
943 , p_owner_type_code IN VARCHAR2
944 , p_owner_id IN NUMBER
945 , p_owner_territory_id IN NUMBER
946 , p_owner_status_id IN NUMBER
947 , p_assigned_by_name IN VARCHAR2
948 , p_assigned_by_id IN NUMBER
949 , p_customer_number IN VARCHAR2
950 , p_customer_id IN NUMBER
951 , p_cust_account_number IN VARCHAR2
952 , p_cust_account_id IN NUMBER
953 , p_address_id IN NUMBER
954 , p_address_number IN VARCHAR2
955 , p_location_id IN NUMBER
956 , p_planned_start_date IN DATE
957 , p_planned_end_date IN DATE
958 , p_scheduled_start_date IN DATE
959 , p_scheduled_end_date IN DATE
960 , p_actual_start_date IN DATE
961 , p_actual_end_date IN DATE
962 , p_timezone_id IN NUMBER
963 , p_timezone_name IN VARCHAR2
964 , p_source_object_type_code IN VARCHAR2
965 , p_source_object_id IN NUMBER
966 , p_source_object_name IN VARCHAR2
967 , p_duration IN NUMBER
968 , p_duration_uom IN VARCHAR2
969 , p_planned_effort IN NUMBER
970 , p_planned_effort_uom IN VARCHAR2
971 , p_actual_effort IN NUMBER
972 , p_actual_effort_uom IN VARCHAR2
973 , p_percentage_complete IN NUMBER
974 , p_reason_code IN VARCHAR2
975 , p_private_flag IN VARCHAR2
976 , p_publish_flag IN VARCHAR2
977 , p_restrict_closure_flag IN VARCHAR2
978 , p_multi_booked_flag IN VARCHAR2
979 , p_milestone_flag IN VARCHAR2
980 , p_holiday_flag IN VARCHAR2
981 , p_billable_flag IN VARCHAR2
982 , p_bound_mode_code IN VARCHAR2
983 , p_soft_bound_flag IN VARCHAR2
984 , p_workflow_process_id IN NUMBER
985 , p_notification_flag IN VARCHAR2
986 , p_notification_period IN NUMBER
987 , p_notification_period_uom IN VARCHAR2
988 , p_alarm_start IN NUMBER
989 , p_alarm_start_uom IN VARCHAR2
990 , p_alarm_on IN VARCHAR2
991 , p_alarm_count IN NUMBER
992 , p_alarm_interval IN NUMBER
993 , p_alarm_interval_uom IN VARCHAR2
994 , p_palm_flag IN VARCHAR2
995 , p_wince_flag IN VARCHAR2
996 , p_laptop_flag IN VARCHAR2
997 , p_device1_flag IN VARCHAR2
998 , p_device2_flag IN VARCHAR2
999 , p_device3_flag IN VARCHAR2
1000 , p_costs IN NUMBER
1001 , p_currency_code IN VARCHAR2
1002 , p_escalation_level IN VARCHAR2
1003 , p_attribute1 IN VARCHAR2
1004 , p_attribute2 IN VARCHAR2
1005 , p_attribute3 IN VARCHAR2
1006 , p_attribute4 IN VARCHAR2
1007 , p_attribute5 IN VARCHAR2
1008 , p_attribute6 IN VARCHAR2
1009 , p_attribute7 IN VARCHAR2
1010 , p_attribute8 IN VARCHAR2
1011 , p_attribute9 IN VARCHAR2
1012 , p_attribute10 IN VARCHAR2
1013 , p_attribute11 IN VARCHAR2
1014 , p_attribute12 IN VARCHAR2
1015 , p_attribute13 IN VARCHAR2
1016 , p_attribute14 IN VARCHAR2
1017 , p_attribute15 IN VARCHAR2
1018 , p_attribute_category IN VARCHAR2
1019 , p_date_selected IN VARCHAR2
1020 , p_category_id IN NUMBER
1021 , p_show_on_calendar IN VARCHAR2
1022 , p_task_assign_tbl IN jtf_tasks_pub.task_assign_tbl
1023 , p_task_depends_tbl IN jtf_tasks_pub.task_depends_tbl
1024 , p_task_rsrc_req_tbl IN jtf_tasks_pub.task_rsrc_req_tbl
1025 , p_task_refer_tbl IN jtf_tasks_pub.task_refer_tbl
1026 , p_task_dates_tbl IN jtf_tasks_pub.task_dates_tbl
1027 , p_task_notes_tbl IN jtf_tasks_pub.task_notes_tbl
1028 , p_task_recur_rec IN jtf_tasks_pub.task_recur_rec
1029 , p_task_contacts_tbl IN jtf_tasks_pub.task_contacts_tbl
1030 , p_template_id IN NUMBER
1031 , p_template_group_id IN NUMBER
1032 , p_enable_workflow IN VARCHAR2
1033 , p_abort_workflow IN VARCHAR2
1034 , p_task_split_flag IN VARCHAR2
1035 , p_parent_task_number IN VARCHAR2
1036 , p_parent_task_id IN NUMBER
1037 , p_child_position IN VARCHAR2
1038 , p_child_sequence_num IN NUMBER
1039 , x_task_id OUT NOCOPY NUMBER
1040 ) IS
1041 l_api_version CONSTANT NUMBER := 1.0;
1042 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TASK';
1043
1044 l_task_schedulable BOOLEAN;
1045 l_reason_code VARCHAR2(100);
1046
1047
1048 BEGIN
1049 SAVEPOINT csf_create_task_pub;
1050
1051 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1052 RAISE fnd_api.g_exc_unexpected_error;
1053 END IF;
1054
1055 IF fnd_api.to_boolean (p_init_msg_list) THEN
1056 fnd_msg_pub.initialize;
1057 END IF;
1058
1059 x_return_status := fnd_api.g_ret_sts_success;
1060
1061 l_task_schedulable := check_schedulable(
1062 p_deleted_flag => 'N'
1063 , p_planned_start_date => p_planned_start_date
1064 , p_planned_end_date => p_planned_end_date
1065 , p_planned_effort => p_planned_effort
1066 , p_task_type_id => p_task_type_id
1067 , p_task_status_id => p_task_status_id
1068 , x_reason_code => l_reason_code
1069 );
1070
1071 -- Task is not schedulable.
1072 IF l_task_schedulable = FALSE OR x_return_status <> fnd_api.g_ret_sts_success THEN
1073 fnd_message.set_name ('CSF', l_reason_code);
1074 fnd_msg_pub.ADD;
1075 RAISE fnd_api.g_exc_error;
1076 END IF;
1077
1078 jtf_tasks_pub.create_task (
1079 p_api_version => p_api_version
1080 , p_init_msg_list => fnd_api.g_false
1081 , p_commit => fnd_api.g_false
1082 , x_return_status => x_return_status
1083 , x_msg_count => x_msg_count
1084 , x_msg_data => x_msg_data
1085 , p_task_id => p_task_id
1086 , p_task_name => p_task_name
1087 , p_task_type_name => p_task_type_name
1088 , p_task_type_id => p_task_type_id
1089 , p_description => p_description
1090 , p_task_status_name => p_task_status_name
1091 , p_task_status_id => p_task_status_id
1092 , p_task_priority_name => p_task_priority_name
1093 , p_task_priority_id => p_task_priority_id
1094 , p_owner_type_name => p_owner_type_name
1095 , p_owner_type_code => p_owner_type_code
1096 , p_owner_id => p_owner_id
1097 , p_owner_territory_id => p_owner_territory_id
1098 , p_assigned_by_name => p_assigned_by_name
1099 , p_assigned_by_id => p_assigned_by_id
1100 , p_customer_number => p_customer_number
1101 , p_customer_id => p_customer_id
1102 , p_cust_account_number => p_cust_account_number
1103 , p_cust_account_id => p_cust_account_id
1104 , p_address_id => p_address_id
1105 , p_address_number => p_address_number
1106 , p_location_id => p_location_id
1107 , p_planned_start_date => p_planned_start_date
1108 , p_planned_end_date => p_planned_end_date
1109 , p_scheduled_start_date => p_scheduled_start_date
1110 , p_scheduled_end_date => p_scheduled_end_date
1111 , p_actual_start_date => p_actual_start_date
1112 , p_actual_end_date => p_actual_end_date
1113 , p_timezone_id => p_timezone_id
1114 , p_timezone_name => p_timezone_name
1115 , p_source_object_type_code => p_source_object_type_code
1116 , p_source_object_id => p_source_object_id
1117 , p_source_object_name => p_source_object_name
1118 , p_duration => p_duration
1119 , p_duration_uom => p_duration_uom
1120 , p_planned_effort => p_planned_effort
1121 , p_planned_effort_uom => p_planned_effort_uom
1122 , p_actual_effort => p_actual_effort
1123 , p_actual_effort_uom => p_actual_effort_uom
1124 , p_percentage_complete => p_percentage_complete
1125 , p_reason_code => p_reason_code
1126 , p_private_flag => p_private_flag
1127 , p_publish_flag => p_publish_flag
1128 , p_restrict_closure_flag => p_restrict_closure_flag
1129 , p_multi_booked_flag => p_multi_booked_flag
1130 , p_milestone_flag => p_milestone_flag
1131 , p_holiday_flag => p_holiday_flag
1132 , p_billable_flag => p_billable_flag
1133 , p_bound_mode_code => p_bound_mode_code
1134 , p_soft_bound_flag => p_soft_bound_flag
1135 , p_workflow_process_id => p_workflow_process_id
1136 , p_notification_flag => p_notification_flag
1137 , p_notification_period => p_notification_period
1138 , p_notification_period_uom => p_notification_period_uom
1139 , p_alarm_start => p_alarm_start
1140 , p_alarm_start_uom => p_alarm_start_uom
1141 , p_alarm_on => p_alarm_on
1142 , p_alarm_count => p_alarm_count
1143 , p_alarm_interval => p_alarm_interval
1144 , p_alarm_interval_uom => p_alarm_interval_uom
1145 , p_palm_flag => p_palm_flag
1146 , p_wince_flag => p_wince_flag
1147 , p_laptop_flag => p_laptop_flag
1148 , p_device1_flag => p_device1_flag
1149 , p_device2_flag => p_device2_flag
1150 , p_device3_flag => p_device3_flag
1151 , p_costs => p_costs
1152 , p_currency_code => p_currency_code
1153 , p_escalation_level => p_escalation_level
1154 , p_attribute1 => p_attribute1
1155 , p_attribute2 => p_attribute2
1156 , p_attribute3 => p_attribute3
1157 , p_attribute4 => p_attribute4
1158 , p_attribute5 => p_attribute5
1159 , p_attribute6 => p_attribute6
1160 , p_attribute7 => p_attribute7
1161 , p_attribute8 => p_attribute8
1162 , p_attribute9 => p_attribute9
1163 , p_attribute10 => p_attribute10
1164 , p_attribute11 => p_attribute11
1165 , p_attribute12 => p_attribute12
1166 , p_attribute13 => p_attribute13
1167 , p_attribute14 => p_attribute14
1168 , p_attribute15 => p_attribute15
1169 , p_attribute_category => p_attribute_category
1170 , p_task_assign_tbl => p_task_assign_tbl
1171 , p_task_depends_tbl => p_task_depends_tbl
1172 , p_task_rsrc_req_tbl => p_task_rsrc_req_tbl
1173 , p_task_refer_tbl => p_task_refer_tbl
1174 , p_task_dates_tbl => p_task_dates_tbl
1175 , p_task_notes_tbl => p_task_notes_tbl
1176 , p_task_recur_rec => p_task_recur_rec
1177 , p_task_contacts_tbl => p_task_contacts_tbl
1178 , p_date_selected => p_date_selected
1179 , p_category_id => p_category_id
1180 , p_show_on_calendar => p_show_on_calendar
1181 , p_owner_status_id => p_owner_status_id
1182 , p_template_id => p_template_id
1183 , p_template_group_id => p_template_group_id
1184 , p_enable_workflow => p_enable_workflow
1185 , p_abort_workflow => p_abort_workflow
1186 , p_task_split_flag => p_task_split_flag
1187 , p_parent_task_number => p_parent_task_number
1188 , p_parent_task_id => p_parent_task_id
1189 , p_child_position => p_child_position
1190 , p_child_sequence_num => p_child_sequence_num
1191 , x_task_id => x_task_id
1192 );
1193
1194 IF x_return_status = fnd_api.g_ret_sts_error THEN
1195 RAISE fnd_api.g_exc_error;
1196 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1197 RAISE fnd_api.g_exc_unexpected_error;
1198 END IF;
1199
1200
1201 CSF_TASKS_PUB.CREATE_ACC_HRS(
1202 p_task_id => x_task_id
1203 , x_return_status => x_return_status
1204 , x_msg_count => x_msg_count
1205 , x_msg_data => x_msg_data
1206 );
1207 IF x_return_status = fnd_api.g_ret_sts_error THEN
1208 RAISE fnd_api.g_exc_error;
1209 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1210 RAISE fnd_api.g_exc_unexpected_error;
1211 END IF;
1212 EXCEPTION
1213 WHEN fnd_api.g_exc_error THEN
1214 ROLLBACK TO csf_create_task_pub;
1215 x_return_status := fnd_api.g_ret_sts_error;
1216 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1217 WHEN fnd_api.g_exc_unexpected_error THEN
1218 ROLLBACK TO csf_create_task_pub;
1219 x_return_status := fnd_api.g_ret_sts_unexp_error;
1220 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1221 WHEN OTHERS THEN
1222 x_return_status := fnd_api.g_ret_sts_unexp_error;
1223 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1224 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1225 END IF;
1226 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1227 ROLLBACK TO csf_create_task_pub;
1228 END create_task;
1229
1230 PROCEDURE delete_task (
1231 p_api_version IN NUMBER
1232 , p_init_msg_list IN VARCHAR2
1233 , p_commit IN VARCHAR2
1234 , x_return_status OUT NOCOPY VARCHAR2
1235 , x_msg_count OUT NOCOPY NUMBER
1236 , x_msg_data OUT NOCOPY VARCHAR2
1237 , p_task_id IN NUMBER
1238 , p_task_number IN VARCHAR2
1239 , p_object_version_number IN NUMBER
1240 , p_delete_future_recurrences IN VARCHAR2
1241 ) IS
1242 BEGIN
1243 jtf_tasks_pub.delete_task (
1244 p_api_version => p_api_version
1245 , p_init_msg_list => p_init_msg_list
1246 , p_commit => p_commit
1247 , x_return_status => x_return_status
1248 , x_msg_count => x_msg_count
1249 , x_msg_data => x_msg_data
1250 , p_task_id => p_task_id
1251 , p_task_number => p_task_number
1252 , p_object_version_number => p_object_version_number
1253 , p_delete_future_recurrences => p_delete_future_recurrences
1254 );
1255 END delete_task;
1256
1257 /**
1258 *
1259 */
1260 PROCEDURE propagate_status_change(
1261 x_return_status OUT NOCOPY VARCHAR2
1262 , x_msg_count OUT NOCOPY NUMBER
1263 , x_msg_data OUT NOCOPY VARCHAR2
1264 , p_task_id IN NUMBER
1265 , p_object_version_number IN OUT NOCOPY NUMBER
1266 , p_new_task_status_id IN NUMBER
1267 , p_new_sts_cancelled_flag IN VARCHAR2
1268 , p_new_sts_closed_flag IN VARCHAR2
1269 ) IS
1270 -- Cursor to get the Task Assignments to be cancelled
1271 CURSOR c_cancel_task_assignments IS
1272 SELECT ta.task_assignment_id
1273 , ta.object_version_number
1274 FROM csf_ct_task_assignments ta
1275 , jtf_task_statuses_b ts
1276 WHERE ta.task_id = p_task_id
1277 AND ts.task_status_id = ta.assignment_status_id
1278 AND ( NVL (ts.working_flag, 'N') = 'Y'
1279 OR NVL (ts.accepted_flag, 'N') = 'Y'
1280 OR NVL (ts.on_hold_flag, 'N') = 'Y'
1281 OR NVL (ts.schedulable_flag, 'N') = 'Y'
1282 OR ( NVL(ts.assigned_flag, 'N') = 'Y'
1283 AND NVL(ts.closed_flag, 'N') <> 'Y'
1284 AND NVL(ts.approved_flag, 'N') <> 'Y'
1285 AND NVL(ts.completed_flag, 'N') <> 'Y'
1286 AND NVL(ts.rejected_flag, 'N') <> 'Y')
1287 );
1288
1289 -- Cursor to get the Closed Task Assignments
1290 CURSOR c_closed_task_assignments IS
1291 SELECT ta.task_assignment_id
1292 , ta.object_version_number
1293 , NVL (ts.closed_flag, 'N') closed_flag
1294 , NVL (ts.cancelled_flag, 'N') cancelled_flag
1295 , NVL (ts.completed_flag, 'N') completed_flag
1296 , NVL (ts.rejected_flag, 'N') rejected_flag
1297 FROM jtf_task_assignments ta, jtf_task_statuses_b ts
1298 WHERE ta.task_id = p_task_id
1299 AND ts.task_status_id = ta.assignment_status_id;
1300
1301 l_task_status_id NUMBER;
1302 l_task_ovn NUMBER;
1303 l_task_closure Varchar2(3) := fnd_profile.value_specific('CSF: Enforce_Task_Closure', fnd_global.user_id);
1304 BEGIN
1305 x_return_status := fnd_api.g_ret_sts_success;
1306
1307 IF p_new_sts_cancelled_flag = 'Y' THEN
1308 -- Cancel all the Open Task Assignments
1309 FOR v_task_assignment IN c_cancel_task_assignments LOOP
1310 csf_task_assignments_pub.update_assignment_status(
1311 p_api_version => 1.0
1312 , x_return_status => x_return_status
1313 , x_msg_count => x_msg_count
1314 , x_msg_data => x_msg_data
1315 , p_task_assignment_id => v_task_assignment.task_assignment_id
1316 , p_object_version_number => v_task_assignment.object_version_number
1317 , p_assignment_status_id => p_new_task_status_id
1318 , p_update_task => fnd_api.g_false
1319 , x_task_object_version_number => l_task_ovn
1320 , x_task_status_id => l_task_status_id
1321 );
1322
1323 IF x_return_status = fnd_api.g_ret_sts_error THEN
1324 RAISE fnd_api.g_exc_error;
1325 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1326 RAISE fnd_api.g_exc_unexpected_error;
1327 END IF;
1328 END LOOP;
1329 ELSIF p_new_sts_closed_flag = 'Y' THEN
1330 -- added an if condition for task closeure for the bug 8282570
1331 IF nvl(l_task_closure,'N') = 'Y'
1332 THEN
1333
1334 FOR v_task_assignment IN c_closed_task_assignments LOOP
1335
1336 /*
1337 * I didnt understand the significance of using CSFW: Update Schedulable Task
1338 * to check whether Debrief should be checked or not. The significance
1339 * of the profile is to govern whether Debrief can be invoked directly
1340 * without Scheduling the Task and not the other way round.
1341 * Therefore removed the logic - venjayar.
1342 */
1343
1344 -- Check whether the Task Assignment is still open.
1345 IF ( v_task_assignment.closed_flag = 'N'
1346 AND v_task_assignment.completed_flag = 'N'
1347 AND v_task_assignment.cancelled_flag = 'N'
1348 AND v_task_assignment.rejected_flag = 'N' )
1349 THEN
1350 fnd_message.set_name ('CSF', 'CSF_CLOSED_TASK');
1351 fnd_msg_pub.ADD;
1352 RAISE fnd_api.g_exc_error;
1353 END IF;
1354
1355 -- Task Assignment is not open. Check for Debrief
1356 IF NOT is_debrief_closed(v_task_assignment.task_assignment_id) THEN
1357 fnd_message.set_name('CSF', 'CSF_DEBRIEF_PENDING');
1358 fnd_msg_pub.ADD;
1359 RAISE fnd_api.g_exc_error;
1360 END IF;
1361
1362 -- All validations done. Close the Task Assignment
1363 jtf_task_assignments_pub.update_task_assignment(
1364 p_api_version => 1.0
1365 , x_return_status => x_return_status
1366 , x_msg_count => x_msg_count
1367 , x_msg_data => x_msg_data
1368 , p_task_assignment_id => v_task_assignment.task_assignment_id
1369 , p_object_version_number => v_task_assignment.object_version_number
1370 , p_assignment_status_id => p_new_task_status_id
1371 );
1372 IF x_return_status = fnd_api.g_ret_sts_error THEN
1373 RAISE fnd_api.g_exc_error;
1374 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1375 RAISE fnd_api.g_exc_unexpected_error;
1376 END IF;
1377 END LOOP;
1378
1379
1380 end if;
1381
1382 END IF;
1383 END propagate_status_change;
1384
1385 /**
1386 * Update the status of a Task and propagate to Task Assignments also.
1387 *
1388 * If the new Status of the Task is CANCELLED, then all Assignments which are open
1389 * (Working, Accepted, Assigned, In-Planning, Planned, On-Hold) needs to be
1390 * cancelled too. Other Assignments of the Task will not be updated.
1391 *
1392 * If the new Status of the Task is CLOSED, then we have to validate if the Task
1393 * can be closed. For this, there should not be any Open Task Assignments linked
1394 * to the Task. Moreover, if the Profile "CSFW: Update Schedulable Task" is set to
1395 * Yes, then the debrief linked with the Assignments should have been COMPLETED.
1396 * Otherwise Task cant be closed. If all verifications passes, then Task and the
1397 * open Assignments are closed.
1398 */
1399 PROCEDURE update_task_status (
1400 p_api_version IN NUMBER
1401 , p_init_msg_list IN VARCHAR2
1402 , p_commit IN VARCHAR2
1403 , p_validation_level IN NUMBER
1404 , x_return_status OUT NOCOPY VARCHAR2
1405 , x_msg_count OUT NOCOPY NUMBER
1406 , x_msg_data OUT NOCOPY VARCHAR2
1407 , p_task_id IN NUMBER
1408 , p_task_status_id IN NUMBER
1409 , p_object_version_number IN OUT NOCOPY NUMBER
1410 ) IS
1411 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_TASK_STATUS';
1412 l_api_version CONSTANT NUMBER := 1.0;
1413
1414 -- Fetch the information related to the given Task
1415 CURSOR c_task_info IS
1416 SELECT t.task_status_id
1417 , t.scheduled_start_date
1418 , t.scheduled_end_date
1419 , t.object_version_number
1420 , t.source_object_type_code
1421 FROM jtf_tasks_b t
1422 , jtf_task_statuses_b ts
1423 WHERE task_id = p_task_id
1424 AND ts.task_status_id = t.task_status_id;
1425
1426 -- Fetch the Flags corresponding to the new Task Status.
1427 CURSOR c_task_status_info IS
1428 SELECT NVL (ts.closed_flag, 'N') closed_flag
1429 , NVL (ts.cancelled_flag, 'N') cancelled_flag
1430 FROM jtf_task_statuses_b ts
1431 WHERE ts.task_status_id = p_task_status_id;
1432
1433
1434 l_task_info c_task_info%ROWTYPE;
1435 l_task_status_info c_task_status_info%ROWTYPE;
1436 BEGIN
1437 -- Standard start of API savepoint
1438 SAVEPOINT update_task_status_pub;
1439
1440 -- Standard call to check for call compatibility
1441 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1442 RAISE fnd_api.g_exc_unexpected_error;
1443 END IF;
1444
1445 -- Initialize message list if p_init_msg_list is set to TRUE
1446 IF fnd_api.to_boolean (p_init_msg_list) THEN
1447 fnd_msg_pub.initialize;
1448 END IF;
1449
1450 -- Initialize API return status to success
1451 x_return_status := fnd_api.g_ret_sts_success;
1452
1453 -- Validate if update in necessary and get old status_id just in case
1454 IF p_task_status_id = fnd_api.g_miss_num THEN
1455 RETURN;
1456 END IF;
1457
1458 OPEN c_task_info;
1459 FETCH c_task_info INTO l_task_info;
1460 CLOSE c_task_info;
1461
1462 -- No change in Task Status
1463 IF p_task_status_id = l_task_info.task_status_id THEN
1464 RETURN;
1465 END IF;
1466
1467 IF p_validation_level IS NULL OR p_validation_level = fnd_api.g_valid_level_full THEN
1468 validate_status_change(l_task_info.task_status_id, p_task_status_id);
1469 END IF;
1470
1471 OPEN c_task_status_info;
1472 FETCH c_task_status_info INTO l_task_status_info;
1473 CLOSE c_task_status_info;
1474
1475 IF l_task_status_info.cancelled_flag = 'Y' AND l_task_info.source_object_type_code = 'SR' THEN
1476 l_task_info.scheduled_start_date := NULL;
1477 l_task_info.scheduled_end_date := NULL;
1478 END IF;
1479
1480 -- Update the Task with the new Task Status Information
1481 jtf_tasks_pub.update_task (
1482 p_api_version => 1.0
1483 , x_return_status => x_return_status
1484 , x_msg_count => x_msg_count
1485 , x_msg_data => x_msg_data
1486 , p_task_id => p_task_id
1487 , p_object_version_number => p_object_version_number
1488 , p_task_status_id => p_task_status_id
1489 , p_scheduled_start_date => l_task_info.scheduled_start_date
1490 , p_scheduled_end_date => l_task_info.scheduled_end_date
1491 );
1492
1493 IF x_return_status = fnd_api.g_ret_sts_error THEN
1494 RAISE fnd_api.g_exc_error;
1495 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1496 RAISE fnd_api.g_exc_unexpected_error;
1497 END IF;
1498
1499 -- Propagate the change to other dependent objects
1500 propagate_status_change(
1501 x_return_status => x_return_status
1502 , x_msg_count => x_msg_count
1503 , x_msg_data => x_msg_data
1504 , p_task_id => p_task_id
1505 , p_object_version_number => p_object_version_number
1506 , p_new_task_status_id => p_task_status_id
1507 , p_new_sts_cancelled_flag => l_task_status_info.cancelled_flag
1508 , p_new_sts_closed_flag => l_task_status_info.closed_flag
1509 );
1510
1511 IF x_return_status = fnd_api.g_ret_sts_error THEN
1512 RAISE fnd_api.g_exc_error;
1513 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1514 RAISE fnd_api.g_exc_unexpected_error;
1515 END IF;
1516
1517 -- Standard check of p_commit
1518 IF fnd_api.to_boolean (p_commit) THEN
1519 COMMIT WORK;
1520 END IF;
1521
1522 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1523 EXCEPTION
1524 WHEN fnd_api.g_exc_error THEN
1525 ROLLBACK TO update_task_status_pub;
1526 x_return_status := fnd_api.g_ret_sts_error;
1527 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1528 WHEN fnd_api.g_exc_unexpected_error THEN
1529 ROLLBACK TO update_task_status_pub;
1530 x_return_status := fnd_api.g_ret_sts_unexp_error;
1531 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1532 WHEN OTHERS THEN
1533 x_return_status := fnd_api.g_ret_sts_unexp_error;
1534 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1535 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1536 END IF;
1537 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1538 ROLLBACK TO update_task_status_pub;
1539 END update_task_status;
1540
1541 PROCEDURE autoreject_task(
1542 p_api_version IN NUMBER
1543 , p_init_msg_list IN VARCHAR2
1544 , p_commit IN VARCHAR2
1545 , p_validation_level IN NUMBER
1546 , x_return_status OUT NOCOPY VARCHAR2
1547 , x_msg_count OUT NOCOPY NUMBER
1548 , x_msg_data OUT NOCOPY VARCHAR2
1549 , p_task_id IN NUMBER
1550 , p_task_status_id IN NUMBER
1551 , p_object_version_number IN OUT NOCOPY NUMBER
1552 , p_reject_message IN VARCHAR2
1553 ) IS
1554 l_api_name CONSTANT VARCHAR2 (30) := 'AUTOREJECT_TASK';
1555 l_api_version CONSTANT NUMBER := 1.0;
1556 BEGIN
1557 savepoint autoreject_task;
1558 csf_tasks_pub.update_task_status(
1559 p_api_version => 1
1560 , p_init_msg_list => fnd_api.g_true
1561 , p_commit => fnd_api.g_false
1562 , x_return_status => x_return_status
1563 , x_msg_count => x_msg_count
1564 , x_msg_data => x_msg_data
1565 , p_task_id => p_task_id
1566 , p_object_version_number => p_object_version_number
1567 , p_task_status_id => p_task_status_id);
1568
1569 IF x_return_status = fnd_api.g_ret_sts_error THEN
1570 RAISE fnd_api.g_exc_error;
1571 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1572 RAISE fnd_api.g_exc_unexpected_error;
1573 END IF;
1574
1575 UPDATE JTF_TASKS_TL
1576 SET rejection_message = p_reject_message
1577 WHERE task_id = p_task_id;
1578
1579 -- Standard check of p_commit
1580 IF fnd_api.to_boolean (p_commit) THEN
1581 COMMIT WORK;
1582 END IF;
1583
1584 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1585
1586 EXCEPTION
1587 WHEN fnd_api.g_exc_error THEN
1588 ROLLBACK TO autoreject_task;
1589 x_return_status := fnd_api.g_ret_sts_error;
1590 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1591 WHEN fnd_api.g_exc_unexpected_error THEN
1592 ROLLBACK TO autoreject_task;
1593 x_return_status := fnd_api.g_ret_sts_unexp_error;
1594 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1595 WHEN OTHERS THEN
1596 x_return_status := fnd_api.g_ret_sts_unexp_error;
1597 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1598 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1599 END IF;
1600 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1601 ROLLBACK TO autoreject_task;
1602 END autoreject_task;
1603
1604 PROCEDURE update_task(
1605 p_api_version IN NUMBER
1606 , p_init_msg_list IN VARCHAR2
1607 , p_commit IN VARCHAR2
1608 , p_validation_level IN NUMBER DEFAULT NULL
1609 , x_return_status OUT NOCOPY VARCHAR2
1610 , x_msg_count OUT NOCOPY NUMBER
1611 , x_msg_data OUT NOCOPY VARCHAR2
1612 , p_task_id IN NUMBER
1613 , p_object_version_number IN OUT NOCOPY NUMBER
1614 , p_task_number IN VARCHAR2
1615 , p_task_name IN VARCHAR2
1616 , p_description IN VARCHAR2
1617 , p_planned_start_date IN DATE
1618 , p_planned_end_date IN DATE
1619 , p_scheduled_start_date IN DATE
1620 , p_scheduled_end_date IN DATE
1621 , p_actual_start_date IN DATE
1622 , p_actual_end_date IN DATE
1623 , p_timezone_id IN NUMBER
1624 , p_source_object_type_code IN VARCHAR2
1625 , p_source_object_id IN NUMBER
1626 , p_source_object_name IN VARCHAR2
1627 , p_task_status_id IN NUMBER
1628 , p_task_type_id IN NUMBER
1629 , p_task_priority_id IN NUMBER
1630 , p_owner_type_code IN VARCHAR2
1631 , p_owner_id IN NUMBER
1632 , p_owner_territory_id IN NUMBER
1633 , p_owner_status_id IN NUMBER
1634 , p_assigned_by_id IN NUMBER
1635 , p_customer_id IN NUMBER
1636 , p_cust_account_id IN NUMBER
1637 , p_address_id IN NUMBER
1638 , p_location_id IN NUMBER
1639 , p_duration IN NUMBER
1640 , p_duration_uom IN VARCHAR2
1641 , p_planned_effort IN NUMBER
1642 , p_planned_effort_uom IN VARCHAR2
1643 , p_actual_effort IN NUMBER
1644 , p_actual_effort_uom IN VARCHAR2
1645 , p_percentage_complete IN NUMBER
1646 , p_reason_code IN VARCHAR2
1647 , p_private_flag IN VARCHAR2
1648 , p_publish_flag IN VARCHAR2
1649 , p_restrict_closure_flag IN VARCHAR2
1650 , p_attribute1 IN VARCHAR2
1651 , p_attribute2 IN VARCHAR2
1652 , p_attribute3 IN VARCHAR2
1653 , p_attribute4 IN VARCHAR2
1654 , p_attribute5 IN VARCHAR2
1655 , p_attribute6 IN VARCHAR2
1656 , p_attribute7 IN VARCHAR2
1657 , p_attribute8 IN VARCHAR2
1658 , p_attribute9 IN VARCHAR2
1659 , p_attribute10 IN VARCHAR2
1660 , p_attribute11 IN VARCHAR2
1661 , p_attribute12 IN VARCHAR2
1662 , p_attribute13 IN VARCHAR2
1663 , p_attribute14 IN VARCHAR2
1664 , p_attribute15 IN VARCHAR2
1665 , p_attribute_category IN VARCHAR2
1666 , p_date_selected IN VARCHAR2
1667 , p_category_id IN NUMBER
1668 , p_multi_booked_flag IN VARCHAR2
1669 , p_milestone_flag IN VARCHAR2
1670 , p_holiday_flag IN VARCHAR2
1671 , p_billable_flag IN VARCHAR2
1672 , p_bound_mode_code IN VARCHAR2
1673 , p_soft_bound_flag IN VARCHAR2
1674 , p_workflow_process_id IN NUMBER
1675 , p_notification_flag IN VARCHAR2
1676 , p_notification_period IN NUMBER
1677 , p_notification_period_uom IN VARCHAR2
1678 , p_alarm_start IN NUMBER
1679 , p_alarm_start_uom IN VARCHAR2
1680 , p_alarm_on IN VARCHAR2
1681 , p_alarm_count IN NUMBER
1682 , p_alarm_fired_count IN NUMBER
1683 , p_alarm_interval IN NUMBER
1684 , p_alarm_interval_uom IN VARCHAR2
1685 , p_palm_flag IN VARCHAR2
1686 , p_wince_flag IN VARCHAR2
1687 , p_laptop_flag IN VARCHAR2
1688 , p_device1_flag IN VARCHAR2
1689 , p_device2_flag IN VARCHAR2
1690 , p_device3_flag IN VARCHAR2
1691 , p_show_on_calendar IN VARCHAR2
1692 , p_costs IN NUMBER
1693 , p_currency_code IN VARCHAR2
1694 , p_escalation_level IN VARCHAR2
1695 , p_parent_task_id IN NUMBER
1696 , p_parent_task_number IN VARCHAR2
1697 , p_task_split_flag IN VARCHAR2
1698 , p_child_position IN VARCHAR2
1699 , p_child_sequence_num IN NUMBER
1700 , p_enable_workflow IN VARCHAR2
1701 , p_abort_workflow IN VARCHAR2
1702 , p_find_overlap IN VARCHAR2 DEFAULT NULL
1703 ) IS
1704 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_TASK';
1705 l_api_version CONSTANT NUMBER := 1.0;
1706
1707 l_new_start_date DATE;
1708 l_new_end_date DATE;
1709 l_planned_effort NUMBER;
1710 l_planned_effort_uom VARCHAR2(3);
1711 l_planned_effort_minutes NUMBER;
1712
1713 CURSOR c_overlap_tasks(p_trip_id NUMBER, p_start_date DATE, p_end_date DATE) IS
1714 SELECT NVL(TASK_NUMBER,TASK_ID) overlap_task_num
1715 FROM csr_trip_tasks_v
1716 WHERE object_capacity_id = p_trip_id
1717 AND task_id <> p_task_id
1718 AND NVL(actual_end_date,scheduled_end_date) >= p_start_date
1719 AND NVL(actual_start_date,scheduled_start_date) <= p_end_date;
1720
1721 CURSOR c_task_info IS
1722 SELECT t.scheduled_start_date
1723 , t.scheduled_end_date
1724 , CASE WHEN ta.actual_start_date IS NULL AND ta.actual_end_date IS NULL THEN 'N' ELSE 'Y' END is_visited
1725 , ta.resource_id
1726 , ta.resource_type_code
1727 , ta.object_capacity_id
1728 , nvl(ta.actual_effort,t.planned_effort) planned_effort
1729 , nvl(ta.actual_effort_uom,t.planned_effort_uom) planned_effort_uom
1730 , ta.task_assignment_id
1731 , ta.object_version_number
1732 , t.task_status_id
1733 , t.planned_start_date
1734 , t.planned_end_date
1735 , ta.assignment_status_id
1736 , t.task_split_flag
1737 , t.task_number
1738 FROM jtf_tasks_b t,
1739 (SELECT tas.actual_start_date
1740 , tas.actual_end_date
1741 , tas.resource_id
1742 , tas.resource_type_code
1743 , tas.object_capacity_id
1744 , tas.task_assignment_id
1745 , tas.object_version_number
1746 , tas.assignment_status_id
1747 , tas.task_id
1748 , tas.actual_effort
1749 , tas.actual_effort_uom
1750 FROM jtf_task_assignments tas, jtf_task_statuses_b ts
1751 WHERE task_id = p_task_id
1752 AND ts.task_status_id = tas.assignment_status_id
1753 AND NVL(ts.cancelled_flag, 'N') <> 'Y'
1754 AND NVL(ts.closed_flag, 'N') <> 'Y'
1755 AND NVL(ts.completed_flag, 'N') <> 'Y'
1756 AND NVL(ts.rejected_flag, 'N') <> 'Y'
1757 ) ta
1758 WHERE t.task_id = p_task_id
1759 AND t.task_id = ta.task_id(+)
1760 AND NVL(t.deleted_flag, 'N') <> 'Y';
1761
1762 -- Fetch the Flags corresponding to the new Task Status.
1763 CURSOR c_task_status_info IS
1764 SELECT NVL (ts.closed_flag, 'N') closed_flag
1765 , NVL (ts.cancelled_flag, 'N') cancelled_flag
1766 FROM jtf_task_statuses_b ts
1767 WHERE ts.task_status_id = p_task_status_id;
1768
1769 --The below cursor +variables are added for access hours 8869998
1770
1771 l_acc_hr_id NUMBER;
1772 l_acchr_loc_id NUMBER;
1773 l_acchr_ct_site_id NUMBER;
1774 l_acchr_ct_id NUMBER;
1775 l_acchrs_found BOOLEAN;
1776 l_address_id_to_pass NUMBER;
1777 l_location_id_to_pass NUMBER;
1778 conf_object_version_number NUMBER;
1779 x_object_version_number NUMBER;
1780 l_auto_acc_hrs VARCHAR2(1);
1781 l_task_status_flag VARCHAR2(1);
1782
1783 CURSOR c_acchrs_location_csr IS
1784 SELECT * from csf_map_access_hours_vl where
1785 customer_location_id = l_acchr_loc_id;
1786
1787 CURSOR c_acchrs_ctsite_csr IS
1788 SELECT * from csf_map_access_hours_vl where
1789 customer_id = l_acchr_ct_id and
1790 customer_site_id = l_acchr_ct_site_id;
1791
1792
1793 CURSOR c_acchrs_ct_csr IS
1794 SELECT * from csf_map_access_hours_vl where
1795 customer_id = l_acchr_ct_id
1796 and customer_site_id is NULL
1797 and customer_location_id is NULL;
1798 l_acchrs_setups_rec c_acchrs_location_csr%ROWTYPE;
1799
1800 CURSOR c_task_details IS
1801 SELECT t.task_number,
1802 t.location_id,
1803 t.address_id,
1804 t.customer_id,
1805 NVL(t.location_id, ps.location_id) loc_id
1806 from jtf_tasks_b t, hz_party_sites ps
1807 where task_id=p_task_id
1808 AND ps.party_site_id(+) = t.address_id;
1809 l_task_dtls c_task_details%rowtype;
1810
1811 CURSOR c_access_hrs_chk IS
1812 SELECT b.access_hour_id,nvl(b.DATA_CHANGED_FRM_UI,'N') DATA_CHANGED,
1813 b.ACCESSHOUR_REQUIRED,
1814 b.AFTER_HOURS_FLAG,
1815 t.DESCRIPTION
1816 FROM csf_access_hours_b b,csf_access_hours_tl t
1817 WHERE b.task_id=p_task_id
1818 and t.access_hour_id=b.access_hour_id
1819 and t.language=userenv('LANG');
1820
1821 l_acc_chk_info c_access_hrs_chk%rowtype;
1822 --end of cursor added access hours validation
1823
1824
1825
1826 l_task_info c_task_info%ROWTYPE;
1827 l_task_status_info c_task_status_info%ROWTYPE;
1828 l_overlap_tasks VARCHAR2(2000);
1829 l_trip_id NUMBER;
1830 l_task_object_version NUMBER;
1831 l_task_status_id NUMBER;
1832 l_task_number NUMBER;
1833
1834
1835 BEGIN
1836 SAVEPOINT csf_update_task;
1837
1838
1839
1840 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1841 RAISE fnd_api.g_exc_unexpected_error;
1842 END IF;
1843
1844 IF fnd_api.to_boolean (p_init_msg_list) THEN
1845 fnd_msg_pub.initialize;
1846 END IF;
1847
1848 x_return_status := fnd_api.g_ret_sts_success;
1849
1850 OPEN c_task_info;
1851 FETCH c_task_info INTO l_task_info;
1852 CLOSE c_task_info;
1853
1854 l_new_start_date := p_scheduled_start_date;
1855 l_new_end_date := p_scheduled_end_date;
1856
1857 IF p_validation_level = fnd_api.g_valid_level_full OR p_validation_level IS NULL THEN
1858
1859 -- Validate Task Status Change
1860 IF p_task_status_id <> fnd_api.g_miss_num THEN
1861 validate_status_change(l_task_info.task_status_id, p_task_status_id);
1862 END IF;
1863
1864 -- Validate Trip Information corresponding to new Scheduled Dates
1865 IF NVL(l_new_start_date,fnd_api.g_miss_date) <> fnd_api.g_miss_date
1866 OR NVL(l_new_end_date,fnd_api.g_miss_date) <> fnd_api.g_miss_date
1867 THEN
1868
1869 l_planned_effort := p_planned_effort;
1870 l_planned_effort_uom := p_planned_effort_uom;
1871
1872 IF l_planned_effort IS NULL OR l_planned_effort = fnd_api.g_miss_num THEN
1873 l_planned_effort := l_task_info.planned_effort;
1874 END IF;
1875 IF l_planned_effort_uom IS NULL OR l_planned_effort_uom = fnd_api.g_miss_char THEN
1876 l_planned_effort_uom := l_task_info.planned_effort_uom;
1877 END IF;
1878
1879 l_planned_effort_minutes := csf_util_pvt.convert_to_minutes(
1880 l_planned_effort
1881 , l_planned_effort_uom
1882 );
1883
1884 l_task_number := l_task_info.task_number;
1885
1886 IF l_task_info.task_split_flag <> 'M'
1887 AND l_planned_effort_minutes > CSR_SCHEDULER_PUB.GET_SCH_PARAMETER_VALUE('spDefaultShiftDuration')
1888 THEN
1889 fnd_message.set_name ('CSF', 'CSF_TASK_UPDATE_NOT_ALLOWED');
1890 fnd_message.set_token('TASK_NUMBER',l_task_number);
1891 fnd_msg_pub.add;
1892 RAISE fnd_api.g_exc_error;
1893 END IF;
1894
1895 IF l_task_info.task_assignment_id IS NOT NULL
1896 AND l_task_info.is_visited = 'N'
1897 AND ( l_task_info.scheduled_start_date <> nvl(l_new_start_date,fnd_api.g_miss_date)
1898 OR l_task_info.scheduled_end_date <> nvl(l_new_end_date,fnd_api.g_miss_date) )
1899 THEN
1900 IF l_new_start_date IS NULL OR l_new_start_date = fnd_api.g_miss_date THEN
1901 l_new_start_date := l_new_end_date - l_planned_effort_minutes / (24 * 60);
1902 END IF;
1903 IF l_new_end_date IS NULL OR l_new_end_date = fnd_api.g_miss_date THEN
1904 l_new_end_date := l_new_start_date + l_planned_effort_minutes / (24 * 60);
1905 END IF;
1906
1907 csf_trips_pub.find_trip(
1908 p_api_version => 1
1909 , p_init_msg_list => fnd_api.g_false
1910 , x_return_status => x_return_status
1911 , x_msg_data => x_msg_data
1912 , x_msg_count => x_msg_count
1913 , p_resource_id => l_task_info.resource_id
1914 , p_resource_type => l_task_info.resource_type_code
1915 , p_start_date_time => l_new_start_date
1916 , p_end_date_time => l_new_end_date
1917 , p_overtime_flag => fnd_api.g_true
1918 , x_trip_id => l_trip_id
1919 );
1920
1921 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1922 RAISE fnd_api.g_exc_unexpected_error;
1923 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1924 -- No Trip or Multiple Trips found for the given dates. Make Trip as NULL
1925 l_trip_id := NULL;
1926 END IF;
1927
1928 IF NVL(l_trip_id, -1) <> NVL(l_task_info.object_capacity_id,-1) THEN
1929 csf_task_assignments_pub.update_task_assignment(
1930 p_api_version => p_api_version
1931 , p_init_msg_list => p_init_msg_list
1932 , p_commit => fnd_api.g_false
1933 , p_validation_level => fnd_api.g_valid_level_none
1934 , x_return_status => x_return_status
1935 , x_msg_count => x_msg_count
1936 , x_msg_data => x_msg_data
1937 , p_task_assignment_id => l_task_info.task_assignment_id
1938 , p_object_version_number => l_task_info.object_version_number
1939 , p_object_capacity_id => l_trip_id
1940 , p_update_task => fnd_api.g_false
1941 , x_task_object_version_number => l_task_object_version
1942 , x_task_status_id => l_task_status_id
1943 );
1944
1945 IF x_return_status = fnd_api.g_ret_sts_error THEN
1946 RAISE fnd_api.g_exc_error;
1947 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1948 RAISE fnd_api.g_exc_unexpected_error;
1949 END IF;
1950 END IF;
1951
1952 IF fnd_api.to_boolean(p_find_overlap) THEN
1953 FOR v_overlap_tasks IN c_overlap_tasks(l_trip_id,l_new_start_date,l_new_end_date) LOOP
1954 l_overlap_tasks := l_overlap_tasks || fnd_global.local_chr(10) || v_overlap_tasks.overlap_task_num;
1955 END LOOP;
1956 END IF;
1957 END IF;
1958 END IF;
1959 END IF;
1960
1961 IF p_task_status_id <> fnd_api.g_miss_num AND l_task_info.task_status_id <> p_task_status_id THEN
1962 -- Clear the Scheduled Dates if the Task is Cancelled
1963 OPEN c_task_status_info;
1964 FETCH c_task_status_info INTO l_task_status_info;
1965 CLOSE c_task_status_info;
1966
1967 IF l_task_status_info.cancelled_flag = 'Y' THEN
1968 l_new_start_date := NULL;
1969 l_new_end_date := NULL;
1970 END IF;
1971 END IF;
1972
1973
1974
1975 jtf_tasks_pub.update_task(
1976 p_api_version => p_api_version
1977 , p_init_msg_list => p_init_msg_list
1978 , p_commit => fnd_api.g_false
1979 , x_return_status => x_return_status
1980 , x_msg_count => x_msg_count
1981 , x_msg_data => x_msg_data
1982 , p_task_id => p_task_id
1983 , p_object_version_number => p_object_version_number
1984 , p_task_number => p_task_number
1985 , p_task_name => p_task_name
1986 , p_description => p_description
1987 , p_task_status_id => p_task_status_id
1988 , p_planned_start_date => p_planned_start_date
1989 , p_planned_end_date => p_planned_end_date
1990 , p_scheduled_start_date => l_new_start_date
1991 , p_scheduled_end_date => l_new_end_date
1992 , p_actual_start_date => p_actual_start_date
1993 , p_actual_end_date => p_actual_end_date
1994 , p_timezone_id => p_timezone_id
1995 , p_source_object_type_code => p_source_object_type_code
1996 , p_source_object_id => p_source_object_id
1997 , p_source_object_name => p_source_object_name
1998 , p_task_type_id => p_task_type_id
1999 , p_task_priority_id => p_task_priority_id
2000 , p_owner_type_code => p_owner_type_code
2001 , p_owner_id => p_owner_id
2002 , p_owner_territory_id => p_owner_territory_id
2003 , p_owner_status_id => p_owner_status_id
2004 , p_assigned_by_id => p_assigned_by_id
2005 , p_customer_id => p_customer_id
2006 , p_cust_account_id => p_cust_account_id
2007 , p_address_id => p_address_id
2008 , p_location_id => p_location_id
2009 , p_duration => p_duration
2010 , p_duration_uom => p_duration_uom
2011 , p_planned_effort => p_planned_effort
2012 , p_planned_effort_uom => p_planned_effort_uom
2013 , p_actual_effort => p_actual_effort
2014 , p_actual_effort_uom => p_actual_effort_uom
2015 , p_percentage_complete => p_percentage_complete
2016 , p_reason_code => p_reason_code
2017 , p_private_flag => p_private_flag
2018 , p_publish_flag => p_publish_flag
2019 , p_restrict_closure_flag => p_restrict_closure_flag
2020 , p_attribute1 => p_attribute1
2021 , p_attribute2 => p_attribute2
2022 , p_attribute3 => p_attribute3
2023 , p_attribute4 => p_attribute4
2024 , p_attribute5 => p_attribute5
2025 , p_attribute6 => p_attribute6
2026 , p_attribute7 => p_attribute7
2027 , p_attribute8 => p_attribute8
2028 , p_attribute9 => p_attribute9
2029 , p_attribute10 => p_attribute10
2030 , p_attribute11 => p_attribute11
2031 , p_attribute12 => p_attribute12
2032 , p_attribute13 => p_attribute13
2033 , p_attribute14 => p_attribute14
2034 , p_attribute15 => p_attribute15
2035 , p_attribute_category => p_attribute_category
2036 , p_date_selected => p_date_selected
2037 , p_category_id => p_category_id
2038 , p_multi_booked_flag => p_multi_booked_flag
2039 , p_milestone_flag => p_milestone_flag
2040 , p_holiday_flag => p_holiday_flag
2041 , p_billable_flag => p_billable_flag
2042 , p_bound_mode_code => p_bound_mode_code
2043 , p_soft_bound_flag => p_soft_bound_flag
2044 , p_workflow_process_id => p_workflow_process_id
2045 , p_notification_flag => p_notification_flag
2046 , p_notification_period => p_notification_period
2047 , p_notification_period_uom => p_notification_period_uom
2048 , p_alarm_start => p_alarm_start
2049 , p_alarm_start_uom => p_alarm_start_uom
2050 , p_alarm_on => p_alarm_on
2051 , p_alarm_count => p_alarm_count
2052 , p_alarm_fired_count => p_alarm_fired_count
2053 , p_alarm_interval => p_alarm_interval
2054 , p_alarm_interval_uom => p_alarm_interval_uom
2055 , p_palm_flag => p_palm_flag
2056 , p_wince_flag => p_wince_flag
2057 , p_laptop_flag => p_laptop_flag
2058 , p_device1_flag => p_device1_flag
2059 , p_device2_flag => p_device2_flag
2060 , p_device3_flag => p_device3_flag
2061 , p_show_on_calendar => p_show_on_calendar
2062 , p_costs => p_costs
2063 , p_currency_code => p_currency_code
2064 , p_escalation_level => p_escalation_level
2065 , p_parent_task_id => p_parent_task_id
2066 , p_parent_task_number => p_parent_task_number
2067 , p_task_split_flag => p_task_split_flag
2068 , p_child_position => p_child_position
2069 , p_child_sequence_num => p_child_sequence_num
2070 , p_enable_workflow => p_enable_workflow
2071 , p_abort_workflow => p_abort_workflow
2072 );
2073
2074 IF x_return_status = fnd_api.g_ret_sts_error THEN
2075 RAISE fnd_api.g_exc_error;
2076 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2077 RAISE fnd_api.g_exc_unexpected_error;
2078 END IF;
2079
2080
2081
2082 -- Propagate the Task Status Change to other dependent Objects.
2083 IF p_task_status_id <> fnd_api.g_miss_num THEN
2084 propagate_status_change(
2085 x_return_status => x_return_status
2086 , x_msg_count => x_msg_count
2087 , x_msg_data => x_msg_data
2088 , p_task_id => p_task_id
2089 , p_object_version_number => p_object_version_number
2090 , p_new_task_status_id => p_task_status_id
2091 , p_new_sts_cancelled_flag => l_task_status_info.cancelled_flag
2092 , p_new_sts_closed_flag => l_task_status_info.closed_flag
2093 );
2094 IF x_return_status = fnd_api.g_ret_sts_error THEN
2095 RAISE fnd_api.g_exc_error;
2096 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2097 RAISE fnd_api.g_exc_unexpected_error;
2098 END IF;
2099 END IF;
2100 l_auto_acc_hrs := fnd_profile.value('CSF_AUTO_POPULATE_ACCESS_HRS');
2101 /*
2102 1) Check if access hours setups are done for the location
2103 2) Else, check if access hours setups are done for the ct + ct site combination
2104 3) Else, check if access hours setups are done for the ct
2105 4) Create access hours for the task, if acc hrs setups are found for the just created task
2106 */
2107 IF l_auto_acc_hrs ='Y'
2108 THEN
2109 OPEN c_task_details;
2110 FETCH c_task_details into l_task_dtls;
2111 l_acchr_ct_id := l_task_dtls.customer_id;
2112
2113 IF (l_task_dtls.location_id IS NOT NULL) THEN
2114 l_acchr_loc_id := l_task_dtls.location_id;
2115 OPEN c_acchrs_location_csr;
2116 FETCH c_acchrs_location_csr INTO l_acchrs_setups_rec;
2117 IF (c_acchrs_location_csr%NOTFOUND) THEN
2118 OPEN c_acchrs_ct_csr;
2119 FETCH c_acchrs_ct_csr INTO l_acchrs_setups_rec;
2120 IF (c_acchrs_ct_csr%NOTFOUND) THEN
2121 l_acchrs_found := false;
2122 ELSE
2123 l_acchrs_found := true;
2124 END IF;
2125 close c_acchrs_ct_csr;
2126 ELSE
2127 l_acchrs_found := true;
2128 END IF;
2129 close c_acchrs_location_csr;
2130 ELSIF(l_task_dtls.ADDRESS_ID IS NOT NULL) THEN
2131 l_acchr_ct_site_id := l_task_dtls.address_id;
2132 OPEN c_acchrs_ctsite_csr;
2133 FETCH c_acchrs_ctsite_csr INTO l_acchrs_setups_rec;
2134 IF (c_acchrs_ctsite_csr%NOTFOUND) THEN
2135 OPEN c_acchrs_ct_csr;
2136 FETCH c_acchrs_ct_csr INTO l_acchrs_setups_rec;
2137 IF (c_acchrs_ct_csr%NOTFOUND) THEN
2138 l_acchrs_found := false;
2139 ELSE
2140 l_acchrs_found := true;
2141 END IF;
2142 close c_acchrs_ct_csr;
2143 ELSE
2144 l_acchrs_found := true;
2145 END IF;
2146 close c_acchrs_ctsite_csr;
2147 END IF;
2148 IF (l_acchrs_found = true)
2149 THEN
2150
2151 OPEN c_access_hrs_chk;
2152 FETCH c_access_hrs_chk into l_acc_chk_info;
2153 CLOSE c_access_hrs_chk;
2154
2155 IF l_acc_chk_info.DATA_CHANGED ='N'
2156 THEN
2157
2158 l_task_status_flag := csf_access_hours_pub.get_task_status_flag(p_task_id);
2159
2160 /*:('Inside condition ');
2161 test('Access hour in table :'||l_acc_chk_info.ACCESSHOUR_REQUIRED);
2162 test('Description :'||l_acchrs_setups_rec.DESCRIPTION);
2163 */
2164 IF l_task_status_flag = 'S'
2165 THEN
2166 CSF_ACCESS_HOURS_PUB.UPDATE_ACCESS_HOURS(
2167 p_ACCESS_HOUR_ID => l_acc_chk_info.access_hour_id,
2168 p_API_VERSION => 1.0 ,
2169 p_init_msg_list => NULL,
2170 p_TASK_ID => p_task_id,
2171 p_ACCESS_HOUR_REQD => l_acchrs_setups_rec.accesshour_required,
2172 p_AFTER_HOURS_FLAG => l_acchrs_setups_rec.after_hours_flag,
2173 p_MONDAY_FIRST_START => l_acchrs_setups_rec.MONDAY_FIRST_START,
2174 p_MONDAY_FIRST_END => l_acchrs_setups_rec.MONDAY_FIRST_END,
2175 p_MONDAY_SECOND_START => l_acchrs_setups_rec.MONDAY_SECOND_START,
2176 p_MONDAY_SECOND_END => l_acchrs_setups_rec.MONDAY_SECOND_END,
2177 p_TUESDAY_FIRST_START => l_acchrs_setups_rec.TUESDAY_FIRST_START,
2178 p_TUESDAY_FIRST_END => l_acchrs_setups_rec.TUESDAY_FIRST_END,
2179 p_TUESDAY_SECOND_START => l_acchrs_setups_rec.TUESDAY_SECOND_START,
2180 p_TUESDAY_SECOND_END => l_acchrs_setups_rec.TUESDAY_SECOND_END,
2181 p_WEDNESDAY_FIRST_START => l_acchrs_setups_rec.WEDNESDAY_FIRST_START,
2182 p_WEDNESDAY_FIRST_END => l_acchrs_setups_rec.WEDNESDAY_FIRST_END,
2183 p_WEDNESDAY_SECOND_START => l_acchrs_setups_rec.WEDNESDAY_SECOND_START,
2184 p_WEDNESDAY_SECOND_END => l_acchrs_setups_rec.WEDNESDAY_SECOND_END,
2185 p_THURSDAY_FIRST_START => l_acchrs_setups_rec.THURSDAY_FIRST_START,
2186 p_THURSDAY_FIRST_END => l_acchrs_setups_rec.THURSDAY_FIRST_END,
2187 p_THURSDAY_SECOND_START => l_acchrs_setups_rec.THURSDAY_SECOND_START,
2188 p_THURSDAY_SECOND_END => l_acchrs_setups_rec.THURSDAY_SECOND_END,
2189 p_FRIDAY_FIRST_START => l_acchrs_setups_rec.FRIDAY_FIRST_START,
2190 p_FRIDAY_FIRST_END => l_acchrs_setups_rec.FRIDAY_FIRST_END,
2191 p_FRIDAY_SECOND_START => l_acchrs_setups_rec.FRIDAY_SECOND_START,
2192 p_FRIDAY_SECOND_END => l_acchrs_setups_rec.FRIDAY_SECOND_END,
2193 p_SATURDAY_FIRST_START => l_acchrs_setups_rec.SATURDAY_FIRST_START,
2194 p_SATURDAY_FIRST_END => l_acchrs_setups_rec.SATURDAY_FIRST_END,
2195 p_SATURDAY_SECOND_START => l_acchrs_setups_rec.SATURDAY_SECOND_START,
2196 p_SATURDAY_SECOND_END => l_acchrs_setups_rec.SATURDAY_SECOND_END,
2197 p_SUNDAY_FIRST_START => l_acchrs_setups_rec.SUNDAY_FIRST_START,
2198 p_SUNDAY_FIRST_END => l_acchrs_setups_rec.SUNDAY_FIRST_END,
2199 p_SUNDAY_SECOND_START => l_acchrs_setups_rec.SUNDAY_SECOND_START,
2200 p_SUNDAY_SECOND_END => l_acchrs_setups_rec.SUNDAY_SECOND_END,
2201 p_DESCRIPTION => nvl(l_acchrs_setups_rec.DESCRIPTION,' '),
2202 px_object_version_number => x_object_version_number,
2203 p_CREATED_BY => null,
2204 p_CREATION_DATE => null,
2205 p_LAST_UPDATED_BY => null,
2206 p_LAST_UPDATE_DATE => null,
2207 p_LAST_UPDATE_LOGIN => null,
2208 x_return_status => x_return_status,
2209 x_msg_count => x_msg_count,
2210 x_msg_data => x_msg_data );
2211 ELSIF l_task_status_flag = 'A'
2212 THEN
2213 /*test('Inside condition A');
2214 test('Access hour in table :'||l_acc_chk_info.ACCESSHOUR_REQUIRED);
2215 test('Access hour from PM table :'||l_acchrs_setups_rec.accesshour_required);
2216 test('After hour in table :'||l_acc_chk_info.AFTER_HOURS_FLAG);
2217 test('After hour from PM table :'||l_acchrs_setups_rec.after_hours_flag);
2218 test('Description in table :'||nvl(l_acc_chk_info.DESCRIPTION,'N' ));
2219 test('Description from PM table :'||nvl(l_acchrs_setups_rec.DESCRIPTION,'N' ));
2220 */
2221 IF ((l_acc_chk_info.ACCESSHOUR_REQUIRED ='Y' and l_acchrs_setups_rec.accesshour_required ='N')
2222 OR (l_acc_chk_info.AFTER_HOURS_FLAG ='Y' and l_acchrs_setups_rec.after_hours_flag='N')
2223 OR (nvl(l_acc_chk_info.DESCRIPTION,'N' )<>nvl(l_acchrs_setups_rec.DESCRIPTION,'N' )))
2224 THEN
2225 CSF_ACCESS_HOURS_PUB.UPDATE_ACCESS_HOURS(
2226 p_ACCESS_HOUR_ID => l_acc_chk_info.access_hour_id,
2227 p_API_VERSION => 1.0 ,
2228 p_init_msg_list => NULL,
2229 p_TASK_ID => p_task_id,
2230 p_ACCESS_HOUR_REQD => l_acchrs_setups_rec.accesshour_required,
2231 p_AFTER_HOURS_FLAG => l_acchrs_setups_rec.after_hours_flag,
2232 p_MONDAY_FIRST_START => NULL,
2233 p_MONDAY_FIRST_END => NULL,
2234 p_MONDAY_SECOND_START => NULL,
2235 p_MONDAY_SECOND_END => NULL,
2236 p_TUESDAY_FIRST_START => NULL,
2237 p_TUESDAY_FIRST_END => NULL,
2238 p_TUESDAY_SECOND_START => NULL,
2239 p_TUESDAY_SECOND_END => NULL,
2240 p_WEDNESDAY_FIRST_START => NULL,
2241 p_WEDNESDAY_FIRST_END => NULL,
2242 p_WEDNESDAY_SECOND_START => NULL,
2243 p_WEDNESDAY_SECOND_END => NULL,
2244 p_THURSDAY_FIRST_START => NULL,
2245 p_THURSDAY_FIRST_END => NULL,
2246 p_THURSDAY_SECOND_START => NULL,
2247 p_THURSDAY_SECOND_END => NULL,
2248 p_FRIDAY_FIRST_START => NULL,
2249 p_FRIDAY_FIRST_END => NULL,
2250 p_FRIDAY_SECOND_START => NULL,
2251 p_FRIDAY_SECOND_END => NULL,
2252 p_SATURDAY_FIRST_START => NULL,
2253 p_SATURDAY_FIRST_END => NULL,
2254 p_SATURDAY_SECOND_START => NULL,
2255 p_SATURDAY_SECOND_END => NULL,
2256 p_SUNDAY_FIRST_START => NULL,
2257 p_SUNDAY_FIRST_END => NULL,
2258 p_SUNDAY_SECOND_START => NULL,
2259 p_SUNDAY_SECOND_END => NULL,
2260 p_DESCRIPTION => nvl(l_acchrs_setups_rec.DESCRIPTION,' '),
2261 px_object_version_number => x_object_version_number,
2262 p_CREATED_BY => null,
2263 p_CREATION_DATE => null,
2264 p_LAST_UPDATED_BY => null,
2265 p_LAST_UPDATE_DATE => null,
2266 p_LAST_UPDATE_LOGIN => null,
2267 x_return_status => x_return_status,
2268 x_msg_count => x_msg_count,
2269 x_msg_data => x_msg_data );
2270 END IF;
2271 END IF;
2272
2273 /*fnd_message.set_name('CSF','CSF_TASK_ACC_UPDATE_ERROR');
2274 fnd_message.set_token('VALUE',l_task_dtls.task_number);
2275 fnd_msg_pub.add;
2276 Add_Err_Msg;*/
2277 IF x_return_status = fnd_api.g_ret_sts_error THEN
2278 RAISE fnd_api.g_exc_error;
2279 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2280 RAISE fnd_api.g_exc_unexpected_error;
2281 END IF;
2282 END IF;
2283 END IF;
2284
2285 END IF;-- This endif is for l_auto_acc_hrs profile check
2286 /* VAKULKAR - end - changes to associate access hours to the tasks */
2287 IF fnd_api.to_boolean (p_commit) THEN
2288 COMMIT WORK;
2289 END IF;
2290
2291 IF l_overlap_tasks IS NOT NULL THEN
2292 fnd_message.set_name('CSR','CSR_TASK_OVERLAP');
2293 fnd_message.set_token('TASKID', l_task_number);
2294 fnd_message.set_token('TASKS',l_overlap_tasks);
2295 fnd_msg_pub.add;
2296 END IF;
2297
2298 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2299
2300
2301 EXCEPTION
2302 WHEN fnd_api.g_exc_error THEN
2303 ROLLBACK TO csf_update_task;
2304 x_return_status := fnd_api.g_ret_sts_error;
2305 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2306 WHEN fnd_api.g_exc_unexpected_error THEN
2307 ROLLBACK TO csf_update_task;
2308 x_return_status := fnd_api.g_ret_sts_unexp_error;
2309 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2310 WHEN OTHERS THEN
2311 x_return_status := fnd_api.g_ret_sts_unexp_error;
2312 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2313 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2314 END IF;
2315 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2316 ROLLBACK TO csf_update_task;
2317 END update_task;
2318
2319 PROCEDURE commit_task (
2320 p_api_version IN NUMBER
2321 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
2322 , p_commit IN VARCHAR2 DEFAULT NULL
2323 , x_return_status OUT NOCOPY VARCHAR2
2324 , x_msg_data OUT NOCOPY VARCHAR2
2325 , x_msg_count OUT NOCOPY NUMBER
2326 , p_task_id IN NUMBER
2327 , p_resource_id IN NUMBER DEFAULT NULL --bug 6647019
2328 , p_resource_type IN VARCHAR2 DEFAULT NULL
2329 ) IS
2330 l_api_name CONSTANT VARCHAR2(30) := 'COMMIT_TASK';
2331 l_api_version CONSTANT NUMBER := 1.0;
2332 l_new_status_id CONSTANT NUMBER := g_assigned;
2333
2334 -- Cursor to get the Task Details
2335 CURSOR c_task_details IS
2336 SELECT t.task_number
2337 , t.task_status_id
2338 , t.object_version_number
2339 , t.scheduled_start_date
2340 , t.scheduled_end_date
2341 , NVL (t.task_confirmation_status, 'N') task_confirmation_status
2342 , ta.task_assignment_id
2343 , ta.object_version_number ta_object_version_number
2344 , ta.object_capacity_id
2345 , ta.assignment_status_id
2346 , cac.status trip_status
2347 , ta.resource_id
2348 , ta.resource_type_code
2349 FROM jtf_tasks_b t
2350 , jtf_task_assignments ta
2351 , jtf_task_statuses_b ts
2352 , cac_sr_object_capacity cac
2353 WHERE t.task_id = p_task_id
2354 AND ta.task_id = t.task_id
2355 AND ts.task_status_id = ta.assignment_status_id
2356 AND NVL (ts.assigned_flag, 'N') <> 'Y'
2357 AND NVL (ts.working_flag, 'N') <> 'Y'
2358 AND NVL (ts.completed_flag, 'N') <> 'Y'
2359 AND NVL (ts.closed_flag, 'N') <> 'Y'
2360 AND NVL (ts.cancelled_flag, 'N') <> 'Y'
2361 AND cac.object_capacity_id (+) = ta.object_capacity_id;
2362
2363 -- Cursor added for bug 6647019 by modifying cursor c_task_details. Added
2364 -- check for p_resource_id and p_resource_type
2365 -- Cursor to get the Task Details
2366 CURSOR c_task_details_1 IS
2367 SELECT t.task_number
2368 , t.task_status_id
2369 , t.object_version_number
2370 , t.scheduled_start_date
2371 , t.scheduled_end_date
2372 , NVL (t.task_confirmation_status, 'N') task_confirmation_status
2373 , ta.task_assignment_id
2374 , ta.object_version_number ta_object_version_number
2375 , ta.object_capacity_id
2376 , ta.assignment_status_id
2377 , cac.status trip_status
2378 , ta.resource_id
2379 , ta.resource_type_code
2380 FROM jtf_tasks_b t
2381 , jtf_task_assignments ta
2382 , jtf_task_statuses_b ts
2383 , cac_sr_object_capacity cac
2384 WHERE t.task_id = p_task_id
2385 AND ta.task_id = t.task_id
2386 AND ts.task_status_id = ta.assignment_status_id
2387 AND NVL (ts.assigned_flag, 'N') <> 'Y'
2388 AND NVL (ts.working_flag, 'N') <> 'Y'
2389 AND NVL (ts.completed_flag, 'N') <> 'Y'
2390 AND NVL (ts.closed_flag, 'N') <> 'Y'
2391 AND NVL (ts.cancelled_flag, 'N') <> 'Y'
2392 AND cac.object_capacity_id (+) = ta.object_capacity_id
2393 AND ta.resource_id = p_resource_id
2394 AND ta.resource_type_code = p_resource_type;
2395
2396
2397 CURSOR c_resource_tp(l_resource_id number, l_resource_type_code VARCHAR2)
2398 IS
2399 SELECT 'X'
2400 FROM JTF_RS_DEFRESROLES_VL A,
2401 JTF_RS_ALL_RESOURCES_VL B,
2402 JTF_RS_ROLES_B D
2403 WHERE A.ROLE_RESOURCE_ID =l_resource_id
2404 AND B.RESOURCE_ID = A.ROLE_RESOURCE_ID
2405 AND B.RESOURCE_TYPE =l_resource_type_code
2406 AND D.ROLE_ID = A.ROLE_ID
2407 AND A.ROLE_TYPE_CODE ='CSF_THIRD_PARTY'
2408 AND NVL( A.DELETE_FLAG, 'N') = 'N'
2409 AND (SYSDATE >= TRUNC (A.RES_RL_START_DATE) OR A.RES_RL_START_DATE IS NULL)
2410 AND (SYSDATE <= TRUNC (A.RES_RL_END_DATE) + 1 OR A.RES_RL_END_DATE IS NULL)
2411 AND D.ROLE_CODE IN ('CSF_THIRD_PARTY_SERVICE_PROVID','CSF_THIRD_PARTY_ADMINISTRATOR');
2412
2413 l_task_details c_task_details%ROWTYPE;
2414 l_trans_valid VARCHAR2(1);
2415 l_valid_statuses VARCHAR2(2000);
2416 l_resource_tp varchar2(1);
2417 l_resource_id number;
2418 l_resource_type_code VARCHAR2(100);
2419 BEGIN
2420 SAVEPOINT csf_commit_task;
2421
2422 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2423 RAISE fnd_api.g_exc_unexpected_error;
2424 END IF;
2425
2426 IF fnd_api.to_boolean (p_init_msg_list) THEN
2427 fnd_msg_pub.initialize;
2428 END IF;
2429
2430 x_return_status := fnd_api.g_ret_sts_success;
2431
2432 -- Fetch the Task Information
2433
2434 IF p_resource_id is null or p_resource_type is null --condition added for bug 6647019
2435 THEN
2436 OPEN c_task_details;
2437 FETCH c_task_details INTO l_task_details;
2438 IF c_task_details%NOTFOUND THEN
2439 CLOSE c_task_details;
2440 fnd_message.set_name ('CSF', 'CSF_AUTO_COMMIT_TASK_STATUS');
2441 fnd_message.set_token ('P_TASK_NUMBER', task_number(p_task_id));
2442 fnd_msg_pub.ADD;
2443 RAISE fnd_api.g_exc_error;
2444 END IF;
2445 CLOSE c_task_details;
2446 ELSE
2447 OPEN c_task_details_1;
2448 FETCH c_task_details_1 INTO l_task_details;
2449 IF c_task_details_1%NOTFOUND THEN
2450 CLOSE c_task_details_1;
2451 fnd_message.set_name ('CSF', 'CSF_AUTO_COMMIT_TASK_STATUS');
2452 fnd_message.set_token ('P_TASK_NUMBER', task_number(p_task_id));
2453 fnd_msg_pub.ADD;
2454 RAISE fnd_api.g_exc_error;
2455 END IF;
2456 CLOSE c_task_details_1;
2457 END IF;
2458
2459 -- Trip should not be in Blocked Status
2460 IF l_task_details.trip_status = csf_trips_pub.g_trip_unavailable THEN
2461 fnd_message.set_name ('CSF', 'CSF_AUTO_COMMIT_TRIP_BLOCK');
2462 fnd_message.set_token ('P_TASK_NUMBER', l_task_details.task_number);
2463 fnd_msg_pub.ADD;
2464 RAISE fnd_api.g_exc_error;
2465 END IF;
2466
2467 -- Validate Status Transition
2468 l_trans_valid := validate_state_transition ('TASK_STATUS', l_task_details.assignment_status_id, l_new_status_id);
2469 IF l_trans_valid = fnd_api.g_false THEN
2470 l_valid_statuses := get_valid_statuses ('TASK_STATUS', l_task_details.assignment_status_id);
2471 IF l_valid_statuses IS NULL THEN
2472 fnd_message.set_name ('CSF', 'CSF_NO_STATE_TRANSITION');
2473 ELSE
2474 fnd_message.set_name ('CSF', 'CSF_INVALID_STATE_TRANSITION');
2475 fnd_message.set_token ('P_VALID_STATUSES', l_valid_statuses);
2476 END IF;
2477 fnd_message.set_token ('P_NEW_STATUS', get_task_status_name (l_new_status_id));
2478 fnd_msg_pub.ADD;
2479 RAISE fnd_api.g_exc_error;
2480 END IF;
2481
2482 -- Check the Customer Confirmation Status - Should be either No or Received
2483 IF l_task_details.task_confirmation_status = 'R' THEN
2484 l_resource_id := nvl(p_resource_id,l_task_details.resource_id);
2485 l_resource_type_code := nvl(p_resource_type,l_task_details.resource_type_code);
2486 OPEN c_resource_tp(l_resource_id,l_resource_type_code);
2487 FETCH c_resource_tp into l_resource_tp;
2488 CLOSE c_resource_tp;
2489 IF l_resource_tp IS NULL
2490 THEN
2491 fnd_message.set_name ('CSF', 'CSF_AUTO_COMMIT_CUST_CONF');
2492 fnd_message.set_token ('P_TASK_NUMBER', l_task_details.task_number);
2493 fnd_msg_pub.ADD;
2494 RAISE fnd_api.g_exc_error;
2495 END IF;
2496 END IF;
2497
2498 -- Check for Scheduled Dates
2499 IF l_task_details.scheduled_start_date IS NULL THEN
2500 fnd_message.set_name ('CSF', 'CSF_AUTO_COMMIT_TASK_SCHE');
2501 fnd_message.set_token ('P_TASK_NUMBER', l_task_details.task_number);
2502 fnd_msg_pub.ADD;
2503 RAISE fnd_api.g_exc_error;
2504 END IF;
2505
2506 csf_task_assignments_pub.update_assignment_status(
2507 p_api_version => 1.0
2508 , p_validation_level => fnd_api.g_valid_level_none
2509 , p_init_msg_list => fnd_api.g_false
2510 , p_commit => fnd_api.g_false
2511 , x_return_status => x_return_status
2512 , x_msg_count => x_msg_count
2513 , x_msg_data => x_msg_data
2514 , p_task_assignment_id => l_task_details.task_assignment_id
2515 , p_assignment_status_id => l_new_status_id
2516 , p_object_version_number => l_task_details.ta_object_version_number
2517 , x_task_object_version_number => l_task_details.object_version_number
2518 , x_task_status_id => l_task_details.task_status_id
2519 );
2520
2521 IF x_return_status = fnd_api.g_ret_sts_success THEN
2522 -- commented for the bug 6801965
2523 -- Committed Task Message is added to the message stack
2524 -- fnd_message.set_name ('CSF', 'CSF_AUTO_COMMITTED');
2525 -- fnd_message.set_token ('P_TASK_NUMBER', l_task_details.task_number);
2526 -- fnd_msg_pub.ADD;
2527 RETURN;
2528 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2529 RAISE fnd_api.g_exc_error;
2530 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2531 RAISE fnd_api.g_exc_unexpected_error;
2532 END IF;
2533
2534 -- Standard check of p_commit
2535 IF fnd_api.to_boolean (p_commit) THEN
2536 COMMIT WORK;
2537 END IF;
2538
2539 EXCEPTION
2540 WHEN fnd_api.g_exc_error THEN
2541 ROLLBACK TO csf_commit_task;
2542 x_return_status := fnd_api.g_ret_sts_error;
2543 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2544 WHEN fnd_api.g_exc_unexpected_error THEN
2545 ROLLBACK TO csf_commit_task;
2546 x_return_status := fnd_api.g_ret_sts_unexp_error;
2547 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2548 WHEN OTHERS THEN
2549 x_return_status := fnd_api.g_ret_sts_unexp_error;
2550 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2551 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2552 END IF;
2553 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2554 ROLLBACK TO csf_commit_task;
2555 END commit_task;
2556
2557 PROCEDURE commit_schedule (
2558 p_api_version IN NUMBER
2559 , p_init_msg_list IN VARCHAR2
2560 , p_commit IN VARCHAR2
2561 , x_return_status OUT NOCOPY VARCHAR2
2562 , x_msg_count OUT NOCOPY NUMBER
2563 , x_msg_data OUT NOCOPY VARCHAR2
2564 , p_resource_id IN NUMBER
2565 , p_resource_type IN VARCHAR2
2566 , p_scheduled_start_date IN DATE
2567 , p_scheduled_end_date IN DATE
2568 , p_query_id IN NUMBER
2569 , p_trip_id IN NUMBER
2570 , p_task_id IN NUMBER
2571 , p_task_source IN VARCHAR2
2572 , p_commit_horizon IN NUMBER
2573 , p_commit_horizon_uom IN VARCHAR2
2574 , p_from_task_id IN NUMBER
2575 , p_to_task_id IN NUMBER
2576 , p_commit_horizon_from IN NUMBER
2577 , p_commit_uom_from IN VARCHAR2
2578 ) IS
2579 l_api_name CONSTANT VARCHAR2(30) := 'COMMIT_SCHEDULE';
2580 l_api_version CONSTANT NUMBER := 1.0;
2581
2582
2583
2584 p_res_id jtf_number_table ;
2585 p_res_type jtf_varchar2_table_2000 ;
2586 p_res_name jtf_varchar2_table_2000 ;
2587 p_res_typ_name jtf_varchar2_table_2000 ;
2588 p_res_key jtf_varchar2_table_2000 ;
2589
2590 l_field_uom_val varchar2(30);
2591 l_base_unit_uom varchar2(10);
2592 l_con_val number;
2593
2594 l_convert_dur_to_day NUMBER;
2595 l_start_date DATE;
2596 l_end_date DATE;
2597 l_commit_horizon BOOLEAN:=FALSE;
2598
2599
2600 CURSOR C_Terr_Resource
2601 IS SELECT DISTINCT TR.RESOURCE_ID RESOURCE_ID,
2602 TR.RESOURCE_TYPE RESOURCE_TYPE,
2603 TR.RESOURCE_NAME RESOURCE_NAME,
2604 CSF_GANTT_DATA_PKG.GET_RESOURCE_TYPE_NAME( TR.RESOURCE_TYPE ) RESOURCE_TYPE_NAME,
2605 TR.RESOURCE_ID||'-'||TR.RESOURCE_TYPE
2606 FROM CSF_SELECTED_RESOURCES_V TR
2607 ORDER BY UPPER(TR.RESOURCE_NAME);
2608
2609
2610
2611 TYPE ref_cursor_type IS REF CURSOR;
2612 TYPE task_split_tbl_type IS TABLE OF jtf_tasks_b.task_split_flag%TYPE;
2613
2614 -- REF Cursor to form different query based on different conditions.
2615 c_task_list ref_cursor_type;
2616
2617 -- Cursor to fetch the WHERE Clause corresponding to the chosen Query.
2618 CURSOR c_query_where_clause IS
2619 SELECT where_clause
2620 FROM csf_dc_queries_b
2621 WHERE query_id = p_query_id;
2622
2623 -- Cursor to fetch all Commit Child Candidates of a Parent Task
2624 -- and only those assigned to Resources belonging to the Dispatcher's Territory.
2625 CURSOR c_child_tasks (p_parent_task_id NUMBER) IS
2626 SELECT t.task_id
2627 , cac.status trip_status
2628 FROM jtf_tasks_b t
2629 , jtf_task_assignments ta
2630 , jtf_task_statuses_b ts
2631 , cac_sr_object_capacity cac
2632 WHERE t.parent_task_id = p_parent_task_id
2633 AND ta.task_id = t.task_id
2634 AND ts.task_status_id = ta.assignment_status_id
2635 AND cac.object_capacity_id(+) = ta.object_capacity_id -- made this outer join for bug 6940526
2636 AND NVL(t.deleted_flag, 'N') <> 'Y'
2637 AND NVL(ts.cancelled_flag, 'N') <> 'Y'
2638 ORDER BY 1 DESC;
2639
2640 --Cursor added for bug 6866929
2641 --This cursor +valriable l_cnt was added for checking multiple assignments for
2642 --for given task
2643 CURSOR check_assignments(p_task_id number)
2644 IS
2645 SELECT count(task_id)
2646 FROM jtf_task_assignments a
2647 , jtf_task_statuses_b b
2648 WHERE a.task_id = p_task_id
2649 AND a.assignment_status_id = b.task_status_id
2650 AND nvl(b.cancelled_flag ,'N') <> 'Y';
2651 l_cnt NUMBER :=1;
2652
2653 l_where_clause csf_dc_queries_b.where_clause%TYPE;
2654 l_query VARCHAR2(2000);
2655 l_task_id_tbl jtf_number_table;
2656 l_task_split_flag_tbl task_split_tbl_type;
2657 l_task_num_tbl jtf_number_table := jtf_number_table();
2658 l_child_task_id_tbl jtf_number_table;
2659 l_trip_status_tbl jtf_number_table;
2660 l_processed_count PLS_INTEGER;
2661 l_blocked_trip_found BOOLEAN;
2662 l_all_passed BOOLEAN;
2663
2664 BEGIN
2665 SAVEPOINT csf_commit_schedule;
2666
2667 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2668 RAISE fnd_api.g_exc_unexpected_error;
2669 END IF;
2670
2671 IF fnd_api.to_boolean (p_init_msg_list) THEN
2672 fnd_msg_pub.initialize;
2673 END IF;
2674
2675 x_return_status := fnd_api.g_ret_sts_success;
2676
2677 -- Check whether the required parameters are passed.
2678 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
2679 fnd_message.set_name('CSF', 'CSF_API_INVALID_PARAM');
2680 fnd_message.set_token('API_NAME', g_pkg_name || '.' || l_api_name);
2681 fnd_message.set_token('PARAM_NAME', 'P_QUERY_ID');
2682 fnd_msg_pub.ADD;
2683 RAISE fnd_api.g_exc_error;
2684 END IF;
2685
2686 IF p_query_id IS NOT NULL AND p_query_id <> -9999 THEN
2687 -- Query will be passed when Commit Schedule Functionality is called from
2688 -- Auto Commit Concurrent Program.
2689
2690 -- Fetch the WHERE Clause for the given Query.
2691 OPEN c_query_where_clause;
2692 FETCH c_query_where_clause INTO l_where_clause;
2693 CLOSE c_query_where_clause;
2694
2695 -- Frame the Task List Query
2696
2697 -- TASK_SPLIT_FLAG is queried from JTF_TASKS_B again since the Query might
2698 -- return PARENT_TASK and CHILD_TASK also and because of that DISTINCT might
2699 -- return two rows one beloning to PARENT_TASK and another for CHILD_TASK bcos
2700 -- of TASK_SPLIT_FLAG.
2701
2702 l_query := ' SELECT DISTINCT NVL(csf_dc_task_grid_v.parent_task_id, csf_dc_task_grid_v.task_id) task_id
2703 , (SELECT t1.task_split_flag
2704 FROM jtf_tasks_b t1
2705 WHERE t1.task_id = NVL(csf_dc_task_grid_v.parent_task_id, csf_dc_task_grid_v.task_id)) task_split_flag
2706 FROM csf_dc_task_grid_v
2707 WHERE ' || l_where_clause || ' ORDER BY 1 DESC';
2708
2709 -- Initialize the REF Cursor to point to the actual Task List Query.
2710 OPEN c_task_list FOR l_query;
2711 ELSIF (p_task_source is not null and p_task_source ='TERRITORY')
2712 THEN
2713 IF p_commit_horizon IS NOT NULL AND p_commit_horizon_uom IS NOT NULL
2714 THEN
2715 l_convert_dur_to_day :=convert_to_days(p_commit_horizon,p_commit_horizon_uom, g_uom_hours);
2716 l_end_date :=sysdate+ l_convert_dur_to_day;
2717 l_commit_horizon :=TRUE;
2718 END IF;
2719 l_convert_dur_to_day:=0;
2720 IF p_commit_horizon_from IS NOT NULL AND p_commit_uom_from IS NOT NULL
2721 THEN
2722 l_convert_dur_to_day :=convert_to_days(p_commit_horizon_from,p_commit_uom_from, g_uom_hours);
2723 l_start_date :=sysdate-l_convert_dur_to_day;
2724 ELSE
2725 l_start_date :=sysdate;
2726 END IF;
2727
2728 OPEN c_terr_resource;
2729 FETCH c_terr_resource
2730 BULK COLLECT INTO
2731 p_res_id
2732 , p_res_type
2733 , p_res_name
2734 , p_res_typ_name
2735 , p_res_key;
2736 CLOSE c_terr_resource;
2737
2738
2739 IF not (l_commit_horizon)
2740 THEN
2741 l_query := 'SELECT DISTINCT NVL(t.parent_task_id, t.task_id) task_id
2742 , t.task_split_flag
2743 FROM jtf_tasks_b t
2744 , jtf_task_assignments ta
2745 , jtf_task_statuses_b ts
2746 , (SELECT TO_NUMBER(SUBSTR(column_value
2747 , 1
2748 , INSTR(column_value, ''-'', 1, 1) - 1
2749 )
2750 )resource_id
2751 ,SUBSTR(column_value
2752 , INSTR(column_value, ''-'', 1, 1) + 1
2753 , LENGTH(column_value)
2754 ) resource_type
2755 FROM TABLE(CAST(:p_res_key AS jtf_varchar2_table_2000))
2756 ) res_info
2757 WHERE ta.resource_id = res_info.resource_id
2758 AND ta.resource_type_code = res_info.resource_type
2759 AND ts.task_status_id = ta.assignment_status_id
2760 AND NVL(ts.closed_flag, ''N'') = ''N''
2761 AND NVL(ts.completed_flag, ''N'') = ''N''
2762 AND NVL(ts.cancelled_flag, ''N'') = ''N''
2763 AND NVL(ts.assigned_flag, ''N'') <> ''Y''
2764 AND NVL(ts.working_flag, ''N'') <> ''Y''
2765 AND t.task_id = ta.task_id
2766 AND ta.task_id >=:p_from_task_id
2767 AND ta.task_id <=:p_to_task_id
2768 AND ta.booking_start_date>=:l_start_date
2769 AND t.task_type_id NOT IN (20,21)
2770 AND NVL(t.deleted_flag, ''N'') <> ''Y''
2771 AND t.source_object_type_code = ''SR''
2772 ORDER BY 1 DESC';
2773 OPEN c_task_list FOR l_query USING p_res_key,p_from_task_id,p_to_task_id,l_start_date;
2774 ELSE
2775 l_query := 'SELECT DISTINCT NVL(t.parent_task_id, t.task_id) task_id
2776 , t.task_split_flag
2777 FROM jtf_tasks_b t
2778 , jtf_task_assignments ta
2779 , jtf_task_statuses_b ts
2780 , (SELECT TO_NUMBER(SUBSTR(column_value
2781 , 1
2782 , INSTR(column_value, ''-'', 1, 1) - 1
2783 )
2784 )resource_id
2785 ,SUBSTR(column_value
2786 , INSTR(column_value, ''-'', 1, 1) + 1
2787 , LENGTH(column_value)
2788 ) resource_type
2789 FROM TABLE(CAST(:p_res_key AS jtf_varchar2_table_2000))
2790 ) res_info
2791 WHERE ta.resource_id = res_info.resource_id
2792 AND ta.resource_type_code = res_info.resource_type
2793 AND ts.task_status_id = ta.assignment_status_id
2794 AND NVL(ts.closed_flag, ''N'') = ''N''
2795 AND NVL(ts.completed_flag, ''N'') = ''N''
2796 AND NVL(ts.cancelled_flag, ''N'') = ''N''
2797 AND NVL(ts.assigned_flag, ''N'') <> ''Y''
2798 AND NVL(ts.working_flag, ''N'') <> ''Y''
2799 AND t.task_id = ta.task_id
2800 AND ta.task_id >=:p_from_task_id
2801 AND ta.task_id <=:p_to_task_id
2802 AND ta.booking_start_date between :l_start_date AND :l_end_date
2803 AND t.task_type_id NOT IN (20,21)
2804 AND NVL(t.deleted_flag, ''N'') <> ''Y''
2805 AND t.source_object_type_code = ''SR''
2806 ORDER BY 1 DESC';
2807 OPEN c_task_list FOR l_query USING p_res_key,p_from_task_id,p_to_task_id,l_start_date,l_end_date;
2808 END IF;
2809 ELSIF p_resource_id IS NOT NULL and p_trip_id IS NULL THEN --altered condition for bug 6647019
2810 -- Resource Info and Dates will be passed when Commit Schedule Functionality is
2811 -- called from Plan Board or Gantt at a Resource Level.
2812
2813 -- Frame the Task List Query using the given Resource and Schedule Dates info.
2814
2815 -- There is no way for Parent Task to be queried as part of this Query and its
2816 -- sufficient for us to have Child's Task Split Flag alone
2817
2818 l_query := 'SELECT DISTINCT NVL(t.parent_task_id, t.task_id) task_id
2819 , t.task_split_flag
2820 FROM jtf_tasks_b t
2821 , jtf_task_assignments ta
2822 , jtf_task_statuses_b ts
2823 WHERE ta.resource_id = :1
2824 AND ta.resource_type_code = :2
2825 AND ts.task_status_id = ta.assignment_status_id
2826 AND NVL(ts.closed_flag, ''N'') = ''N''
2827 AND NVL(ts.completed_flag, ''N'') = ''N''
2828 AND NVL(ts.cancelled_flag, ''N'') = ''N''
2829 AND ta.booking_start_date BETWEEN :3 and :4
2830 AND t.task_id = ta.task_id
2831 AND t.task_type_id NOT IN (20,21)
2832 AND NVL(t.deleted_flag, ''N'') <> ''Y''
2833 AND t.source_object_type_code = ''SR''
2834 ORDER BY 1 DESC';
2835
2836 -- Initialize the REF Cursor to point to the actual Task List Query.
2837 OPEN c_task_list FOR l_query USING p_resource_id
2838 , p_resource_type
2839 , p_scheduled_start_date
2840 , p_scheduled_end_date;
2841 ELSIF p_task_id IS NOT NULL THEN
2842 -- There is just one task and its sufficient for us to get the TASK_SPLIT_FLAG
2843 -- of that task.
2844 l_query := 'SELECT NVL(t.parent_task_id, t.task_id) task_id
2845 , task_split_flag
2846 FROM jtf_tasks_b t
2847 WHERE t.task_id = :1';
2848
2849 OPEN c_task_list FOR l_query USING p_task_id;
2850 ELSIF p_trip_id IS NOT NULL THEN
2851 l_query := 'SELECT NVL(t.parent_task_id, t.task_id) task_id
2852 , task_split_flag
2853 FROM cac_sr_object_capacity cac
2854 , jtf_task_assignments ta
2855 , jtf_tasks_b t
2856 , jtf_task_statuses_b ts
2857 WHERE cac.object_capacity_id = :1
2858 AND ta.resource_id = cac.object_id
2859 AND ta.resource_type_code = cac.object_type
2860 AND ta.booking_start_date <= (cac.end_date_time + ' || g_overtime || ')
2861 AND ta.booking_end_date >= cac.start_date_time
2862 AND ts.task_status_id = ta.assignment_status_id
2863 AND NVL(ts.closed_flag, ''N'') = ''N''
2864 AND NVL(ts.completed_flag, ''N'') = ''N''
2865 AND NVL(ts.cancelled_flag, ''N'') = ''N''
2866 AND t.task_id = ta.task_id
2867 AND t.task_type_id NOT IN (20,21)
2868 AND NVL(t.deleted_flag, ''N'') <> ''Y''
2869 AND t.source_object_type_code = ''SR''
2870 ORDER BY 1 DESC';
2871
2872 OPEN c_task_list FOR l_query USING p_trip_id;
2873 END IF;
2874
2875 l_processed_count := 0;
2876 l_all_passed := TRUE;
2877 LOOP
2878 FETCH c_task_list BULK COLLECT INTO l_task_id_tbl, l_task_split_flag_tbl LIMIT 1000;
2879
2880 -- Process each Task in the Task List
2881 IF l_task_id_tbl.COUNT = 0 THEN -- if there are no tasks in the trip #bug7146595
2882 fnd_message.set_name('CSF','CSF_NO_TASK_FOR_RESOURCE');
2883 fnd_msg_pub.ADD;
2884 END IF; -- end of code for the bug7146595
2885
2886 FOR i IN 1..l_task_id_tbl.COUNT
2887 LOOP
2888 l_processed_count := l_processed_count + 1;
2889
2890 --The following code is added for this bug 6866929
2891 OPEN check_assignments(l_task_id_tbl(i));
2892 FETCH check_assignments into l_cnt;
2893 CLOSE check_assignments;
2894 IF l_cnt > 1
2895 THEN
2896 fnd_message.set_name('CSF','CSF_AUTO_COMMIT_MULTI_RES');
2897 fnd_message.set_token ('TASK', task_number(l_task_id_tbl(i)));
2898 fnd_msg_pub.ADD;
2899 l_all_passed := FALSE;
2900 --End of the code added for this bug 6866929
2901 ELSE
2902 IF l_task_split_flag_tbl(i) IS NOT NULL THEN
2903 -- The current Task is a Parent Task. Fetch the Child Tasks and Commit them
2904 OPEN c_child_tasks(l_task_id_tbl(i));
2905 FETCH c_child_tasks BULK COLLECT INTO l_child_task_id_tbl, l_trip_status_tbl;
2906 CLOSE c_child_tasks;
2907
2908 -- Check whether any of the Trip containing the Child Task is blocked.
2909 l_blocked_trip_found := FALSE;
2910 FOR j IN 1..l_trip_status_tbl.COUNT LOOP
2911 IF l_trip_status_tbl(j) = csf_trips_pub.g_trip_unavailable THEN
2912 fnd_message.set_name ('CSF', 'CSF_AUTO_COMMIT_CHILD_TRIP_BLK');
2913 fnd_message.set_token ('P_TASK_NUMBER', task_number(l_child_task_id_tbl(j)));
2914 fnd_message.set_token ('P_PARENT_TASK', task_number(l_task_id_tbl(i)));
2915 fnd_msg_pub.ADD;
2916 l_blocked_trip_found := TRUE;
2917 l_all_passed := FALSE;
2918 EXIT;
2919 END IF;
2920 END LOOP;
2921
2922 IF NOT l_blocked_trip_found THEN
2923 FOR j IN 1..l_child_task_id_tbl.COUNT LOOP
2924 commit_task (
2925 p_api_version => 1.0
2926 , x_return_status => x_return_status
2927 , x_msg_data => x_msg_data
2928 , x_msg_count => x_msg_count
2929 , p_task_id => l_child_task_id_tbl(j)
2930 );
2931 IF x_return_status = fnd_api.g_ret_sts_error THEN
2932 l_all_passed := FALSE;
2933 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2934 RAISE fnd_api.g_exc_unexpected_error;
2935 END IF;
2936 END LOOP;
2937 END IF;
2938 ELSE
2939 commit_task (
2940 p_api_version => 1.0
2941 , x_return_status => x_return_status
2942 , x_msg_data => x_msg_data
2943 , x_msg_count => x_msg_count
2944 , p_task_id => l_task_id_tbl(i)
2945 , p_resource_id => p_resource_id --bug 6647019
2946 , p_resource_type => p_resource_type
2947 );
2948 IF x_return_status = fnd_api.g_ret_sts_error THEN
2949 l_all_passed := FALSE;
2950 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2951 RAISE fnd_api.g_exc_unexpected_error;
2952 ELSE --ADDED code for the bug 6801965
2953 l_task_num_tbl.extend;
2954 l_task_num_tbl(l_task_num_tbl.last) := l_task_id_tbl(i);
2955 END IF;
2956 END IF;
2957 END IF;--This is endif for checking multiple task assignments.
2958 END LOOP;
2959 EXIT WHEN c_task_list%NOTFOUND;
2960 END LOOP;
2961
2962
2963
2964 IF l_processed_count = 0 AND p_query_id IS NOT NULL THEN
2965 fnd_message.set_name ('CSF', 'CSF_AUTO_COMMIT_NO_TASK');
2966 fnd_msg_pub.ADD;
2967 x_return_status := fnd_api.g_ret_sts_error;
2968 END IF;
2969
2970 IF NOT l_all_passed THEN
2971 x_return_status := fnd_api.g_ret_sts_error;
2972 END IF;
2973 -- added code for the bug 6801965
2974 IF l_task_num_tbl.count > 0 THEN
2975 FOR i in 1..l_task_num_tbl.count
2976 LOOP
2977 fnd_message.set_name ('CSF', 'CSF_AUTO_COMMITTED');
2978 fnd_message.set_token ('P_TASK_NUMBER', task_number(l_task_num_tbl(i)));
2979 fnd_msg_pub.ADD;
2980 END LOOP;
2981 END IF;
2982 -- end of code for the bug 6801965
2983 CLOSE c_task_list;
2984 -- Standard check of p_commit
2985 IF fnd_api.to_boolean (p_commit) THEN
2986 COMMIT WORK;
2987 END IF;
2988
2989 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2990 EXCEPTION
2991 WHEN fnd_api.g_exc_error THEN
2992 ROLLBACK TO csf_commit_schedule;
2993 x_return_status := fnd_api.g_ret_sts_error;
2994 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2995 WHEN fnd_api.g_exc_unexpected_error THEN
2996 ROLLBACK TO csf_commit_schedule;
2997 x_return_status := fnd_api.g_ret_sts_unexp_error;
2998 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2999 WHEN OTHERS THEN
3000 x_return_status := fnd_api.g_ret_sts_unexp_error;
3001 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3002 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3003 END IF;
3004 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3005 ROLLBACK TO csf_commit_schedule;
3006 END commit_schedule;
3007
3008 /**
3009 * Updates the Task Information of the Parent Task by considering the current information
3010 * of all the Children.
3011 * <br>
3012 * The various attributes updated are
3013 * 1. Task Status Propagation from Child Task to Parent Task
3014 * 2. Scheduled Start Date of the Task
3015 * 3. Scheduled End Date of the Task
3016 * 4. Actual Start Date of the Task
3017 * 5. Actual Effort of the Task
3018 * <br>
3019 * <b> Task Status Propagation </b>
3020 * The Bitcodes of each Task Status is defined above. The Bitcodes have been
3021 * carefully chosen so that AND of the Bitcodes of all the Child Tasks will
3022 * give the Bitcode of the Task Status the Parent should ultimately take.
3023 * <br>
3024 * <b> For Example </b>
3025 * Case#1:
3026 * Let us assume there is a Parent Task P with three children C1, C2 and C3.
3027 *
3028 * C1 Task Status = Closed : Bitcode = 11001
3029 * C2 Task Status = Working : Bitcode = 00001
3030 * C3 Task Status = Assigned : Bitcode = 00011
3031 *
3032 * We expect the Parent Task to be in Working Status. BIT AND of all the Child
3033 * Tasks will result in 00001 which translates to Working.
3034 * <br>
3035 * Case#2:
3036 * Let us assume there is a Parent Task P with three children C1, C2 and C3.
3037 *
3038 * C1 Task Status = Closed : Bitcode = 11001
3039 * C2 Task Status = Closed : Bitcode = 11001
3040 * C3 Task Status = Assigned : Bitcode = 00011
3041 *
3042 * Since one of the Child Tasks is already Closed, it means that the Technician has
3043 * started to work on the Parent Task. So the Task Status should be Working. The BIT AND
3044 * of all the child tasks results in the same thing even though none of the child task is
3045 * in Working status.
3046 * <br>
3047 * Case#3:
3048 * Bitcode Transition will fail however when On-Hold comes into picture. If there are
3049 * any Child Tasks in On-Hold Status and all others are in Closed, Cancelled or Completed
3050 * status, then the Parent should be updated to On-Hold status. Even if any one of the
3051 * Child Task is in Working/Assigned/Planned status, then the Parent Task should
3052 * be updated to Working/Assigned/Planned (in the same order of preference). Thus any
3053 * Bitcode assigned to On-Hold will not work and it has to be treated separately.
3054 * <br>
3055 * Since there are Default Task Profiles for Planned, Asssigned, Cancelled and Working
3056 * a Global PLSQL Table is maintained to cache that information. But we might require
3057 * statuses corresponding to Closed, Completed and On-Hold. These are retrieved from the
3058 * Child Tasks and so another Local Table is also maintained to store these information
3059 * which will go out of scope once the procedure completes. Note that In-Planning
3060 * is not used as a task cant be a Parent if its in In-Planning.
3061 *
3062 * For more information refer to Bug#4032201.
3063 *
3064 * <br>
3065 * Scheduled Start Date will the minimum start date of all the children.
3066 * Scheduled End Date will the maximum end date of all the children.
3067 * Actual Start Date will the minimum start date of all the children.
3068 * Actual End Date will the maximum end date of all the children.
3069 * Actual Effort will be the sum of all the Actuals of Children after converting
3070 * to minutes.
3071 *
3072 * @param p_api_version API Version (1.0)
3073 * @param p_init_msg_list Initialize Message List
3074 * @param p_commit Commit the Work
3075 * @param x_return_status Return Status of the Procedure.
3076 * @param x_msg_count Number of Messages in the Stack.
3077 * @param x_msg_data Stack of Error Messages.
3078 * @param p_parent_task_id Task Identifier of the Parent Task.
3079 * @param p_parent_version_number Object Version of Parent Task
3080 * @param p_planned_start_date Planned start date of Parent Task.
3081 * @param p_planned_end_date Planned end date of Parent Task.
3082 */
3083 PROCEDURE sync_parent_with_child(
3084 p_api_version IN NUMBER
3085 , p_init_msg_list IN VARCHAR2
3086 , p_commit IN VARCHAR2
3087 , x_return_status OUT NOCOPY VARCHAR2
3088 , x_msg_count OUT NOCOPY NUMBER
3089 , x_msg_data OUT NOCOPY VARCHAR2
3090 , p_parent_task_id IN NUMBER
3091 , p_parent_version_number IN OUT NOCOPY NUMBER
3092 , p_planned_start_date IN DATE
3093 , p_planned_end_date IN DATE
3094 ) IS
3095 l_api_name CONSTANT VARCHAR2(30) := 'SYNC_PARENT_WITH_CHILD';
3096 l_api_version CONSTANT NUMBER := 1.0;
3097
3098 CURSOR c_curr_parent_info IS
3099 SELECT t.task_status_id
3100 , t.actual_start_date
3101 , t.actual_end_date
3102 , t.scheduled_start_date
3103 , t.scheduled_end_date
3104 , t.planned_start_date
3105 , t.planned_end_date
3106 , csf_util_pvt.convert_to_minutes(t.actual_effort, t.actual_effort_uom) actual_effort
3107 FROM jtf_tasks_b t
3108 WHERE t.task_id = p_parent_task_id
3109 AND NVL(t.deleted_flag, 'N') <> 'Y';
3110
3111 CURSOR c_new_parent_info IS
3112 SELECT g_inplanning task_status_id
3113 , MIN(t.scheduled_start_date) scheduled_start_date
3114 , MAX(t.scheduled_end_date) scheduled_end_date
3115 , MIN(t.actual_start_date) actual_start_date
3116 , MAX(t.actual_end_date) actual_end_date
3117 , SUM(csf_util_pvt.convert_to_minutes(t.actual_effort, t.actual_effort_uom)) actual_effort
3118 FROM jtf_tasks_b t
3119 , jtf_task_statuses_b ts
3120 WHERE t.parent_task_id = p_parent_task_id
3121 AND NVL(t.deleted_flag, 'N') <> 'Y'
3122 AND ts.task_status_id = t.task_status_id
3123 AND NVL(ts.cancelled_flag, 'N') <> 'Y';
3124
3125 CURSOR c_child_tasks IS
3126 SELECT t.task_id
3127 , t.task_status_id
3128 , NVL(ts.schedulable_flag, 'N') schedulable_flag
3129 , NVL(ts.assigned_flag, 'N') assigned_flag
3130 , NVL(ts.working_flag, 'N') working_flag
3131 , NVL(ts.completed_flag, 'N') completed_flag
3132 , NVL(ts.closed_flag, 'N') closed_flag
3133 , NVL(ts.on_hold_flag, 'N') on_hold_flag
3134 , NVL(ts.rejected_flag, 'N') rejected_flag
3135 , NVL(ts.cancelled_flag, 'N') cancelled_flag
3136 , NVL(ts.accepted_flag, 'N') accepted_flag
3137 , NVL(ts.assignment_status_flag, 'N') assignment_status_flag
3138 , 0 status_bitcode
3139 FROM jtf_tasks_b t
3140 , jtf_task_statuses_b ts
3141 WHERE t.parent_task_id = p_parent_task_id
3142 AND ts.task_status_id = t.task_status_id
3143 AND NVL(t.deleted_flag, 'N') <> 'Y'
3144 ORDER BY t.task_id;
3145
3146 l_status_bitcode_map_tbl number_tbl_type;
3147
3148 l_curr_parent_info c_curr_parent_info%ROWTYPE;
3149 l_new_parent_info c_new_parent_info%ROWTYPE;
3150 l_pri_sts_bitcode NUMBER;
3151 l_sec_sts_bitcode NUMBER;
3152 l_update_parent BOOLEAN;
3153 l_actual_effort_uom VARCHAR2(3);
3154 --*********** added for bug 6646890************
3155 l_update BOOLEAN := FALSE;
3156 l_child_status NUMBER;
3157 i NUMBER := 1;
3158 --*********** added for bug 6646890************
3159
3160
3161 FUNCTION get_status_bitcode(p_task c_child_tasks%ROWTYPE)
3162 RETURN NUMBER IS
3163 l_status_bitcode NUMBER;
3164 BEGIN
3165 l_status_bitcode := g_start_bitcode;
3166
3167 IF p_task.cancelled_flag = 'N' AND p_task.rejected_flag = 'N' AND p_task.on_hold_flag = 'N' THEN
3168 IF p_task.closed_flag = 'Y' THEN
3169 l_status_bitcode := g_closed_bitcode;
3170 ELSIF p_task.completed_flag = 'Y' THEN
3171 l_status_bitcode := g_completed_bitcode;
3172 ELSIF p_task.working_flag = 'Y' THEN
3173 l_status_bitcode := g_working_bitcode;
3174 --*********** added for bug 6646890************
3175 ELSIF p_task.accepted_flag = 'Y' THEN
3176 l_status_bitcode := g_accepted_bitcode;
3177 --*********** added for bug 6646890************
3178 ELSIF p_task.assigned_flag = 'Y' THEN
3179 l_status_bitcode := g_assigned_bitcode;
3180 ELSIF p_task.schedulable_flag = 'Y' THEN
3181 l_status_bitcode := g_planned_bitcode;
3182 END IF;
3183 --RETURN l_status_bitcode + 480; -- 480 stands for 111100000
3184 ELSE
3185 IF p_task.cancelled_flag = 'Y' THEN
3186 l_status_bitcode := g_cancelled_bitcode;
3187 ELSIF p_task.rejected_flag = 'Y' THEN
3188 l_status_bitcode := g_rejected_bitcode;
3189 ELSE
3190 l_status_bitcode := g_onhold_bitcode;
3191 END IF;
3192 --RETURN l_status_bitcode + 31; -- 31 stands for 000011111
3193 END IF;
3194
3195 RETURN l_status_bitcode;
3196 END get_status_bitcode;
3197 BEGIN
3198 SAVEPOINT csf_sync_parent_with_child;
3199
3200 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3201 RAISE fnd_api.g_exc_unexpected_error;
3202 END IF;
3203
3204 IF fnd_api.to_boolean (p_init_msg_list) THEN
3205 fnd_msg_pub.initialize;
3206 END IF;
3207
3208 x_return_status := fnd_api.g_ret_sts_success;
3209
3210 OPEN c_curr_parent_info;
3211 FETCH c_curr_parent_info INTO l_curr_parent_info;
3212 CLOSE c_curr_parent_info;
3213
3214 OPEN c_new_parent_info;
3215 FETCH c_new_parent_info INTO l_new_parent_info;
3216 CLOSE c_new_parent_info;
3217
3218 /****************************************************************************
3219 * Propagating the Child Task's Status to the Parent *
3220 *****************************************************************************/
3221 -- Initialize the Finite Automata.
3222 l_pri_sts_bitcode := g_start_bitcode;
3223 l_sec_sts_bitcode := g_start_bitcode;
3224
3225 -- If we have Child Tasks one in Planned and another in Closed, we have to
3226 -- move the Parent to Working Status. But none of the Children would have
3227 -- given the Working Status ID. So take it from Default Value.
3228 l_status_bitcode_map_tbl(g_working_bitcode) := g_working;
3229
3230 FOR v_child IN c_child_tasks LOOP
3231 -- Compute the Bit Code of the Current Child Task.
3232 --*********** added for bug 6646890************
3233 IF i=1 THEN
3234 l_child_status := v_child.task_status_id ;
3235 i := i+1;
3236 END IF;
3237 IF l_child_status = v_child.task_status_id THEN
3238 l_update := TRUE;
3239 ELSE
3240 l_update := FALSE;
3241 END IF;
3242 --*********** added for bug 6646890************
3243
3244 v_child.status_bitcode := get_status_bitcode(v_child);
3245
3246 IF v_child.status_bitcode <> g_start_bitcode THEN
3247 IF BITAND (v_child.status_bitcode, 63) BETWEEN 1 AND 62 THEN
3248 l_pri_sts_bitcode := BITAND(l_pri_sts_bitcode, v_child.status_bitcode);
3249 l_status_bitcode_map_tbl(v_child.status_bitcode) := v_child.task_status_id;
3250 ELSIF BITAND (v_child.status_bitcode, 448) BETWEEN 63 AND 510 THEN
3251 l_sec_sts_bitcode := BITAND(l_sec_sts_bitcode, v_child.status_bitcode);
3252 l_status_bitcode_map_tbl(v_child.status_bitcode) := v_child.task_status_id;
3253 END IF;
3254 END IF;
3255 END LOOP;
3256
3257 -- If we have a valid Primary Status for Parent, then we have to use that status.
3258 -- Otherwise we have to try using Secondary Status.
3259 -- (l_pri_sts_bitcode in (17,49) and l_sec_sts_bitcode=g_onhold_bitcode ) has been added for the bug for the following
3260 -- scenario:
3261 -- Suppose there are two child tasks T1,T2.T1 is in onhold status and T2 in Completed/Closed Status . Then the parent task
3262 -- status should be Onhold.
3263 IF (l_pri_sts_bitcode in (17,49) and l_sec_sts_bitcode=g_onhold_bitcode ) or l_pri_sts_bitcode >= 63 THEN
3264 l_pri_sts_bitcode := l_sec_sts_bitcode;
3265 END IF;
3266
3267 IF l_status_bitcode_map_tbl.EXISTS(l_pri_sts_bitcode) and not (l_update) THEN
3268 l_new_parent_info.task_status_id := l_status_bitcode_map_tbl(l_pri_sts_bitcode);
3269 --*********** added for bug 6646890************
3270 ELSIF l_update THEN
3271 l_new_parent_info.task_status_id := l_child_status;
3272 END IF;
3273 --*********** added for bug 6646890************
3274
3275 /****************************************************************************
3276 * Finding out whether Parent's Data has Changed *
3277 *****************************************************************************/
3278 l_update_parent :=
3279 l_curr_parent_info.task_status_id <> l_new_parent_info.task_status_id
3280 OR ( NVL(l_curr_parent_info.scheduled_start_date, fnd_api.g_miss_date)
3281 <> NVL(l_new_parent_info.scheduled_start_date, fnd_api.g_miss_date) )
3282 OR ( NVL(l_curr_parent_info.scheduled_end_date, fnd_api.g_miss_date)
3283 <> NVL(l_new_parent_info.scheduled_end_date, fnd_api.g_miss_date) )
3284 OR ( NVL(l_curr_parent_info.actual_start_date, fnd_api.g_miss_date)
3285 <> NVL(l_new_parent_info.actual_start_date, fnd_api.g_miss_date) )
3286 OR ( NVL(l_curr_parent_info.actual_end_date, fnd_api.g_miss_date)
3287 <> NVL(l_new_parent_info.actual_end_date, fnd_api.g_miss_date) )
3288 OR ( NVL(l_curr_parent_info.planned_start_date, fnd_api.g_miss_date)
3289 <> NVL(p_planned_start_date, fnd_api.g_miss_date) )
3290 OR ( NVL(l_curr_parent_info.planned_end_date, fnd_api.g_miss_date)
3291 <> NVL(p_planned_end_date, fnd_api.g_miss_date) )
3292 OR ( NVL(l_curr_parent_info.actual_effort, -1)
3293 <> NVL(l_new_parent_info.actual_effort, -1) );
3294
3295
3296 /****************************************************************************
3297 * Updating the Parent Task Information *
3298 *****************************************************************************/
3299 IF l_update_parent THEN
3300 IF l_new_parent_info.actual_effort IS NOT NULL THEN
3301 l_actual_effort_uom := csf_util_pvt.get_uom_minutes;
3302 END IF;
3303
3304 jtf_tasks_pub.update_task (
3305 p_api_version => 1.0
3306 , p_init_msg_list => p_init_msg_list
3307 , p_commit => fnd_api.g_false
3308 , x_return_status => x_return_status
3309 , x_msg_count => x_msg_count
3310 , x_msg_data => x_msg_data
3311 , p_task_id => p_parent_task_id
3312 , p_object_version_number => p_parent_version_number
3313 , p_task_status_id => l_new_parent_info.task_status_id
3314 , p_scheduled_start_date => l_new_parent_info.scheduled_start_date
3315 , p_scheduled_end_date => l_new_parent_info.scheduled_end_date
3316 , p_planned_start_date => p_planned_start_date
3317 , p_planned_end_date => p_planned_end_date
3318 , p_actual_start_date => l_new_parent_info.actual_start_date
3319 , p_actual_end_date => l_new_parent_info.actual_end_date
3320 , p_actual_effort => l_new_parent_info.actual_effort
3321 , p_actual_effort_uom => l_actual_effort_uom
3322 , p_task_split_flag => 'M'
3323 , p_enable_workflow => fnd_api.g_miss_char
3324 , p_abort_workflow => fnd_api.g_miss_char
3325 );
3326
3327 IF x_return_status = fnd_api.g_ret_sts_error THEN
3328 RAISE fnd_api.g_exc_error;
3329 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3330 RAISE fnd_api.g_exc_unexpected_error;
3331 END IF;
3332 END IF;
3333
3334 -- Standard check of p_commit
3335 IF fnd_api.to_boolean (p_commit) THEN
3336 COMMIT WORK;
3337 END IF;
3338 EXCEPTION
3339 WHEN fnd_api.g_exc_error THEN
3340 ROLLBACK TO csf_sync_parent_with_child;
3341 x_return_status := fnd_api.g_ret_sts_error;
3342 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3343 WHEN fnd_api.g_exc_unexpected_error THEN
3344 ROLLBACK TO csf_sync_parent_with_child;
3345 x_return_status := fnd_api.g_ret_sts_unexp_error;
3346 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3347 WHEN OTHERS THEN
3348 x_return_status := fnd_api.g_ret_sts_unexp_error;
3349 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3350 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3351 END IF;
3352 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3353 ROLLBACK TO csf_sync_parent_with_child;
3354 END sync_parent_with_child;
3355
3356 /**
3357 * Updates the Attributes of the Child Tasks by considering the Parent Task.
3358 *
3359 * @param p_api_version API Version (1.0)
3360 * @param p_init_msg_list Initialize Message List
3361 * @param p_commit Commit the Work
3362 * @param x_return_status Return Status of the Procedure.
3363 * @param x_msg_count Number of Messages in the Stack.
3364 * @param x_msg_data Stack of Error Messages.
3365 * @param p_parent_task_id Task Identifier of the Parent Task.
3366 */
3367 PROCEDURE sync_child_from_parent(
3368 p_api_version IN NUMBER
3369 , p_init_msg_list IN VARCHAR2
3370 , p_commit IN VARCHAR2
3371 , x_return_status OUT NOCOPY VARCHAR2
3372 , x_msg_count OUT NOCOPY NUMBER
3373 , x_msg_data OUT NOCOPY VARCHAR2
3374 , p_parent_task_id IN NUMBER
3375 ) IS
3376 l_api_name CONSTANT VARCHAR2(30) := 'SYNC_CHILD_FROM_PARENT';
3377 l_api_version CONSTANT NUMBER := 1.0;
3378
3379 CURSOR c_child_tasks IS
3380 SELECT t.task_id
3381 , t.object_version_number
3382 , NVL(t.child_position, '@@') child_position
3383 , NVL(t.child_sequence_num, -1) child_sequence_num
3384 , RANK() OVER (ORDER BY t.scheduled_start_date, t.scheduled_end_date,nvl(t.child_sequence_num,-1)) correct_seq_num
3385 , LEAD (t.task_id) OVER (ORDER BY t.scheduled_start_date, t.scheduled_end_date,nvl(t.child_sequence_num,-1)) next_task_id
3386 FROM jtf_tasks_b t ,jtf_task_statuses_b ts
3387 WHERE t.parent_task_id = p_parent_task_id
3388 AND NVL(t.deleted_flag, 'N') <> 'Y'
3389 AND ts.task_status_id = t.task_status_id
3390 AND NVL(ts.cancelled_flag, 'N') <> 'Y';
3391
3392 l_child_position jtf_tasks_b.child_position%TYPE;
3393 BEGIN
3394 SAVEPOINT csf_sync_child_from_parent;
3395
3396 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3397 RAISE fnd_api.g_exc_unexpected_error;
3398 END IF;
3399
3400 IF fnd_api.to_boolean (p_init_msg_list) THEN
3401 fnd_msg_pub.initialize;
3402 END IF;
3403
3404 x_return_status := fnd_api.g_ret_sts_success;
3405
3406 l_child_position := 'F';
3407 FOR v_child_task IN c_child_tasks LOOP
3408 IF v_child_task.next_task_id IS NULL AND v_child_task.correct_seq_num <> 1 THEN
3409 l_child_position := 'L';
3410 END IF;
3411
3412 IF ( (v_child_task.child_sequence_num <> v_child_task.correct_seq_num)
3413 OR (v_child_task.child_position <> l_child_position) )
3414 THEN
3415 -- Update the Child Task
3416 jtf_tasks_pub.update_task(
3417 p_api_version => 1.0
3418 , x_return_status => x_return_status
3419 , x_msg_count => x_msg_count
3420 , x_msg_data => x_msg_data
3421 , p_task_id => v_child_task.task_id
3422 , p_task_split_flag => fnd_api.g_miss_char
3423 , p_object_version_number => v_child_task.object_version_number
3424 , p_child_sequence_num => v_child_task.correct_seq_num
3425 , p_child_position => l_child_position
3426 , p_enable_workflow => fnd_api.g_miss_char
3427 , p_abort_workflow => fnd_api.g_miss_char
3428 );
3429
3430 IF x_return_status = fnd_api.g_ret_sts_error THEN
3431 RAISE fnd_api.g_exc_error;
3432 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3433 RAISE fnd_api.g_exc_unexpected_error;
3434 END IF;
3435 END IF;
3436
3437 l_child_position := 'M';
3438 END LOOP;
3439
3440 -- Standard check of p_commit
3441 IF fnd_api.to_boolean (p_commit) THEN
3442 COMMIT WORK;
3443 END IF;
3444 EXCEPTION
3445 WHEN fnd_api.g_exc_error THEN
3446 ROLLBACK TO csf_sync_child_from_parent;
3447 x_return_status := fnd_api.g_ret_sts_error;
3448 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3449 WHEN fnd_api.g_exc_unexpected_error THEN
3450 ROLLBACK TO csf_sync_child_from_parent;
3451 x_return_status := fnd_api.g_ret_sts_unexp_error;
3452 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3453 WHEN OTHERS THEN
3454 x_return_status := fnd_api.g_ret_sts_unexp_error;
3455 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3456 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3457 END IF;
3458 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3459 ROLLBACK TO csf_sync_child_from_parent;
3460 END sync_child_from_parent;
3461
3462 PROCEDURE assign_task(
3463 p_api_version IN NUMBER
3464 , p_init_msg_list IN VARCHAR2
3465 , p_commit IN VARCHAR2
3466 , x_return_status OUT NOCOPY VARCHAR2
3467 , x_msg_count OUT NOCOPY NUMBER
3468 , x_msg_data OUT NOCOPY VARCHAR2
3469 , p_task_id IN NUMBER
3470 , p_object_version_number IN OUT NOCOPY NUMBER
3471 , p_task_status_id IN NUMBER
3472 , p_scheduled_start_date IN DATE
3473 , p_scheduled_end_date IN DATE
3474 , p_planned_start_date IN DATE
3475 , p_planned_end_date IN DATE
3476 , p_old_task_assignment_id IN NUMBER
3477 , p_old_ta_object_version IN NUMBER
3478 , p_assignment_status_id IN NUMBER
3479 , p_resource_id IN NUMBER
3480 , p_resource_type IN VARCHAR2
3481 , p_object_capacity_id IN NUMBER
3482 , p_sched_travel_distance IN NUMBER
3483 , p_sched_travel_duration IN NUMBER
3484 , p_sched_travel_duration_uom IN VARCHAR2
3485 , p_planned_effort IN NUMBER
3486 , p_planned_effort_uom IN VARCHAR2
3487 , x_task_assignment_id OUT NOCOPY NUMBER
3488 , x_ta_object_version_number OUT NOCOPY NUMBER
3489 ) IS
3490 l_api_name CONSTANT VARCHAR2(30) := 'ASSIGN_TASK';
3491 l_api_version CONSTANT NUMBER := 1.0;
3492
3493 CURSOR c_task_info IS
3494 SELECT t.task_id
3495 , t.task_status_id
3496 , t.task_split_flag
3497 , t.object_version_number
3498 , t.scheduled_start_date
3499 , t.scheduled_end_date
3500 , NVL( ( SELECT 'Y'
3501 FROM jtf_task_assignments ta, jtf_task_statuses_b ats
3502 WHERE ta.task_id = p_task_id
3503 AND ta.assignment_status_id = ats.task_status_id
3504 AND NVL(ats.cancelled_flag, 'N') <> 'Y'
3505 AND ROWNUM = 1
3506 ), 'N'
3507 ) is_scheduled
3508 FROM jtf_tasks_b t
3509 WHERE t.task_id = p_task_id;
3510
3511 CURSOR c_task_assignment_info IS
3512 SELECT ta.resource_id
3513 , ta.resource_type_code
3514 FROM jtf_task_assignments ta
3515 WHERE ta.task_assignment_id = p_old_task_assignment_id;
3516
3517 l_task_info c_task_info%ROWTYPE;
3518 l_task_assignment_info c_task_assignment_info%ROWTYPE;
3519 l_planned_effort NUMBER;
3520 l_planned_effort_uom VARCHAR2(3);
3521 l_create_assignment BOOLEAN;
3522 l_assignment_status_id NUMBER;
3523 l_role VARCHAR2(30);
3524 l_task_split_flag VARCHAR2(1);
3525 BEGIN
3526 SAVEPOINT csf_assign_task;
3527
3528 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3529 RAISE fnd_api.g_exc_unexpected_error;
3530 END IF;
3531
3532 IF fnd_api.to_boolean(p_init_msg_list) THEN
3533 fnd_msg_pub.initialize;
3534 END IF;
3535
3536 x_return_status := fnd_api.g_ret_sts_success;
3537
3538 -- Get the Task Information
3539 OPEN c_task_info;
3540 FETCH c_task_info INTO l_task_info;
3541 CLOSE c_task_info;
3542
3543 IF l_task_info.task_id IS NULL THEN
3544 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'NO_DATA_FOUND JTF_TASKS_B.TASK_ID = ' || p_task_id);
3545 RAISE fnd_api.g_exc_unexpected_error;
3546 END IF;
3547
3548 -- If the Task is already Scheduled, then the Task Should be treated to be
3549 -- in Unscheduled Task Status as the task should have been unscheduled
3550 -- before rescheduling. Since we are avoiding unnecessary unscheduling,
3551 -- lets assume the old task status to be Unscheduled Task Status.
3552 IF l_task_info.is_scheduled = 'Y' THEN
3553 l_task_info.task_status_id := g_unscheduled;
3554 END IF;
3555
3556 -- Find out whether the new Task Status is valid.
3557 validate_status_change(l_task_info.task_status_id, p_task_status_id);
3558
3559 -- If the Old Assignment specified is linked to the same resource as that
3560 -- of the New Assignment, then there is no need to cancel the Old Assignment.
3561 -- Rather just update the Old Assignment with the new Travel Times.
3562 l_create_assignment := TRUE;
3563
3564 IF p_old_task_assignment_id IS NOT NULL THEN
3565 g_reschedule := 'Y';
3566 x_ta_object_version_number := p_old_ta_object_version;
3567 l_assignment_status_id := g_cancelled;
3568
3569 OPEN c_task_assignment_info;
3570 FETCH c_task_assignment_info INTO l_task_assignment_info;
3571 CLOSE c_task_assignment_info;
3572
3573 IF l_task_assignment_info.resource_id = p_resource_id
3574 AND l_task_assignment_info.resource_type_code = p_resource_type
3575 THEN
3576 l_create_assignment := FALSE;
3577 l_assignment_status_id := p_assignment_status_id;
3578 x_task_assignment_id := p_old_task_assignment_id;
3579 END IF;
3580
3581 csf_task_assignments_pub.update_task_assignment (
3582 p_api_version => 1.0
3583 , p_validation_level => fnd_api.g_valid_level_none
3584 , x_return_status => x_return_status
3585 , x_msg_count => x_msg_count
3586 , x_msg_data => x_msg_data
3587 , p_task_assignment_id => p_old_task_assignment_id
3588 , p_object_version_number => x_ta_object_version_number
3589 , p_assignment_status_id => l_assignment_status_id
3590 , p_object_capacity_id => p_object_capacity_id
3591 , p_sched_travel_distance => p_sched_travel_distance
3592 , p_sched_travel_duration => p_sched_travel_duration
3593 , p_sched_travel_duration_uom => p_sched_travel_duration_uom
3594 , p_update_task => fnd_api.g_false
3595 , x_task_object_version_number => l_task_info.object_version_number
3596 , x_task_status_id => l_task_info.task_status_id
3597 );
3598 IF x_return_status = fnd_api.g_ret_sts_error THEN
3599 RAISE fnd_api.g_exc_error;
3600 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3601 RAISE fnd_api.g_exc_unexpected_error;
3602 END IF;
3603 END IF;
3604
3605 l_planned_effort := fnd_api.g_miss_num;
3606 l_planned_effort_uom := fnd_api.g_miss_char;
3607
3608 IF (l_task_info.task_split_flag = 'D') THEN
3609 l_planned_effort := p_planned_effort;
3610 l_planned_effort_uom := p_planned_effort_uom;
3611 END IF;
3612
3613 l_role := csf_resource_pub.get_third_party_role ( p_resource_id
3614 , p_resource_type
3615 );
3616
3617 IF ( l_role = 'CSF_THIRD_PARTY_SERVICE_PROVID' ) THEN
3618 l_task_split_flag := NULL;
3619 ELSE
3620 l_task_split_flag := fnd_api.g_miss_char;
3621 END IF;
3622
3623
3624 -- Update the Task
3625 jtf_tasks_pub.update_task(
3626 p_api_version => 1.0
3627 , x_return_status => x_return_status
3628 , x_msg_count => x_msg_count
3629 , x_msg_data => x_msg_data
3630 , p_task_id => p_task_id
3631 , p_object_version_number => p_object_version_number
3632 , p_task_status_id => p_task_status_id
3633 , p_scheduled_start_date => p_scheduled_start_date
3634 , p_scheduled_end_date => p_scheduled_end_date
3635 , p_planned_start_date => p_planned_start_date
3636 , p_planned_end_date => p_planned_end_date
3637 , p_planned_effort => l_planned_effort
3638 , p_planned_effort_uom => l_planned_effort_uom
3639 , p_task_split_flag => l_task_split_flag
3640 , p_enable_workflow => fnd_api.g_miss_char
3641 , p_abort_workflow => fnd_api.g_miss_char
3642 );
3643 IF x_return_status = fnd_api.g_ret_sts_error THEN
3644 RAISE fnd_api.g_exc_error;
3645 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3646 RAISE fnd_api.g_exc_unexpected_error;
3647 END IF;
3648
3649 -- Clear out the Rejection Message, if any.
3650 UPDATE jtf_tasks_tl
3651 SET rejection_message = NULL
3652 WHERE task_id = p_task_id;
3653
3654
3655 -- Create the Task Assignment
3656 IF l_create_assignment THEN
3657 csf_task_assignments_pub.create_task_assignment(
3658 p_api_version => 1.0
3659 , p_validation_level => fnd_api.g_valid_level_none
3660 , x_return_status => x_return_status
3661 , x_msg_count => x_msg_count
3662 , x_msg_data => x_msg_data
3663 , p_task_id => p_task_id
3664 , p_resource_id => p_resource_id
3665 , p_resource_type_code => p_resource_type
3666 , p_assignment_status_id => p_assignment_status_id
3667 , p_object_capacity_id => p_object_capacity_id
3668 , p_sched_travel_distance => p_sched_travel_distance
3669 , p_sched_travel_duration => p_sched_travel_duration
3670 , p_sched_travel_duration_uom => p_sched_travel_duration_uom
3671 , p_update_task => fnd_api.g_false
3672 , x_task_assignment_id => x_task_assignment_id
3673 , x_ta_object_version_number => x_ta_object_version_number
3674 , x_task_object_version_number => p_object_version_number
3675 , x_task_status_id => l_task_info.task_status_id
3676 );
3677
3678 IF x_return_status = fnd_api.g_ret_sts_error THEN
3679 RAISE fnd_api.g_exc_error;
3680 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3681 RAISE fnd_api.g_exc_unexpected_error;
3682 END IF;
3683 END IF;
3684
3685 IF fnd_api.to_boolean(p_commit) THEN
3686 COMMIT WORK;
3687 END IF;
3688
3689 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3690
3691 EXCEPTION
3692 WHEN fnd_api.g_exc_error THEN
3693 ROLLBACK TO csf_assign_task;
3694 x_return_status := fnd_api.g_ret_sts_error;
3695 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3696 WHEN fnd_api.g_exc_unexpected_error THEN
3697 ROLLBACK TO csf_assign_task;
3698 x_return_status := fnd_api.g_ret_sts_unexp_error;
3699 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3700 WHEN OTHERS THEN
3701 x_return_status := fnd_api.g_ret_sts_unexp_error;
3702 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3703 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3704 END IF;
3705 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3706 ROLLBACK TO csf_assign_task;
3707 END assign_task;
3708
3709 PROCEDURE unassign_task(
3710 p_api_version IN NUMBER
3711 , p_init_msg_list IN VARCHAR2
3712 , p_commit IN VARCHAR2
3713 , x_return_status OUT NOCOPY VARCHAR2
3714 , x_msg_count OUT NOCOPY NUMBER
3715 , x_msg_data OUT NOCOPY VARCHAR2
3716 , p_task_id IN NUMBER
3717 , p_object_version_number IN OUT NOCOPY NUMBER
3718 , p_task_status_id IN NUMBER
3719 , p_task_assignment_id IN NUMBER
3720 , p_ta_object_version_number IN OUT NOCOPY NUMBER
3721 , p_assignment_status_id IN NUMBER
3722 ) IS
3723 l_api_name CONSTANT VARCHAR2(30) := 'UNASSIGN_TASK';
3724 l_api_version CONSTANT NUMBER := 1.0;
3725
3726 CURSOR c_task_info IS
3727 SELECT t.task_id
3728 , t.task_status_id
3729 , t.task_split_flag
3730 , source_object_type_code
3731 , scheduled_start_date
3732 , scheduled_end_date
3733 , ta.assignment_status_id
3734 , ta.object_capacity_id
3735 FROM jtf_tasks_b t , jtf_task_assignments ta
3736 WHERE t.task_id = p_task_id
3737 AND ta.task_id = t.task_id
3738 AND ta.task_assignment_id = p_task_assignment_id;
3739
3740 -- Fetch the Flags corresponding to the new Task Status.
3741 CURSOR c_task_status_info IS
3742 SELECT NVL (ts.closed_flag, 'N') closed_flag
3743 , NVL (ts.cancelled_flag, 'N') cancelled_flag
3744 FROM jtf_task_statuses_b ts
3745 WHERE ts.task_status_id = p_task_status_id;
3746
3747 l_task_info c_task_info%ROWTYPE;
3748 l_task_status_info c_task_status_info%ROWTYPE;
3749 l_task_status_id NUMBER;
3750 BEGIN
3751 SAVEPOINT csf_unassign_task;
3752
3753 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3754 RAISE fnd_api.g_exc_unexpected_error;
3755 END IF;
3756
3757 IF fnd_api.to_boolean(p_init_msg_list) THEN
3758 fnd_msg_pub.initialize;
3759 END IF;
3760
3761 x_return_status := fnd_api.g_ret_sts_success;
3762
3763 OPEN c_task_info;
3764 FETCH c_task_info INTO l_task_info;
3765 CLOSE c_task_info;
3766
3767 IF nvl(l_task_info.assignment_status_id, -1) <> NVL(p_assignment_status_id, g_cancelled)
3768 OR l_task_info.object_capacity_id IS NOT NULL THEN
3769 -- Cancel the Task Assignment
3770 -- P_OBJECT_CAPACITY_ID is passed as NULL so that when UPDATE_ASSIGNMENT_STATUS
3771 -- Queries the Task Information, there will not be any Trip Information and
3772 -- Update is avoided as Scheduler will take care of the update.
3773 csf_task_assignments_pub.update_task_assignment (
3774 p_api_version => 1.0
3775 , p_validation_level => fnd_api.g_valid_level_none
3776 , x_return_status => x_return_status
3777 , x_msg_count => x_msg_count
3778 , x_msg_data => x_msg_data
3779 , p_task_assignment_id => p_task_assignment_id
3780 , p_assignment_status_id => NVL(p_assignment_status_id, g_cancelled)
3781 , p_object_version_number => p_ta_object_version_number
3782 , p_object_capacity_id => NULL
3783 , p_update_task => fnd_api.g_false
3784 , x_task_object_version_number => p_object_version_number
3785 , x_task_status_id => l_task_status_id
3786 );
3787 IF x_return_status = fnd_api.g_ret_sts_error THEN
3788 RAISE fnd_api.g_exc_error;
3789 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3790 RAISE fnd_api.g_exc_unexpected_error;
3791 END IF;
3792 END IF;
3793
3794 IF NVL(p_task_status_id,-1) <> nvl(l_task_info.task_status_id,-1)
3795 OR ( l_task_info.source_object_type_code = 'SR'
3796 AND ( l_task_info.scheduled_start_date IS NOT NULL
3797 OR l_task_info.scheduled_end_date IS NOT NULL ) ) THEN
3798
3799 -- Validate the Task Status Transition
3800 validate_status_change(l_task_info.task_status_id, p_task_status_id);
3801
3802 IF l_task_info.source_object_type_code = 'SR' THEN
3803 l_task_info.scheduled_start_date := NULL;
3804 l_task_info.scheduled_end_date := NULL;
3805 END IF;
3806
3807 -- Update the Task Information.
3808 jtf_tasks_pub.update_task(
3809 p_api_version => 1.0
3810 , x_return_status => x_return_status
3811 , x_msg_count => x_msg_count
3812 , x_msg_data => x_msg_data
3813 , p_task_id => p_task_id
3814 , p_object_version_number => p_object_version_number
3815 , p_task_status_id => p_task_status_id
3816 , p_scheduled_start_date => l_task_info.scheduled_start_date
3817 , p_scheduled_end_date => l_task_info.scheduled_end_date
3818 , p_enable_workflow => fnd_api.g_miss_char
3819 , p_abort_workflow => fnd_api.g_miss_char
3820 );
3821 IF x_return_status = fnd_api.g_ret_sts_error THEN
3822 RAISE fnd_api.g_exc_error;
3823 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3824 RAISE fnd_api.g_exc_unexpected_error;
3825 END IF;
3826 END IF;
3827
3828 -- if the task is a child task and is being cancelled, delete(logically) the task
3829 IF l_task_info.task_split_flag = 'D' THEN
3830
3831 OPEN c_task_status_info;
3832 FETCH c_task_status_info INTO l_task_status_info;
3833 CLOSE c_task_status_info;
3834
3835 IF l_task_status_info.cancelled_flag = 'Y' THEN
3836 csf_tasks_pub.delete_task (
3837 p_api_version => 1.0
3838 , x_return_status => x_return_status
3839 , x_msg_count => x_msg_count
3840 , x_msg_data => x_msg_data
3841 , p_task_id => p_task_id
3842 , p_object_version_number => p_object_version_number
3843 );
3844
3845 IF x_return_status = fnd_api.g_ret_sts_error THEN
3846 RAISE fnd_api.g_exc_error;
3847 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3848 RAISE fnd_api.g_exc_unexpected_error;
3849 END IF;
3850 END IF;
3851 END IF;
3852
3853 IF fnd_api.to_boolean(p_commit) THEN
3854 COMMIT WORK;
3855 END IF;
3856 EXCEPTION
3857 WHEN fnd_api.g_exc_error THEN
3858 ROLLBACK TO csf_unassign_task;
3859 x_return_status := fnd_api.g_ret_sts_error;
3860 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3861 WHEN fnd_api.g_exc_unexpected_error THEN
3862 ROLLBACK TO csf_unassign_task;
3863 x_return_status := fnd_api.g_ret_sts_unexp_error;
3864 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3865 WHEN OTHERS THEN
3866 x_return_status := fnd_api.g_ret_sts_unexp_error;
3867 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3868 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3869 END IF;
3870 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3871 ROLLBACK TO csf_unassign_task;
3872 END unassign_task;
3873
3874 PROCEDURE update_task_and_assignment(
3875 p_api_version IN NUMBER
3876 , p_init_msg_list IN VARCHAR2
3877 , p_commit IN VARCHAR2
3878 , x_return_status OUT NOCOPY VARCHAR2
3879 , x_msg_count OUT NOCOPY NUMBER
3880 , x_msg_data OUT NOCOPY VARCHAR2
3881 , p_task_id IN NUMBER
3882 , p_object_version_number IN OUT NOCOPY NUMBER
3883 , p_scheduled_start_date IN DATE
3884 , p_scheduled_end_date IN DATE
3885 , p_task_assignment_id IN NUMBER
3886 , p_ta_object_version_number IN OUT NOCOPY NUMBER
3887 , p_sched_travel_distance IN NUMBER
3888 , p_sched_travel_duration IN NUMBER
3889 , p_sched_travel_duration_uom IN VARCHAR2
3890 ) IS
3891 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TASK_AND_ASSIGNMENT';
3892 l_api_version CONSTANT NUMBER := 1.0;
3893 l_scheduled_start DATE;
3894 l_scheduled_end DATE;
3895 l_distance NUMBER;
3896 l_duration NUMBER;
3897 l_duration_uom VARCHAR2(3);
3898
3899 BEGIN
3900 SAVEPOINT csf_update_task_and_assignment;
3901
3902 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3903 RAISE fnd_api.g_exc_unexpected_error;
3904 END IF;
3905
3906 IF fnd_api.to_boolean(p_init_msg_list) THEN
3907 fnd_msg_pub.initialize;
3908 END IF;
3909
3910 x_return_status := fnd_api.g_ret_sts_success;
3911
3912 -- Update the Task Assignment if any columns are changing
3913 IF p_sched_travel_distance IS NOT NULL
3914 OR p_sched_travel_duration IS NOT NULL
3915 OR p_sched_travel_duration_uom IS NOT NULL
3916 THEN
3917 jtf_task_assignments_pub.update_task_assignment(
3918 p_api_version => 1.0
3919 , x_return_status => x_return_status
3920 , x_msg_count => x_msg_count
3921 , x_msg_data => x_msg_data
3922 , p_task_assignment_id => p_task_assignment_id
3923 , p_object_version_number => p_ta_object_version_number
3924 , p_sched_travel_distance => p_sched_travel_distance
3925 , p_sched_travel_duration => p_sched_travel_duration
3926 , p_sched_travel_duration_uom => p_sched_travel_duration_uom
3927 , p_enable_workflow => fnd_api.g_miss_char
3928 , p_abort_workflow => fnd_api.g_miss_char
3929 );
3930 IF x_return_status = fnd_api.g_ret_sts_error THEN
3931 RAISE fnd_api.g_exc_error;
3932 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3933 RAISE fnd_api.g_exc_unexpected_error;
3934 END IF;
3935 END IF;
3936
3937 -- Update the Task if any columns are changing
3938 IF p_scheduled_start_date IS NOT NULL OR p_scheduled_end_date IS NOT NULL THEN
3939 jtf_tasks_pub.update_task(
3940 p_api_version => 1.0
3941 , x_return_status => x_return_status
3942 , x_msg_count => x_msg_count
3943 , x_msg_data => x_msg_data
3944 , p_task_id => p_task_id
3945 , p_object_version_number => p_object_version_number
3946 , p_scheduled_start_date => p_scheduled_start_date
3947 , p_scheduled_end_date => p_scheduled_end_date
3948 , p_enable_workflow => fnd_api.g_miss_char
3949 , p_abort_workflow => fnd_api.g_miss_char
3950 );
3951
3952 IF x_return_status = fnd_api.g_ret_sts_error THEN
3953 RAISE fnd_api.g_exc_error;
3954 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3955 RAISE fnd_api.g_exc_unexpected_error;
3956 END IF;
3957 END IF;
3958
3959 IF fnd_api.to_boolean(p_commit) THEN
3960 COMMIT WORK;
3961 END IF;
3962 EXCEPTION
3963 WHEN fnd_api.g_exc_error THEN
3964 ROLLBACK TO csf_update_task_and_assignment;
3965 x_return_status := fnd_api.g_ret_sts_error;
3966 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3967 WHEN fnd_api.g_exc_unexpected_error THEN
3968 ROLLBACK TO csf_update_task_and_assignment;
3969 x_return_status := fnd_api.g_ret_sts_unexp_error;
3970 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3971 WHEN OTHERS THEN
3972 x_return_status := fnd_api.g_ret_sts_unexp_error;
3973 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3974 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3975 END IF;
3976 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3977 ROLLBACK TO csf_update_task_and_assignment;
3978 END update_task_and_assignment;
3979
3980 PROCEDURE update_task_longer_than_shift(
3981 p_api_version IN NUMBER
3982 , p_init_msg_list IN VARCHAR2
3983 , p_commit IN VARCHAR2
3984 , x_return_status OUT NOCOPY VARCHAR2
3985 , x_msg_count OUT NOCOPY NUMBER
3986 , x_msg_data OUT NOCOPY VARCHAR2
3987 , p_task_id IN NUMBER
3988 , p_object_version_number IN OUT NOCOPY NUMBER
3989 , p_planned_start_date IN DATE
3990 , p_planned_end_date IN DATE
3991 , p_action IN PLS_INTEGER
3992 , p_task_status_id IN NUMBER
3993 ) IS
3994 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TASK_LONGER_THAN_SHIFT';
3995 l_api_version CONSTANT NUMBER := 1.0;
3996
3997 CURSOR c_parent_task_info IS
3998 SELECT t.task_id
3999 , t.task_status_id
4000 , t.scheduled_start_date
4001 , t.scheduled_end_date
4002 , t.planned_effort
4003 , t.planned_effort_uom
4004 , t.task_split_flag
4005 FROM jtf_tasks_b t
4006 WHERE t.task_id = p_task_id;
4007
4008 CURSOR c_child_tasks IS
4009 SELECT t.task_id
4010 , t.object_version_number task_ovn
4011 , t.task_status_id
4012 , ta.task_assignment_id
4013 , ta.object_version_number task_assignment_ovn
4014 , ta.assignment_status_id
4015 FROM jtf_tasks_b t ,jtf_task_statuses_b ts ,jtf_task_assignments ta
4016 WHERE t.parent_task_id = p_task_id
4017 AND NVL(t.deleted_flag, 'N') <> 'Y'
4018 AND ts.task_status_id = t.task_status_id
4019 AND NVL(ts.cancelled_flag, 'N') <> 'Y'
4020 AND t.task_id = ta.task_id
4021 AND ta.assignment_status_id = ts.task_status_id;
4022
4023 l_parent_task_info c_parent_task_info%ROWTYPE;
4024 l_scheduled_start DATE;
4025 l_scheduled_end DATE;
4026 l_task_split_flag VARCHAR2(1);
4027 BEGIN
4028 SAVEPOINT update_task_longer_than_shift;
4029
4030 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
4031 RAISE fnd_api.g_exc_unexpected_error;
4032 END IF;
4033
4034 IF fnd_api.to_boolean(p_init_msg_list) THEN
4035 fnd_msg_pub.initialize;
4036 END IF;
4037
4038 x_return_status := fnd_api.g_ret_sts_success;
4039
4040 -- Get the Task Information
4041 OPEN c_parent_task_info;
4042 FETCH c_parent_task_info INTO l_parent_task_info;
4043 CLOSE c_parent_task_info;
4044
4045 IF l_parent_task_info.task_id IS NULL THEN
4046 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'NO_DATA_FOUND JTF_TASKS_B.TASK_ID = ' || p_task_id);
4047 RAISE fnd_api.g_exc_unexpected_error;
4048 END IF;
4049
4050 -- Find out whether the new Task Status is valid.
4051 IF p_task_status_id <> fnd_api.g_miss_num THEN
4052 validate_status_change(l_parent_task_info.task_status_id, p_task_status_id);
4053 END IF;
4054
4055 IF p_action = g_action_normal_to_parent THEN
4056 -- Correct the Parent Task Information based on current Child Tasks
4057 sync_parent_with_child(
4058 p_api_version => 1.0
4059 , p_init_msg_list => fnd_api.g_false
4060 , p_commit => fnd_api.g_false
4061 , x_return_status => x_return_status
4062 , x_msg_count => x_msg_count
4063 , x_msg_data => x_msg_data
4064 , p_parent_task_id => p_task_id
4065 , p_parent_version_number => p_object_version_number
4066 , p_planned_start_date => p_planned_start_date
4067 , p_planned_end_date => p_planned_end_date
4068 );
4069 IF x_return_status = fnd_api.g_ret_sts_error THEN
4070 RAISE fnd_api.g_exc_error;
4071 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4072 RAISE fnd_api.g_exc_unexpected_error;
4073 END IF;
4074
4075 -- Correct the Child Task's Information
4076 sync_child_from_parent(
4077 p_api_version => 1.0
4078 , p_init_msg_list => fnd_api.g_false
4079 , p_commit => fnd_api.g_false
4080 , x_return_status => x_return_status
4081 , x_msg_count => x_msg_count
4082 , x_msg_data => x_msg_data
4083 , p_parent_task_id => p_task_id
4084 );
4085 IF x_return_status = fnd_api.g_ret_sts_error THEN
4086 RAISE fnd_api.g_exc_error;
4087 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4088 RAISE fnd_api.g_exc_unexpected_error;
4089 END IF;
4090 ELSIF p_action = g_action_parent_to_normal THEN
4091 jtf_tasks_pub.update_task(
4092 p_api_version => 1.0
4093 , x_return_status => x_return_status
4094 , x_msg_count => x_msg_count
4095 , x_msg_data => x_msg_data
4096 , p_task_id => p_task_id
4097 , p_object_version_number => p_object_version_number
4098 , p_task_status_id => p_task_status_id
4099 , p_scheduled_start_date => NULL
4100 , p_scheduled_end_date => NULL
4101 , p_task_split_flag => NULL
4102 , p_actual_start_date => NULL
4103 , p_actual_end_date => NULL
4104 , p_actual_effort => NULL
4105 , p_actual_effort_uom => NULL
4106 , p_enable_workflow => fnd_api.g_miss_char
4107 , p_abort_workflow => fnd_api.g_miss_char
4108 );
4109
4110 IF x_return_status = fnd_api.g_ret_sts_error THEN
4111 RAISE fnd_api.g_exc_error;
4112 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4113 RAISE fnd_api.g_exc_unexpected_error;
4114 END IF;
4115
4116 -- cancel all children if parent is changed back to normal.
4117 FOR child_task IN c_child_tasks LOOP
4118 unassign_task(
4119 p_api_version => 1.0
4120 , p_init_msg_list => fnd_api.g_false
4121 , p_commit => fnd_api.g_false
4122 , x_return_status => x_return_status
4123 , x_msg_count => x_msg_count
4124 , x_msg_data => x_msg_data
4125 , p_task_id => child_task.task_id
4126 , p_object_version_number => child_task.task_ovn
4127 , p_task_status_id => g_cancelled
4128 , p_task_assignment_id => child_task.task_assignment_id
4129 , p_ta_object_version_number => child_task.task_assignment_ovn
4130 , p_assignment_status_id => g_cancelled
4131 );
4132 IF x_return_status = fnd_api.g_ret_sts_error THEN
4133 RAISE fnd_api.g_exc_error;
4134 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4135 RAISE fnd_api.g_exc_unexpected_error;
4136 END IF;
4137 END LOOP;
4138 END IF;
4139
4140 IF fnd_api.to_boolean(p_commit) THEN
4141 COMMIT WORK;
4142 END IF;
4143 EXCEPTION
4144 WHEN fnd_api.g_exc_error THEN
4145 ROLLBACK TO update_task_longer_than_shift;
4146 x_return_status := fnd_api.g_ret_sts_error;
4147 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4148 WHEN fnd_api.g_exc_unexpected_error THEN
4149 ROLLBACK TO update_task_longer_than_shift;
4150 x_return_status := fnd_api.g_ret_sts_unexp_error;
4151 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4152 WHEN OTHERS THEN
4153 x_return_status := fnd_api.g_ret_sts_unexp_error;
4154 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4155 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4156 END IF;
4157 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4158 ROLLBACK TO update_task_longer_than_shift;
4159 END update_task_longer_than_shift;
4160
4161 PROCEDURE create_child_task(
4162 p_api_version IN NUMBER
4163 , p_init_msg_list IN VARCHAR2
4164 , p_commit IN VARCHAR2
4165 , x_return_status OUT NOCOPY VARCHAR2
4166 , x_msg_count OUT NOCOPY NUMBER
4167 , x_msg_data OUT NOCOPY VARCHAR2
4168 , p_parent_task_id IN NUMBER
4169 , p_task_status_id IN NUMBER
4170 , p_planned_effort IN NUMBER
4171 , p_planned_effort_uom IN VARCHAR2
4172 , p_bound_mode_code IN VARCHAR2
4173 , p_soft_bound_flag IN VARCHAR2
4174 , p_scheduled_start_date IN DATE
4175 , p_scheduled_end_date IN DATE
4176 , p_assignment_status_id IN NUMBER
4177 , p_resource_id IN NUMBER
4178 , p_resource_type IN VARCHAR2
4179 , p_object_capacity_id IN NUMBER
4180 , p_sched_travel_distance IN NUMBER
4181 , p_sched_travel_duration IN NUMBER
4182 , p_sched_travel_duration_uom IN VARCHAR2
4183 , p_child_position IN VARCHAR2
4184 , p_child_sequence_num IN NUMBER
4185 , x_task_id OUT NOCOPY NUMBER
4186 , x_object_version_number OUT NOCOPY NUMBER
4187 , x_task_assignment_id OUT NOCOPY NUMBER
4188 ) IS
4189 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_CHILD_TASK';
4190 l_api_version CONSTANT NUMBER := 1.0;
4191
4192 CURSOR c_parent_task_info IS
4193 SELECT t.task_name
4194 , t.description
4195 , t.task_type_id
4196 , t.task_priority_id
4197 , t.address_id
4198 , t.customer_id
4199 , t.source_object_type_code
4200 , t.source_object_id
4201 , t.source_object_name
4202 , t.owner_type_code
4203 , t.owner_id
4204 , t.task_confirmation_status
4205 , t.task_confirmation_counter
4206 , t.cust_account_id
4207 , t.planned_effort_uom
4208 , t.attribute1
4209 , t.attribute2
4210 , t.attribute3
4211 , t.attribute4
4212 , t.attribute5
4213 , t.attribute6
4214 , t.attribute7
4215 , t.attribute8
4216 , t.attribute9
4217 , t.attribute10
4218 , t.attribute11
4219 , t.attribute12
4220 , t.attribute13
4221 , t.attribute14
4222 , t.attribute15
4223 , t.attribute_category
4224 FROM jtf_tasks_vl t
4225 WHERE t.task_id = p_parent_task_id;
4226
4227 l_parent_task_info c_parent_task_info%ROWTYPE;
4228 BEGIN
4229 SAVEPOINT csf_create_child_task;
4230
4231 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
4232 RAISE fnd_api.g_exc_unexpected_error;
4233 END IF;
4234
4235 IF fnd_api.to_boolean(p_init_msg_list) THEN
4236 fnd_msg_pub.initialize;
4237 END IF;
4238
4239 x_return_status := fnd_api.g_ret_sts_success;
4240
4241 -- Get the Parent Task Information
4242 OPEN c_parent_task_info;
4243 FETCH c_parent_task_info INTO l_parent_task_info;
4244 IF c_parent_task_info%NOTFOUND THEN
4245 CLOSE c_parent_task_info;
4246 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'NO_DATA_FOUND JTF_TASKS_B.TASK_ID = ' || p_parent_task_id);
4247 RAISE fnd_api.g_exc_unexpected_error;
4248 END IF;
4249 CLOSE c_parent_task_info;
4250
4251 -- Create the Child Task using Parent Task Information
4252 -- (Set Zero Length Planned Window at Scheduled Start, Bound Mode code in BTS)
4253 jtf_tasks_pub.create_task(
4254 p_api_version => 1.0
4255 , p_init_msg_list => fnd_api.g_false
4256 , p_commit => fnd_api.g_false
4257 , x_return_status => x_return_status
4258 , x_msg_count => x_msg_count
4259 , x_msg_data => x_msg_data
4260 , p_task_name => l_parent_task_info.task_name
4261 , p_description => l_parent_task_info.description
4262 , p_task_type_id => l_parent_task_info.task_type_id
4263 , p_task_status_id => p_task_status_id
4264 , p_task_priority_id => l_parent_task_info.task_priority_id
4265 , p_owner_id => l_parent_task_info.owner_id
4266 , p_owner_type_code => l_parent_task_info.owner_type_code
4267 , p_customer_id => l_parent_task_info.customer_id
4268 , p_address_id => l_parent_task_info.address_id
4269 , p_planned_start_date => p_scheduled_start_date
4270 , p_planned_end_date => p_scheduled_start_date
4271 , p_scheduled_start_date => p_scheduled_start_date
4272 , p_scheduled_end_date => p_scheduled_end_date
4273 , p_source_object_type_code => l_parent_task_info.source_object_type_code
4274 , p_source_object_id => l_parent_task_info.source_object_id
4275 , p_source_object_name => l_parent_task_info.source_object_name
4276 , p_planned_effort => p_planned_effort
4277 , p_planned_effort_uom => p_planned_effort_uom
4278 , p_bound_mode_code => p_bound_mode_code
4279 , p_soft_bound_flag => p_soft_bound_flag
4280 , p_parent_task_id => p_parent_task_id
4281 , p_cust_account_id => l_parent_task_info.cust_account_id
4282 , p_enable_workflow => NULL
4283 , p_abort_workflow => NULL
4284 , p_task_split_flag => 'D'
4285 , p_child_position => NVL(p_child_position, 'N')
4286 , p_child_sequence_num => p_child_sequence_num
4287 , p_attribute1 => l_parent_task_info.attribute1
4288 , p_attribute2 => l_parent_task_info.attribute2
4289 , p_attribute3 => l_parent_task_info.attribute3
4290 , p_attribute4 => l_parent_task_info.attribute4
4291 , p_attribute5 => l_parent_task_info.attribute5
4292 , p_attribute6 => l_parent_task_info.attribute6
4293 , p_attribute7 => l_parent_task_info.attribute7
4294 , p_attribute8 => l_parent_task_info.attribute8
4295 , p_attribute9 => l_parent_task_info.attribute9
4296 , p_attribute10 => l_parent_task_info.attribute10
4297 , p_attribute11 => l_parent_task_info.attribute11
4298 , p_attribute12 => l_parent_task_info.attribute12
4299 , p_attribute13 => l_parent_task_info.attribute13
4300 , p_attribute14 => l_parent_task_info.attribute14
4301 , p_attribute15 => l_parent_task_info.attribute15
4302 , p_attribute_category => l_parent_task_info.attribute_category
4303 , x_task_id => x_task_id
4304 );
4305 IF x_return_status = fnd_api.g_ret_sts_error THEN
4306 RAISE fnd_api.g_exc_error;
4307 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4308 RAISE fnd_api.g_exc_unexpected_error;
4309 END IF;
4310 x_object_version_number := 1;
4311
4312 -- Copy Task Confirmation Values.
4313 IF l_parent_task_info.task_confirmation_status = 'N' THEN
4314 NULL;
4315 -- JTF automatically creates Task with Confirmation Status as N and
4316 -- Counter as ZERO. Thus there is no need for another uncessary update.
4317 ELSIF l_parent_task_info.task_confirmation_status = 'R' THEN
4318 jtf_task_confirmation_pub.set_confirmation_required(
4319 p_api_version => 1.0
4320 , p_init_msg_list => fnd_api.g_false
4321 , p_commit => fnd_api.g_false
4322 , x_return_status => x_return_status
4323 , x_msg_count => x_msg_count
4324 , x_msg_data => x_msg_data
4325 , p_task_id => x_task_id
4326 , p_object_version_number => x_object_version_number
4327 );
4328 IF x_return_status = fnd_api.g_ret_sts_error THEN
4329 RAISE fnd_api.g_exc_error;
4330 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4331 RAISE fnd_api.g_exc_unexpected_error;
4332 END IF;
4333 ELSIF l_parent_task_info.task_confirmation_status = 'C' THEN
4334 jtf_task_confirmation_pub.set_confirmation_confirmed(
4335 p_api_version => 1.0
4336 , p_init_msg_list => fnd_api.g_false
4337 , p_commit => fnd_api.g_false
4338 , x_return_status => x_return_status
4339 , x_msg_count => x_msg_count
4340 , x_msg_data => x_msg_data
4341 , p_task_id => x_task_id
4342 , p_object_version_number => x_object_version_number
4343 );
4344 IF x_return_status = fnd_api.g_ret_sts_error THEN
4345 RAISE fnd_api.g_exc_error;
4346 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4347 RAISE fnd_api.g_exc_unexpected_error;
4348 END IF;
4349 IF l_parent_task_info.task_confirmation_counter > 0 THEN
4350 -- This is one horrible way of incrementing the counter. JTF has not given
4351 -- a API to set it directly. This way will increase the Object Version
4352 -- Number for each increase..
4353 FOR k IN 1 .. l_parent_task_info.task_confirmation_counter LOOP
4354 jtf_task_confirmation_pub.increase_counter(
4355 p_api_version => 1.0
4356 , p_init_msg_list => fnd_api.g_false
4357 , p_commit => fnd_api.g_false
4358 , x_return_status => x_return_status
4359 , x_msg_count => x_msg_count
4360 , x_msg_data => x_msg_data
4361 , p_task_id => x_task_id
4362 , p_object_version_number => x_object_version_number
4363 );
4364 IF x_return_status = fnd_api.g_ret_sts_error THEN
4365 RAISE fnd_api.g_exc_error;
4366 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4367 RAISE fnd_api.g_exc_unexpected_error;
4368 END IF;
4369 END LOOP;
4370 END IF;
4371 END IF;
4372
4373 -- Create the Task Assignment
4374 jtf_task_assignments_pub.create_task_assignment(
4375 p_api_version => 1.0
4376 , x_return_status => x_return_status
4377 , x_msg_count => x_msg_count
4378 , x_msg_data => x_msg_data
4379 , p_task_id => x_task_id
4380 , p_resource_id => p_resource_id
4381 , p_resource_type_code => p_resource_type
4382 , p_assignment_status_id => p_assignment_status_id
4383 , p_object_capacity_id => p_object_capacity_id
4384 , p_sched_travel_distance => p_sched_travel_distance
4385 , p_sched_travel_duration => p_sched_travel_duration
4386 , p_sched_travel_duration_uom => p_sched_travel_duration_uom
4387 , p_enable_workflow => NULL
4388 , p_abort_workflow => NULL
4389 , p_free_busy_type => NULL
4390 , x_task_assignment_id => x_task_assignment_id
4391 );
4392
4393 IF x_return_status = fnd_api.g_ret_sts_error THEN
4394 RAISE fnd_api.g_exc_error;
4395 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4396 RAISE fnd_api.g_exc_unexpected_error;
4397 END IF;
4398
4399 IF fnd_api.to_boolean(p_commit) THEN
4400 COMMIT WORK;
4401 END IF;
4402
4403 EXCEPTION
4404 WHEN fnd_api.g_exc_error THEN
4405 ROLLBACK TO csf_create_child_task;
4406 x_return_status := fnd_api.g_ret_sts_error;
4407 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4408 WHEN fnd_api.g_exc_unexpected_error THEN
4409 ROLLBACK TO csf_create_child_task;
4410 x_return_status := fnd_api.g_ret_sts_unexp_error;
4411 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4412 WHEN OTHERS THEN
4413 x_return_status := fnd_api.g_ret_sts_unexp_error;
4414 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4415 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4416 END IF;
4417 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4418 ROLLBACK TO csf_create_child_task;
4419 END create_child_task;
4420
4421 /**
4422 * Updates the customer confirmation for normal/child/parent task
4423 *
4424 * @param p_api_version API Version (1.0)
4425 * @param p_init_msg_list Initialize Message List
4426 * @param p_commit Commit the Work
4427 * @param x_return_status Return Status of the Procedure
4428 * @param x_msg_count Number of Messages in the Stack
4429 * @param x_msg_data Stack of Error Messages
4430 * @param p_task_id Task to be processed
4431 * @param p_object_version_number Object version of input task
4432 * @param p_action Whether Required/Received/Not Required
4433 * @param p_initiated Whether Customer or Dispatcher
4434 */
4435
4436 PROCEDURE update_cust_confirmation(
4437 p_api_version IN NUMBER
4438 , p_init_msg_list IN VARCHAR2
4439 , p_commit IN VARCHAR2
4440 , x_return_status OUT NOCOPY VARCHAR2
4441 , x_msg_count OUT NOCOPY NUMBER
4442 , x_msg_data OUT NOCOPY VARCHAR2
4443 , p_task_id IN NUMBER
4444 , p_object_version_number IN OUT NOCOPY NUMBER
4445 , p_action IN PLS_INTEGER
4446 , p_initiated IN PLS_INTEGER
4447 ) IS
4448 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CUST_CONFIRMATION';
4449 l_api_version CONSTANT NUMBER := 1.0;
4450 i PLS_INTEGER := 1;
4451
4452 CURSOR c_task_info (p_task_id NUMBER) IS
4453 SELECT t.task_id
4454 , t.task_split_flag
4455 , t.parent_task_id
4456 , t.task_confirmation_status
4457 FROM jtf_tasks_b t
4458 WHERE t.task_id = p_task_id;
4459
4460 CURSOR c_parent_child_tasks (p_task_id NUMBER) IS
4461 SELECT jtb.task_id
4462 , jtb.object_version_number
4463 FROM jtf_task_statuses_vl ts, jtf_tasks_b jtb
4464 WHERE jtb.parent_task_id = p_task_id
4465 AND ts.task_status_id = jtb.task_status_id
4466 AND jtb.task_split_flag = 'D'
4467 AND ( NVL(ts.on_hold_flag, 'N') = 'Y'
4468 OR NVL(ts.working_flag, 'N') = 'Y'
4469 OR NVL(ts.schedulable_flag, 'N') = 'Y'
4470 OR ( NVL(ts.assigned_flag, 'N') = 'Y'
4471 AND NVL(ts.closed_flag, 'N') <> 'Y'
4472 AND NVL(ts.approved_flag, 'N') <> 'Y'
4473 AND NVL(ts.completed_flag, 'N') <> 'Y'
4474 AND NVL(ts.rejected_flag, 'N') <> 'Y' ))
4475 UNION
4476 SELECT t.task_id
4477 , t.object_version_number
4478 FROM jtf_tasks_b t
4479 WHERE task_id = p_task_id;
4480
4481 l_cust_task_tbl jtf_number_table := jtf_number_table();
4482 l_cust_objver_tbl jtf_number_table := jtf_number_table();
4483 l_task_info c_task_info%ROWTYPE;
4484 BEGIN
4485 SAVEPOINT csf_update_cust_confirmation;
4486
4487 x_return_status := fnd_api.g_ret_sts_success;
4488
4489 IF fnd_api.to_boolean (p_init_msg_list) THEN
4490 fnd_msg_pub.initialize;
4491 END IF;
4492
4493 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
4494 RAISE fnd_api.g_exc_unexpected_error;
4495 END IF;
4496
4497 OPEN c_task_info(p_task_id);
4498 FETCH c_task_info INTO l_task_info;
4499 CLOSE c_task_info;
4500
4501 IF ( l_task_info.task_split_flag IS NULL ) THEN
4502 l_cust_task_tbl.extend();
4503 l_cust_objver_tbl.extend();
4504 l_cust_task_tbl(l_cust_task_tbl.last) := p_task_id;
4505 l_cust_objver_tbl(l_cust_objver_tbl.last) := p_object_version_number;
4506 ELSIF ( l_task_info.task_split_flag = 'M' ) THEN
4507 OPEN c_parent_child_tasks(l_task_info.task_id);
4508 FETCH c_parent_child_tasks BULK COLLECT INTO l_cust_task_tbl, l_cust_objver_tbl;
4509 CLOSE c_parent_child_tasks;
4510 ELSIF ( l_task_info.task_split_flag = 'D' ) THEN
4511 OPEN c_parent_child_tasks(l_task_info.parent_task_id);
4512 FETCH c_parent_child_tasks BULK COLLECT INTO l_cust_task_tbl, l_cust_objver_tbl;
4513 CLOSE c_parent_child_tasks;
4514 END IF;
4515
4516 i:= l_cust_task_tbl.first;
4517 WHILE i IS NOT null
4518 LOOP
4519 IF p_action = csf_tasks_pub.g_action_conf_to_received THEN
4520 jtf_task_confirmation_pub.set_confirmation_confirmed(
4521 p_api_version => 1.0
4522 , p_init_msg_list => fnd_api.g_false
4523 , p_commit => fnd_api.g_false
4524 , x_return_status => x_return_status
4525 , x_msg_count => x_msg_count
4526 , x_msg_data => x_msg_data
4527 , p_task_id => l_cust_task_tbl(i)
4528 , p_object_version_number => l_cust_objver_tbl(i)
4529 );
4530 ELSIF p_action = csf_tasks_pub.g_action_conf_to_required THEN
4531 jtf_task_confirmation_pub.set_confirmation_required(
4532 p_api_version => 1.0
4533 , p_init_msg_list => fnd_api.g_false
4534 , p_commit => fnd_api.g_false
4535 , x_return_status => x_return_status
4536 , x_msg_count => x_msg_count
4537 , x_msg_data => x_msg_data
4538 , p_task_id => l_cust_task_tbl(i)
4539 , p_object_version_number => l_cust_objver_tbl(i)
4540 );
4541 IF x_return_status = fnd_api.g_ret_sts_success THEN
4542 IF l_task_info.task_confirmation_status = 'C' THEN
4543 IF p_initiated = csf_tasks_pub.g_dispatcher_initiated THEN
4544 jtf_task_confirmation_pub.increase_counter(
4545 p_api_version => 1.0
4546 , p_init_msg_list => fnd_api.g_false
4547 , p_commit => fnd_api.g_false
4548 , x_return_status => x_return_status
4549 , x_msg_count => x_msg_count
4550 , x_msg_data => x_msg_data
4551 , p_task_id => l_cust_task_tbl(i)
4552 , p_object_version_number => l_cust_objver_tbl(i)
4553 );
4554 ELSIF p_initiated = csf_tasks_pub.g_customer_initiated THEN
4555 jtf_task_confirmation_pub.reset_counter(
4556 p_api_version => 1.0
4557 , p_commit => fnd_api.g_false
4558 , p_init_msg_list => fnd_api.g_false
4559 , p_object_version_number => l_cust_objver_tbl(i)
4560 , p_task_id => l_cust_task_tbl(i)
4561 , x_return_status => x_return_status
4562 , x_msg_count => x_msg_count
4563 , x_msg_data => x_msg_data
4564 );
4565 END IF;
4566 END IF;
4567 END IF;
4568 ELSIF p_action = csf_tasks_pub.g_action_conf_not_required THEN
4569 jtf_task_confirmation_pub.reset_confirmation_status(
4570 p_api_version => 1.0
4571 , p_init_msg_list => fnd_api.g_false
4572 , p_commit => fnd_api.g_false
4573 , x_return_status => x_return_status
4574 , x_msg_count => x_msg_count
4575 , x_msg_data => x_msg_data
4576 , p_task_id => l_cust_task_tbl(i)
4577 , p_object_version_number => l_cust_objver_tbl(i)
4578 );
4579 END IF;
4580
4581 IF x_return_status = fnd_api.g_ret_sts_error THEN
4582 RAISE fnd_api.g_exc_error;
4583 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4584 RAISE fnd_api.g_exc_unexpected_error;
4585 END IF;
4586
4587 IF ( p_task_id = l_cust_task_tbl(i) ) THEN
4588 p_object_version_number := l_cust_objver_tbl(i);
4589 END IF;
4590
4591 i := l_cust_task_tbl.next(i);
4592 END LOOP;
4593
4594 -- Standard check of p_commit
4595 IF fnd_api.to_boolean (p_commit) THEN
4596 COMMIT WORK;
4597 END IF;
4598
4599 EXCEPTION
4600 WHEN fnd_api.g_exc_error THEN
4601 ROLLBACK TO csf_update_cust_confirmation;
4602 x_return_status := fnd_api.g_ret_sts_error;
4603 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4604 WHEN fnd_api.g_exc_unexpected_error THEN
4605 ROLLBACK TO csf_update_cust_confirmation;
4606 x_return_status := fnd_api.g_ret_sts_unexp_error;
4607 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4608 WHEN OTHERS THEN
4609 x_return_status := fnd_api.g_ret_sts_unexp_error;
4610 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4611 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4612 END IF;
4613 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4614 ROLLBACK TO csf_update_cust_confirmation;
4615 END update_cust_confirmation;
4616
4617 FUNCTION get_task_location_id (
4618 p_task_id IN NUMBER
4619 , p_party_site_id IN NUMBER
4620 , p_location_id IN NUMBER
4621 ) RETURN NUMBER
4622 IS
4623 l_location_id NUMBER;
4624
4625 CURSOR c_ps_location IS
4626 SELECT ps.location_id
4627 FROM hz_party_sites ps
4628 WHERE ps.party_site_id = p_party_site_id;
4629
4630 CURSOR c_task_location IS
4631 SELECT NVL(t.location_id, ps.location_id)
4632 FROM jtf_tasks_b t
4633 , hz_party_sites ps
4634 WHERE t.task_id = p_task_id
4635 AND ps.party_site_id(+) = t.address_id;
4636
4637 BEGIN
4638 IF p_location_id IS NOT NULL THEN
4639 l_location_id := p_location_id;
4640 ELSIF p_party_site_id IS NOT NULL THEN
4641 OPEN c_ps_location;
4642 FETCH c_ps_location INTO l_location_id;
4643 CLOSE c_ps_location;
4644 ELSE
4645 OPEN c_task_location;
4646 FETCH c_task_location INTO l_location_id;
4647 CLOSE c_task_location;
4648 END IF;
4649
4650 RETURN l_location_id;
4651 EXCEPTION
4652 WHEN OTHERS THEN
4653 RETURN -1;
4654 END get_task_location_id;
4655
4656 FUNCTION get_task_address (
4657 p_task_id IN NUMBER
4658 , p_party_site_id IN NUMBER
4659 , p_location_id IN NUMBER
4660 , p_short_flag IN VARCHAR2
4661 ) RETURN VARCHAR2
4662 IS
4663 CURSOR c_location_info IS
4664 SELECT l.address1
4665 , l.address2
4666 , l.address3
4667 , l.address4
4668 , l.postal_code
4669 , l.city
4670 , l.state
4671 , l.province
4672 , l.country
4673 FROM hz_locations l
4674 WHERE l.location_id = p_location_id;
4675
4676 CURSOR c_ps_location_info IS
4677 SELECT l.address1
4678 , l.address2
4679 , l.address3
4680 , l.address4
4681 , l.postal_code
4682 , l.city
4683 , l.state
4684 , l.province
4685 , l.country
4686 FROM hz_party_sites ps
4687 , hz_locations l
4688 WHERE ps.party_site_id = p_party_site_id
4689 AND l.location_id = ps.location_id;
4690
4691 CURSOR c_task_location_info IS
4692 SELECT l.address1
4693 , l.address2
4694 , l.address3
4695 , l.address4
4696 , l.postal_code
4697 , l.city
4698 , l.state
4699 , l.province
4700 , l.country
4701 FROM jtf_tasks_b t
4702 , hz_party_sites ps
4703 , hz_locations l
4704 WHERE t.task_id = p_task_id
4705 AND ps.party_site_id(+) = t.address_id
4706 AND l.location_id = NVL(t.location_id, ps.location_id);
4707
4708 l_address VARCHAR2(1300);
4709 l_location_rec c_location_info%ROWTYPE;
4710 BEGIN
4711 IF p_location_id IS NOT NULL THEN
4712 OPEN c_location_info;
4713 FETCH c_location_info INTO l_location_rec;
4714 CLOSE c_location_info;
4715 ELSIF p_party_site_id IS NOT NULL THEN
4716 OPEN c_ps_location_info;
4717 FETCH c_ps_location_info INTO l_location_rec;
4718 CLOSE c_ps_location_info;
4719 ELSE
4720 OPEN c_task_location_info;
4721 FETCH c_task_location_info INTO l_location_rec;
4722 CLOSE c_task_location_info;
4723 END IF;
4724
4725 IF p_short_flag = 'Y' THEN
4726 IF l_location_rec.postal_code IS NOT NULL THEN
4727 l_address := l_location_rec.postal_code;
4728 ELSE
4729 l_address := l_location_rec.address1;
4730 END IF;
4731
4732 IF l_location_rec.city IS NOT NULL THEN
4733 l_address := l_address || ',' || l_location_rec.city;
4734 END IF;
4735
4736 IF l_location_rec.state IS NOT NULL THEN
4737 l_address := l_address || ',' || l_location_rec.state;
4738 ELSIF l_location_rec.province IS NOT NULL THEN
4739 l_address := l_address || ',' || l_location_rec.province;
4740 END IF;
4741 ELSE
4742 l_address := l_location_rec.address1;
4743 IF l_location_rec.address2 IS NOT NULL THEN
4744 l_address := l_address || ',' || l_location_rec.address2;
4745 END IF;
4746
4747 IF l_location_rec.address3 IS NOT NULL THEN
4748 l_address := l_address || ',' || l_location_rec.address3;
4749 END IF;
4750
4751 IF l_location_rec.address4 IS NOT NULL THEN
4752 l_address := l_address || ',' || l_location_rec.address4;
4753 END IF;
4754
4755 IF l_location_rec.postal_code IS NOT NULL THEN
4756 l_address := l_address || ',' || l_location_rec.postal_code;
4757 END IF;
4758
4759 IF l_location_rec.city IS NOT NULL THEN
4760 l_address := l_address || ',' || l_location_rec.city;
4761 END IF;
4762
4763 IF l_location_rec.state IS NOT NULL THEN
4764 l_address := l_address || ',' || l_location_rec.state;
4765 ELSIF l_location_rec.province IS NOT NULL THEN
4766 l_address := l_address || ',' || l_location_rec.province;
4767 END IF;
4768
4769 l_address := l_address || ',' || l_location_rec.country;
4770 END IF;
4771
4772 RETURN l_address;
4773 EXCEPTION
4774 WHEN OTHERS THEN
4775 RETURN NULL;
4776 END get_task_address;
4777
4778 /**
4779 * Gets the Task Effort conditionally converted to the Default UOM as given by
4780 * the profile CSF: Default Effort UOM by calling
4781 * CSF_UTIL_PVT.GET_EFFORT_IN_DEFAULT_UOM function.
4782 * <br>
4783 * All parameters are optional. If Planned Effort, Planned Effort UOM and Task
4784 * Split Flag are passed, then it helps in better performance as JTF_TASKS_B
4785 * wont be queried to get those information. In case of better flexibility,
4786 * the caller can just pass the Task ID and the API will fetch the required
4787 * information. If case none of the required parameters are passed, the API returns
4788 * NULL.
4789 * <br>
4790 * Parent Task / Normal Tasks are created by the Teleservice Operators and therefore
4791 * its always better to represent them in the UOM they had given initially. Tasks
4792 * created as part of the Background processes like Child Tasks are always created
4793 * in Minutes by Scheduler and therefore it is incumbent upon us to represent
4794 * them in a proper UOM. Thus this API will convert the Planned Effort to the default
4795 * UOM only for Child Tasks and will merely act as a Concatenation Operator for
4796 * other Tasks. If you want to overrule this and want conversion to Default UOM
4797 * to take place for all Tasks, pass p_always_convert as FND_API.G_TRUE
4798 *
4799 * Also refer to the documentation on CSF_UTIL_PVT.GET_EFFORT_IN_DEFAULT_UOM.
4800 * <br>
4801 *
4802 * @param p_planned_effort Planned Effort to be converted
4803 * @param p_planned_effort_uom UOM of the above Effort
4804 * @param p_task_split_flag Determines whether the Task is Child / Other
4805 * @param p_task_id Task ID of the Task whose effort is to be converted
4806 * @param p_always_convert Overrule the condition and convert for all Tasks.
4807 *
4808 * @result Planned Effort appro converted to Default UOM.
4809 */
4810 FUNCTION get_task_effort_in_default_uom(
4811 p_planned_effort NUMBER
4812 , p_planned_effort_uom VARCHAR2
4813 , p_task_split_flag VARCHAR2
4814 , p_task_id NUMBER
4815 , p_always_convert VARCHAR2
4816 )
4817 RETURN VARCHAR2 IS
4818
4819 l_effort NUMBER;
4820 l_effort_uom jtf_tasks_b.planned_effort_uom%TYPE;
4821 l_task_split_flag jtf_tasks_b.task_split_flag%TYPE;
4822
4823 CURSOR c_task_info IS
4824 SELECT NVL(p_planned_effort, planned_effort) planned_effort
4825 , NVL(p_planned_effort_uom, planned_effort_uom) planned_effort_uom
4826 , decode(p_task_split_flag, '@', task_split_flag, p_task_split_flag) task_split_flag
4827 FROM jtf_tasks_b
4828 WHERE task_id = p_task_id;
4829 BEGIN
4830 l_effort := p_planned_effort;
4831 l_effort_uom := p_planned_effort_uom;
4832 l_task_split_flag := p_task_split_flag;
4833
4834 IF l_effort IS NULL
4835 OR l_effort_uom IS NULL
4836 OR ( l_task_split_flag = '@' AND NVL(p_always_convert, fnd_api.g_false) = fnd_api.g_false)
4837 THEN
4838 IF p_task_id IS NOT NULL THEN
4839 OPEN c_task_info;
4840 FETCH c_task_info INTO l_effort, l_effort_uom, l_task_split_flag;
4841 CLOSE c_task_info;
4842 END IF;
4843 END IF;
4844
4845 IF l_effort IS NULL OR l_effort_uom IS NULL THEN
4846 RETURN NULL;
4847 END IF;
4848
4849 IF NVL(l_task_split_flag, 'M') IN ('M', '@')
4850 AND NVL(p_always_convert, fnd_api.g_false) = fnd_api.g_false
4851 THEN
4852 RETURN l_effort || ' ' || csf_util_pvt.get_uom(l_effort_uom);
4853 END IF;
4854
4855 RETURN csf_util_pvt.get_effort_in_default_uom(l_effort, l_effort_uom);
4856 END get_task_effort_in_default_uom;
4857
4858 PROCEDURE get_contact_details(
4859 p_incident_id IN NUMBER
4860 , p_task_id IN NUMBER
4861 , x_last_name OUT NOCOPY VARCHAR2
4862 , x_first_name OUT NOCOPY VARCHAR2
4863 , x_title OUT NOCOPY VARCHAR2
4864 , x_phone OUT NOCOPY VARCHAR2
4865 , x_phone_ext OUT NOCOPY VARCHAR2
4866 , x_email_address OUT NOCOPY VARCHAR2
4867 ) IS
4868 l_contact_source CONSTANT VARCHAR2(10) := fnd_profile.value('CSF_DFLT_SOURCE_FOR_CONTACT');
4869
4870 l_contact_type cs_sr_contact_points_v.contact_type%TYPE;
4871 l_contact_point_id cs_sr_contact_points_v.contact_point_id%TYPE;
4872 l_party_id cs_sr_contact_points_v.party_id%TYPE;
4873
4874 -- Cursor to fetch the Task Contact Points
4875 CURSOR c_task_contact_points IS
4876 SELECT pc.person_last_name last_name
4877 , pc.person_first_name first_name
4878 , pc.person_title title
4879 , tp.phone_id
4880 FROM jtf_task_contacts tc
4881 , jtf_party_all_contacts_v pc
4882 , jtf_task_phones_v tp
4883 WHERE tc.task_id = p_task_id
4884 AND tc.contact_id IN (pc.party_id, pc.subject_party_id)
4885 AND tp.task_contact_id (+) = tc.task_contact_id;
4886
4887 -- Cursor to fetch the Service Request Contact Points
4888 CURSOR c_sr_contact_points IS
4889 SELECT sub_last_name last_name
4890 , sub_first_name first_name
4891 , sub_title title
4892 , contact_point_id
4893 , party_id
4894 , contact_type
4895 FROM cs_sr_contact_points_v
4896 WHERE incident_id = p_incident_id
4897 AND primary_flag = 'Y';
4898
4899 -- Cursor to fetch the Phone Number of Contacts
4900 CURSOR c_contact_phone IS
4901 SELECT cp.contact_point_type
4902 , DECODE(cp.phone_country_code, '', '', NULL, '', cp.phone_country_code || '-' )
4903 || DECODE(cp.phone_area_code, '', '', NULL, '', cp.phone_area_code || '-')
4904 || cp.phone_number phone
4905 , cp.phone_extension
4906 , cp.email_address
4907 FROM hz_contact_points cp
4908 , ar_lookups ar
4909 WHERE cp.contact_point_id = l_contact_point_id
4910 AND cp.contact_point_type IN ('EMAIL', 'PHONE')
4911 AND cp.phone_line_type = ar.lookup_code (+)
4912 AND ar.lookup_type(+) = 'PHONE_LINE_TYPE';
4913
4914 -- Cursor to fetch information regarding HRMS Employees
4915 -- We require joining again with cs_hz_sr_contact_points so that the OUTER
4916 -- Join on Phone ID works properly. If its a constant, it expects a NOT NULL
4917 -- Value.
4918 CURSOR c_emp_info IS
4919 SELECT p.last_name
4920 , p.first_name
4921 , p.title
4922 , pp.phone_number
4923 , p.email_address
4924 FROM cs_hz_sr_contact_points sr_cp
4925 , per_all_people_f p
4926 , per_phones pp
4927 , hr_lookups hrl
4928 WHERE sr_cp.incident_id = p_incident_id
4929 AND sr_cp.primary_flag = 'Y'
4930 AND p.person_id = sr_cp.party_id
4931 AND pp.phone_id(+) = sr_cp.contact_point_id
4932 AND pp.parent_table(+) = 'PER_ALL_PEOPLE_F'
4933 AND hrl.lookup_code(+) = pp.phone_type
4934 AND hrl.lookup_type(+) = 'PHONE_TYPE'
4935 ORDER BY p.effective_end_date desc;
4936
4937
4938 BEGIN
4939
4940 IF l_contact_source = 'TASK' THEN
4941 -- Fetch the Contact Points from the Task Data Model
4942 OPEN c_task_contact_points;
4943 FETCH c_task_contact_points INTO x_last_name, x_first_name, x_title, l_contact_point_id;
4944 CLOSE c_task_contact_points;
4945
4946 FOR v IN c_contact_phone LOOP
4947 IF v.contact_point_type = 'EMAIL' THEN
4948 x_email_address := v.email_address;
4949 ELSE
4950 x_phone := v.phone;
4951 x_phone_ext := v.phone_extension;
4952 END IF;
4953 END LOOP;
4954 ELSE
4955 -- Fetch the Contact Points from the SR Data Model
4956 OPEN c_sr_contact_points;
4957 FETCH c_sr_contact_points INTO x_last_name, x_first_name, x_title, l_contact_point_id, l_party_id, l_contact_type;
4958 CLOSE c_sr_contact_points;
4959
4960 IF l_contact_type = 'EMPLOYEE' THEN
4961 OPEN c_emp_info;
4962 FETCH c_emp_info INTO x_last_name, x_first_name, x_title, x_phone, x_email_address;
4963 CLOSE c_emp_info;
4964 ELSE
4965 FOR v IN c_contact_phone LOOP
4966 IF v.contact_point_type = 'EMAIL' THEN
4967 x_email_address := v.email_address;
4968 ELSE
4969 x_phone := v.phone;
4970 x_phone_ext := v.phone_extension;
4971 END IF;
4972 END LOOP;
4973 END IF;
4974 END IF;
4975
4976 END get_contact_details;
4977
4978 FUNCTION get_contact_details(p_incident_id NUMBER, p_task_id NUMBER)
4979 RETURN VARCHAR2 IS
4980 l_title VARCHAR2(60);
4981 l_first_name VARCHAR2(150);
4982 l_last_name VARCHAR2(150);
4983 l_phone VARCHAR2(50);
4984 l_extension VARCHAR2(20);
4985 l_email_address VARCHAR2(2000);
4986
4987 l_name VARCHAR2(500);
4988 BEGIN
4989 get_contact_details(
4990 p_incident_id => p_incident_id
4991 , p_task_id => p_task_id
4992 , x_last_name => l_last_name
4993 , x_first_name => l_first_name
4994 , x_title => l_title
4995 , x_phone => l_phone
4996 , x_phone_ext => l_extension
4997 , x_email_address => l_email_address
4998 );
4999
5000 l_name := '';
5001 IF l_title IS NOT NULL THEN
5002 l_name := l_title || ' ';
5003 END IF;
5004 IF l_first_name IS NOT NULL THEN
5005 l_name := l_name || l_first_name || ' ';
5006 END IF;
5007 IF l_last_name IS NOT NULL THEN
5008 l_name := l_name || l_last_name;
5009 END IF;
5010
5011 RETURN l_name || '@@' || l_phone || '@@' || l_extension || '@@' || l_email_address;
5012 END get_contact_details;
5013
5014 procedure create_personal_task(
5015 p_api_version in number
5016 , p_init_msg_list in varchar2
5017 , p_commit in varchar2
5018 , p_task_name in varchar2
5019 , p_description in varchar2
5020 , p_task_type_name in varchar2
5021 , p_task_type_id in number
5022 , p_task_status_name in varchar2
5023 , p_task_status_id in number
5024 , p_task_priority_name in varchar2
5025 , p_task_priority_id in number
5026 , p_owner_id in number
5027 , p_owner_type_code in varchar2
5028 , p_address_id in number
5029 , p_customer_id in number
5030 , p_planned_start_date in date
5031 , p_planned_end_date in date
5032 , p_scheduled_start_date in date
5033 , p_scheduled_end_date in date
5034 , p_source_object_type_code in varchar2
5035 , p_planned_effort in number
5036 , p_planned_effort_uom in varchar2
5037 , p_bound_mode_code in varchar2
5038 , p_soft_bound_flag in varchar2
5039 , p_task_assign_tbl jtf_tasks_pub.task_assign_tbl
5040 , p_type in varchar2
5041 , p_trip in number
5042 , x_return_status out nocopy varchar2
5043 , x_msg_count out nocopy number
5044 , x_msg_data out nocopy varchar2
5045 , x_task_id out nocopy number
5046 )
5047 is
5048 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_PERSONAL_TASK';
5049 l_api_version CONSTANT NUMBER := 1.0;
5050
5051 l_location number;
5052 l_obj number;
5053 x_object_version_number number;
5054 l_task_id number;
5055 l_task_assignment_id number;
5056 l_obj_number number;
5057 l_task_ovn number;
5058 l_ts number;
5059 l_addr number;
5060
5061 begin
5062 SAVEPOINT csf_create_per_task;
5063
5064 x_return_status := fnd_api.g_ret_sts_success;
5065
5066 IF fnd_api.to_boolean (p_init_msg_list) THEN
5067 fnd_msg_pub.initialize;
5068 END IF;
5069
5070
5071 jtf_tasks_pub.create_task(
5072 p_api_version => p_api_version
5073 , p_init_msg_list => p_init_msg_list
5074 , p_commit => p_commit
5075 , p_task_name => p_task_name
5076 , p_description => p_description
5077 , p_task_type_name => p_task_type_name
5078 , p_task_type_id => p_task_type_id
5079 , p_task_status_name => p_task_status_name
5080 , p_task_status_id => p_task_status_id
5081 , p_task_priority_name => p_task_priority_name
5082 , p_task_priority_id => p_task_priority_id
5083 , p_owner_id => p_owner_id
5084 , p_owner_type_code => p_owner_type_code
5085 , p_address_id => p_address_id
5086 , p_customer_id => p_customer_id
5087 , p_planned_start_date => p_planned_start_date
5088 , p_planned_end_date => p_planned_end_date
5089 , p_scheduled_start_date => p_scheduled_start_date
5090 , p_scheduled_end_date => p_scheduled_end_date
5091 , p_source_object_type_code => p_source_object_type_code
5092 , p_planned_effort => p_planned_effort
5093 , p_planned_effort_uom => p_planned_effort_uom
5094 , p_bound_mode_code => p_bound_mode_code
5095 , p_soft_bound_flag => p_soft_bound_flag
5096 , p_task_assign_tbl => p_task_assign_tbl
5097 , x_return_status => x_return_status
5098 , x_msg_count => x_msg_count
5099 , x_msg_data => x_msg_data
5100 , x_task_id => x_task_id
5101 );
5102 IF x_return_status = fnd_api.g_ret_sts_error THEN
5103 RAISE fnd_api.g_exc_error;
5104 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5105 RAISE fnd_api.g_exc_unexpected_error;
5106 END IF;
5107
5108 l_task_id := x_task_id;
5109
5110
5111
5112 IF fnd_api.to_boolean (p_commit) THEN
5113 COMMIT WORK;
5114 END IF;
5115
5116 Exception
5117 WHEN fnd_api.g_exc_error THEN
5118 ROLLBACK TO csf_create_per_task;
5119 x_return_status := fnd_api.g_ret_sts_error;
5120 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
5121 WHEN fnd_api.g_exc_unexpected_error THEN
5122 ROLLBACK TO csf_create_per_task;
5123 x_return_status := fnd_api.g_ret_sts_unexp_error;
5124 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
5125 WHEN OTHERS THEN
5126 x_return_status := fnd_api.g_ret_sts_unexp_error;
5127 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
5128 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
5129 END IF;
5130 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
5131 ROLLBACK TO csf_create_per_task;
5132 end;
5133
5134 procedure update_personal_task(
5135 p_api_version in number
5136 , p_init_msg_list in varchar2
5137 , p_commit in varchar2
5138 , p_task_id in NUMBER
5139 , p_task_name in varchar2
5140 , x_version in out nocopy number
5141 , p_description in VARCHAR2
5142 , p_task_type_id in number
5143 , p_task_status_id in number
5144 , p_task_priority_id in number
5145 , p_owner_id in number
5146 , p_owner_type_code in varchar2
5147 , p_address_id in number
5148 , p_customer_id in number
5149 , p_planned_start_date in date
5150 , p_planned_end_date in date
5151 , p_scheduled_start_date in date
5152 , p_scheduled_end_date in date
5153 , p_source_object_type_code in varchar2
5154 , p_planned_effort in number
5155 , p_planned_effort_uom in varchar2
5156 , p_bound_mode_code in varchar2
5157 , p_soft_bound_flag in varchar2
5158 , p_type in varchar2
5159 , p_trip in number
5160 , x_return_status out nocopy varchar2
5161 , x_msg_count out nocopy number
5162 , x_msg_data out nocopy varchar2
5163 )
5164 is
5165 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PERSONAL_TASK';
5166 l_api_version CONSTANT NUMBER := 1.0;
5167
5168 cursor c_obj(p_task number)
5169 is
5170 select object_version_number
5171 from jtf_tasks_b
5172 where task_id =p_task;
5173
5174 cursor c_task_ass(p_task_id number)
5175 is
5176 select jta.task_assignment_id
5177 from jtf_task_assignments jta,jtf_tasks_b jt
5178 where jt.task_id=p_task_id
5179 and jta.task_id=jt.task_id
5180 and jt.source_object_type_code = 'TASK'
5181 and jt.task_type_id not in (20,21);
5182
5183 l_location number;
5184 l_obj number;
5185 x_object_version_number number;
5186 l_obj_task number;
5187 l_ts number;
5188 l_task_assignment_id number;
5189
5190
5191 begin
5192
5193 SAVEPOINT csf_update_per_task;
5194
5195 x_return_status := fnd_api.g_ret_sts_success;
5196
5197 IF fnd_api.to_boolean (p_init_msg_list) THEN
5198 fnd_msg_pub.initialize;
5199 END IF;
5200
5201 open c_obj(p_task_id);
5202 fetch c_obj into l_obj;
5203 close c_obj;
5204
5205 csf_tasks_pub.update_task(
5206 p_api_version => p_api_version
5207 , p_init_msg_list => p_init_msg_list
5208 , p_commit => p_commit
5209 , p_task_id => p_task_id
5210 , p_object_version_number => x_version
5211 , p_task_name => p_task_name
5212 , p_description => p_description
5213 , p_task_type_id => p_task_type_id
5214 , p_task_status_id => p_task_status_id
5215 , p_task_priority_id => p_task_priority_id
5216 , p_owner_id => p_owner_id
5217 , p_owner_type_code => p_owner_type_code
5218 , p_address_id => p_address_id
5219 , p_customer_id => p_customer_id
5220 , p_planned_start_date => p_planned_start_date
5221 , p_planned_end_date => p_planned_end_date
5222 , p_scheduled_start_date => p_scheduled_start_date
5223 , p_scheduled_end_date => p_scheduled_end_date
5224 , p_source_object_type_code => p_source_object_type_code
5225 , p_planned_effort => p_planned_effort
5226 , p_planned_effort_uom => p_planned_effort_uom
5227 , p_bound_mode_code => p_bound_mode_code
5228 , p_soft_bound_flag => p_soft_bound_flag
5229 , x_return_status => x_return_status
5230 , x_msg_count => x_msg_count
5231 , x_msg_data => x_msg_data
5232 );
5233
5234 IF x_return_status = fnd_api.g_ret_sts_error THEN
5235 RAISE fnd_api.g_exc_error;
5236 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5237 RAISE fnd_api.g_exc_unexpected_error;
5238 END IF;
5239
5240 IF fnd_api.to_boolean (p_commit) THEN
5241 COMMIT WORK;
5242 END IF;
5243
5244 Exception
5245 WHEN fnd_api.g_exc_error THEN
5246 ROLLBACK TO csf_update_per_task;
5247 x_return_status := fnd_api.g_ret_sts_error;
5248 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
5249 WHEN fnd_api.g_exc_unexpected_error THEN
5250 ROLLBACK TO csf_update_per_task;
5251 x_return_status := fnd_api.g_ret_sts_unexp_error;
5252 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
5253 WHEN OTHERS THEN
5254 x_return_status := fnd_api.g_ret_sts_unexp_error;
5255 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
5256 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
5257 END IF;
5258 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
5259 ROLLBACK TO csf_update_per_task;
5260 end;
5261 PROCEDURE set_desc_field_attr(
5262 p_inst_flex_fld_tbl IN csf_tasks_pub.Inst_flexfld_rec_type
5263 ,p_return_status OUT NOCOPY VARCHAR2
5264 ,p_msg_count OUT NOCOPY NUMBER
5265 ,p_msg_data OUT NOCOPY VARCHAR2
5266 ,p_obj_ver_no OUT NOCOPY NUMBER
5267
5268 )
5269 IS
5270 l_api_name CONSTANT VARCHAR2 (30) := 'SET_DESC_FIELD_ATTR';
5271 l_upd_instance_rec csi_datastructures_pub.instance_rec;
5272 l_out_id_tbl csi_datastructures_pub.id_tbl;
5273 l_out_party_tbl csi_datastructures_pub.party_tbl;
5274 l_transaction_rec csi_datastructures_pub.transaction_rec;
5275 l_out_party_account_tbl csi_datastructures_pub.party_account_tbl;
5276 l_out_instance_asset_tbl csi_datastructures_pub.instance_asset_tbl;
5277 l_out_pricing_attribs_tbl csi_datastructures_pub.pricing_attribs_tbl;
5278 l_out_organization_units_tbl csi_datastructures_pub.organization_units_tbl;
5279 l_out_extend_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
5280 CURSOR c_instance_obj (p_instance_id number)
5281 IS
5282 SELECT object_version_number
5283 FROM CSI_ITEM_INSTANCES
5284 WHERE instance_id=p_instance_id;
5285
5286
5287
5288 --Below Variables are used for updating hz_parties flex field
5289 l_profile_id number;
5290 l_org_update_rec HZ_PARTY_V2PUB.organization_rec_type;
5291 l_party_update_rec HZ_PARTY_V2PUB.party_rec_type;
5292 l_party_object_version_number NUMBER;
5293
5294 BEGIN
5295 SAVEPOINT set_desc_field_attr;
5296
5297 IF p_inst_flex_fld_tbl.l_flex_fl_table = 'CSI_ITEM_INSTANCES'
5298 THEN
5299 l_upd_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
5300 l_upd_instance_rec.INSTANCE_ID := p_inst_flex_fld_tbl.l_instance_id;
5301 l_upd_instance_rec.INSTANCE_NUMBER := FND_API.G_MISS_CHAR;
5302 l_upd_instance_rec.EXTERNAL_REFERENCE := FND_API.G_MISS_CHAR;
5303 l_upd_instance_rec.INVENTORY_ITEM_ID := FND_API.G_MISS_NUM;
5304 l_upd_instance_rec.VLD_ORGANIZATION_ID := FND_API.G_MISS_NUM;
5305 l_upd_instance_rec.INVENTORY_REVISION := FND_API.G_MISS_CHAR;
5306 l_upd_instance_rec.INV_MASTER_ORGANIZATION_ID := FND_API.G_MISS_NUM;
5307 l_upd_instance_rec.SERIAL_NUMBER := FND_API.G_MISS_CHAR;
5308 l_upd_instance_rec.MFG_SERIAL_NUMBER_FLAG := FND_API.G_MISS_CHAR;
5309 l_upd_instance_rec.LOT_NUMBER := FND_API.G_MISS_CHAR;
5310 l_upd_instance_rec.QUANTITY := FND_API.G_MISS_NUM;
5311 l_upd_instance_rec.UNIT_OF_MEASURE := FND_API.G_MISS_CHAR;
5312 l_upd_instance_rec.ACCOUNTING_CLASS_CODE := FND_API.G_MISS_CHAR;
5313 l_upd_instance_rec.INSTANCE_CONDITION_ID := FND_API.G_MISS_NUM;
5314 l_upd_instance_rec.INSTANCE_STATUS_ID := FND_API.G_MISS_NUM;
5315 l_upd_instance_rec.CUSTOMER_VIEW_FLAG := FND_API.G_MISS_CHAR;
5316 l_upd_instance_rec.MERCHANT_VIEW_FLAG := FND_API.G_MISS_CHAR;
5317 l_upd_instance_rec.SELLABLE_FLAG := FND_API.G_MISS_CHAR;
5318 l_upd_instance_rec.SYSTEM_ID := FND_API.G_MISS_NUM;
5319 l_upd_instance_rec.INSTANCE_TYPE_CODE := FND_API.G_MISS_CHAR;
5320 l_upd_instance_rec.ACTIVE_START_DATE := FND_API.G_MISS_DATE;
5321 l_upd_instance_rec.ACTIVE_END_DATE := FND_API.G_MISS_DATE;
5322 l_upd_instance_rec.LOCATION_TYPE_CODE := FND_API.G_MISS_CHAR;
5323 l_upd_instance_rec.LOCATION_ID := FND_API.G_MISS_NUM;
5324 l_upd_instance_rec.INV_ORGANIZATION_ID := FND_API.G_MISS_NUM;
5325 l_upd_instance_rec.INV_SUBINVENTORY_NAME := FND_API.G_MISS_CHAR;
5326 l_upd_instance_rec.INV_LOCATOR_ID := FND_API.G_MISS_NUM;
5327 l_upd_instance_rec.PA_PROJECT_ID := FND_API.G_MISS_NUM;
5328 l_upd_instance_rec.PA_PROJECT_TASK_ID := FND_API.G_MISS_NUM;
5329 l_upd_instance_rec.IN_TRANSIT_ORDER_LINE_ID := FND_API.G_MISS_NUM;
5330 l_upd_instance_rec.WIP_JOB_ID := FND_API.G_MISS_NUM;
5331 l_upd_instance_rec.PO_ORDER_LINE_ID := FND_API.G_MISS_NUM;
5332 l_upd_instance_rec.LAST_OE_ORDER_LINE_ID := FND_API.G_MISS_NUM;
5333 l_upd_instance_rec.LAST_OE_RMA_LINE_ID := FND_API.G_MISS_NUM;
5334 l_upd_instance_rec.LAST_PO_PO_LINE_ID := FND_API.G_MISS_NUM;
5335 l_upd_instance_rec.LAST_OE_PO_NUMBER := FND_API.G_MISS_CHAR;
5336 l_upd_instance_rec.LAST_WIP_JOB_ID := FND_API.G_MISS_NUM;
5337 l_upd_instance_rec.LAST_PA_PROJECT_ID := FND_API.G_MISS_NUM;
5338 l_upd_instance_rec.LAST_PA_TASK_ID := FND_API.G_MISS_NUM;
5339 l_upd_instance_rec.LAST_OE_AGREEMENT_ID := FND_API.G_MISS_NUM;
5340 l_upd_instance_rec.INSTALL_DATE := FND_API.G_MISS_DATE;
5341 l_upd_instance_rec.MANUALLY_CREATED_FLAG := FND_API.G_MISS_CHAR;
5342 l_upd_instance_rec.RETURN_BY_DATE := FND_API.G_MISS_DATE;
5343 l_upd_instance_rec.ACTUAL_RETURN_DATE := FND_API.G_MISS_DATE;
5344 l_upd_instance_rec.CREATION_COMPLETE_FLAG := FND_API.G_MISS_CHAR;
5345 l_upd_instance_rec.COMPLETENESS_FLAG := FND_API.G_MISS_CHAR;
5346 l_upd_instance_rec.VERSION_LABEL := FND_API.G_MISS_CHAR;
5347 l_upd_instance_rec.VERSION_LABEL_DESCRIPTION := FND_API.G_MISS_CHAR;
5348 l_upd_instance_rec.OBJECT_VERSION_NUMBER := p_inst_flex_fld_tbl.l_obj_ver_number;
5349 l_upd_instance_rec.LAST_TXN_LINE_DETAIL_ID := FND_API.G_MISS_NUM;
5350 l_upd_instance_rec.INSTALL_LOCATION_TYPE_CODE := FND_API.G_MISS_CHAR;
5351 l_upd_instance_rec.INSTALL_LOCATION_ID := FND_API.G_MISS_NUM;
5352 l_upd_instance_rec.INSTANCE_USAGE_CODE := FND_API.G_MISS_CHAR;
5353 l_upd_instance_rec.CHECK_FOR_INSTANCE_EXPIRY := FND_API.G_FALSE;
5354 l_upd_instance_rec.PROCESSED_FLAG := FND_API.G_MISS_CHAR;
5355 l_upd_instance_rec.CALL_CONTRACTS := FND_API.G_FALSE;
5356 l_upd_instance_rec.INTERFACE_ID := FND_API.G_MISS_NUM;
5357 l_upd_instance_rec.GRP_CALL_CONTRACTS := FND_API.G_FALSE;
5358 l_upd_instance_rec.CONFIG_INST_HDR_ID := FND_API.G_MISS_NUM;
5359 l_upd_instance_rec.CONFIG_INST_REV_NUM := FND_API.G_MISS_NUM;
5360 l_upd_instance_rec.CONFIG_INST_ITEM_ID := FND_API.G_MISS_NUM;
5361 l_upd_instance_rec.CONFIG_VALID_STATUS := FND_API.G_MISS_CHAR;
5362 l_upd_instance_rec.INSTANCE_DESCRIPTION := FND_API.G_MISS_CHAR;
5363 l_upd_instance_rec.CALL_BATCH_VALIDATION := FND_API.G_FALSE;
5364 l_upd_instance_rec.REQUEST_ID := FND_API.G_MISS_NUM;
5365 l_upd_instance_rec.PROGRAM_APPLICATION_ID := FND_API.G_MISS_NUM;
5366 l_upd_instance_rec.PROGRAM_ID := FND_API.G_MISS_NUM;
5367 l_upd_instance_rec.PROGRAM_UPDATE_DATE := FND_API.G_MISS_DATE;
5368 l_upd_instance_rec.CASCADE_OWNERSHIP_FLAG := FND_API.G_MISS_CHAR;
5369 l_upd_instance_rec.NETWORK_ASSET_FLAG := FND_API.G_MISS_CHAR;
5370 l_upd_instance_rec.MAINTAINABLE_FLAG := FND_API.G_MISS_CHAR;
5371 l_upd_instance_rec.PN_LOCATION_ID := FND_API.G_MISS_NUM;
5372 l_upd_instance_rec.ASSET_CRITICALITY_CODE := FND_API.G_MISS_CHAR;
5373 l_upd_instance_rec.CATEGORY_ID := FND_API.G_MISS_NUM;
5374 l_upd_instance_rec.EQUIPMENT_GEN_OBJECT_ID := FND_API.G_MISS_NUM;
5375 l_upd_instance_rec.INSTANTIATION_FLAG := FND_API.G_MISS_CHAR;
5376 l_upd_instance_rec.LINEAR_LOCATION_ID := FND_API.G_MISS_NUM;
5377 l_upd_instance_rec.OPERATIONAL_LOG_FLAG := FND_API.G_MISS_CHAR;
5378 l_upd_instance_rec.CHECKIN_STATUS := FND_API.G_MISS_NUM;
5379 l_upd_instance_rec.SUPPLIER_WARRANTY_EXP_DATE := FND_API.G_MISS_DATE;
5380 l_upd_instance_rec.CONTEXT := p_inst_flex_fld_tbl.l_context ;
5381 l_upd_instance_rec.ATTRIBUTE1 := p_inst_flex_fld_tbl.ATTRIBUTE1 ;
5382 l_upd_instance_rec.ATTRIBUTE2 := p_inst_flex_fld_tbl.ATTRIBUTE2 ;
5383 l_upd_instance_rec.ATTRIBUTE3 := p_inst_flex_fld_tbl.ATTRIBUTE3 ;
5384 l_upd_instance_rec.ATTRIBUTE4 := p_inst_flex_fld_tbl.ATTRIBUTE4 ;
5385 l_upd_instance_rec.ATTRIBUTE5 := p_inst_flex_fld_tbl.ATTRIBUTE5 ;
5386 l_upd_instance_rec.ATTRIBUTE6 := p_inst_flex_fld_tbl.ATTRIBUTE6 ;
5387 l_upd_instance_rec.ATTRIBUTE7 := p_inst_flex_fld_tbl.ATTRIBUTE7 ;
5388 l_upd_instance_rec.ATTRIBUTE8 := p_inst_flex_fld_tbl.ATTRIBUTE8 ;
5389 l_upd_instance_rec.ATTRIBUTE9 := p_inst_flex_fld_tbl.ATTRIBUTE9 ;
5390 l_upd_instance_rec.ATTRIBUTE10 := p_inst_flex_fld_tbl.ATTRIBUTE10;
5391 l_upd_instance_rec.ATTRIBUTE11 := p_inst_flex_fld_tbl.ATTRIBUTE11;
5392 l_upd_instance_rec.ATTRIBUTE12 := p_inst_flex_fld_tbl.ATTRIBUTE12;
5393 l_upd_instance_rec.ATTRIBUTE13 := p_inst_flex_fld_tbl.ATTRIBUTE13;
5394 l_upd_instance_rec.ATTRIBUTE14 := p_inst_flex_fld_tbl.ATTRIBUTE14;
5395 l_upd_instance_rec.ATTRIBUTE15 := p_inst_flex_fld_tbl.ATTRIBUTE15;
5396 l_upd_instance_rec.ATTRIBUTE16 := p_inst_flex_fld_tbl.ATTRIBUTE16;
5397 l_upd_instance_rec.ATTRIBUTE17 := p_inst_flex_fld_tbl.ATTRIBUTE17;
5398 l_upd_instance_rec.ATTRIBUTE18 := p_inst_flex_fld_tbl.ATTRIBUTE18;
5399 l_upd_instance_rec.ATTRIBUTE19 := p_inst_flex_fld_tbl.ATTRIBUTE19;
5400 l_upd_instance_rec.ATTRIBUTE20 := p_inst_flex_fld_tbl.ATTRIBUTE20;
5401 l_upd_instance_rec.ATTRIBUTE21 := p_inst_flex_fld_tbl.ATTRIBUTE21;
5402 l_upd_instance_rec.ATTRIBUTE22 := p_inst_flex_fld_tbl.ATTRIBUTE22;
5403 l_upd_instance_rec.ATTRIBUTE23 := p_inst_flex_fld_tbl.ATTRIBUTE23;
5404 l_upd_instance_rec.ATTRIBUTE24 := p_inst_flex_fld_tbl.ATTRIBUTE24;
5405 l_upd_instance_rec.ATTRIBUTE25 := p_inst_flex_fld_tbl.ATTRIBUTE25;
5406 l_upd_instance_rec.ATTRIBUTE26 := p_inst_flex_fld_tbl.ATTRIBUTE26;
5407 l_upd_instance_rec.ATTRIBUTE27 := p_inst_flex_fld_tbl.ATTRIBUTE27;
5408 l_upd_instance_rec.ATTRIBUTE28 := p_inst_flex_fld_tbl.ATTRIBUTE28;
5409 l_upd_instance_rec.ATTRIBUTE29 := p_inst_flex_fld_tbl.ATTRIBUTE29;
5410 l_upd_instance_rec.ATTRIBUTE30 := p_inst_flex_fld_tbl.ATTRIBUTE30;
5411 l_upd_instance_rec.PURCHASE_UNIT_PRICE := FND_API.G_MISS_NUM;
5412 l_upd_instance_rec.PURCHASE_CURRENCY_CODE := FND_API.G_MISS_CHAR;
5413 l_upd_instance_rec.PAYABLES_UNIT_PRICE := FND_API.G_MISS_NUM;
5414 l_upd_instance_rec.PAYABLES_CURRENCY_CODE := FND_API.G_MISS_CHAR;
5415 l_upd_instance_rec.SALES_UNIT_PRICE := FND_API.G_MISS_NUM;
5416 l_upd_instance_rec.SALES_CURRENCY_CODE := FND_API.G_MISS_CHAR;
5417 l_upd_instance_rec.OPERATIONAL_STATUS_CODE := FND_API.G_MISS_CHAR;
5418 l_upd_instance_rec.DEPARTMENT_ID := fnd_api.g_miss_num;
5419 l_upd_instance_rec.WIP_ACCOUNTING_CLASS := fnd_api.g_miss_char;
5420 l_upd_instance_rec.AREA_ID := fnd_api.g_miss_num;
5421 l_upd_instance_rec.OWNER_PARTY_ID := fnd_api.g_miss_num;
5422 l_upd_instance_rec.SOURCE_CODE := FND_API.G_MISS_CHAR;
5423
5424 l_transaction_rec.transaction_id := FND_API.G_MISS_NUM ;
5425 l_transaction_rec.transaction_date := sysdate;
5426 l_transaction_rec.SOURCE_TRANSACTION_DATE := sysdate;
5427 l_transaction_rec.transaction_type_id := 55; -- this is transaction id for FIELD SERVICE IN csi_transactions
5428 l_transaction_rec.object_version_number := 1;
5429
5430 csi_item_instance_pub.update_item_instance
5431 (
5432 p_api_version => 1.0
5433 ,p_commit => fnd_api.g_false
5434 ,p_init_msg_list => fnd_api.g_true
5435 ,p_validation_level => fnd_api.G_VALID_LEVEL_NONE
5436 ,p_instance_rec => l_upd_instance_rec
5437 ,p_ext_attrib_values_tbl => l_out_extend_attrib_values_tbl
5438 ,p_party_tbl => l_out_party_tbl
5439 ,p_account_tbl => l_out_party_account_tbl
5440 ,p_pricing_attrib_tbl => l_out_pricing_attribs_tbl
5441 ,p_org_assignments_tbl => l_out_organization_units_tbl
5442 ,p_asset_assignment_tbl => l_out_instance_asset_tbl
5443 ,p_txn_rec => l_transaction_rec
5444 ,x_instance_id_lst => l_out_id_tbl
5445 ,x_return_status => p_return_status
5446 ,x_msg_count => p_msg_count
5447 ,x_msg_data => p_msg_data
5448 );
5449 IF p_return_status = fnd_api.g_ret_sts_success
5450 THEN
5451 OPEN c_instance_obj(l_upd_instance_rec.INSTANCE_ID);
5452 FETCH c_instance_obj INTO p_obj_ver_no;
5453 CLOSE c_instance_obj;
5454 END IF;
5455
5456 ELSIF p_inst_flex_fld_tbl.l_flex_fl_table = 'HZ_PARTIES'
5457 THEN
5458 l_party_update_rec.party_id := p_inst_flex_fld_tbl.l_party_id ;
5459 l_org_update_rec.party_rec := l_party_update_rec;
5460 l_org_update_rec.party_rec.Attribute_Category := NVL( p_inst_flex_fld_tbl.l_att_catogary , FND_API.G_MISS_CHAR);
5461 l_org_update_rec.party_rec.Attribute1 := NVL( p_inst_flex_fld_tbl.Attribute1 , FND_API.G_MISS_CHAR);
5462 l_org_update_rec.party_rec.Attribute2 := NVL( p_inst_flex_fld_tbl.Attribute2 , FND_API.G_MISS_CHAR);
5463 l_org_update_rec.party_rec.Attribute3 := NVL( p_inst_flex_fld_tbl.Attribute3 , FND_API.G_MISS_CHAR);
5464 l_org_update_rec.party_rec.Attribute4 := NVL( p_inst_flex_fld_tbl.Attribute4 , FND_API.G_MISS_CHAR);
5465 l_org_update_rec.party_rec.Attribute5 := NVL( p_inst_flex_fld_tbl.Attribute5 , FND_API.G_MISS_CHAR);
5466 l_org_update_rec.party_rec.Attribute6 := NVL( p_inst_flex_fld_tbl.Attribute6 , FND_API.G_MISS_CHAR);
5467 l_org_update_rec.party_rec.Attribute7 := NVL( p_inst_flex_fld_tbl.Attribute7 , FND_API.G_MISS_CHAR);
5468 l_org_update_rec.party_rec.Attribute8 := NVL( p_inst_flex_fld_tbl.Attribute8 , FND_API.G_MISS_CHAR);
5469
5470 l_org_update_rec.party_rec.Attribute9 := NVL( p_inst_flex_fld_tbl.Attribute9 , FND_API.G_MISS_CHAR);
5471 l_org_update_rec.party_rec.Attribute10 := NVL( p_inst_flex_fld_tbl.Attribute10 , FND_API.G_MISS_CHAR);
5472 l_org_update_rec.party_rec.Attribute11 := NVL( p_inst_flex_fld_tbl.Attribute11 , FND_API.G_MISS_CHAR);
5473 l_org_update_rec.party_rec.Attribute12 := NVL( p_inst_flex_fld_tbl.Attribute12 , FND_API.G_MISS_CHAR);
5474 l_org_update_rec.party_rec.Attribute13 := NVL( p_inst_flex_fld_tbl.Attribute13 , FND_API.G_MISS_CHAR);
5475 l_org_update_rec.party_rec.Attribute14 := NVL( p_inst_flex_fld_tbl.Attribute14 , FND_API.G_MISS_CHAR);
5476 l_org_update_rec.party_rec.Attribute15 := NVL( p_inst_flex_fld_tbl.Attribute15 , FND_API.G_MISS_CHAR);
5477 l_org_update_rec.party_rec.Attribute16 := NVL( p_inst_flex_fld_tbl.Attribute16 , FND_API.G_MISS_CHAR);
5478 l_org_update_rec.party_rec.Attribute17 := NVL( p_inst_flex_fld_tbl.Attribute17 , FND_API.G_MISS_CHAR);
5479 l_org_update_rec.party_rec.Attribute18 := NVL( p_inst_flex_fld_tbl.Attribute18 , FND_API.G_MISS_CHAR);
5480 l_org_update_rec.party_rec.Attribute19 := NVL( p_inst_flex_fld_tbl.Attribute19 , FND_API.G_MISS_CHAR);
5481
5482 l_org_update_rec.party_rec.Attribute20 := NVL( p_inst_flex_fld_tbl.Attribute20 , FND_API.G_MISS_CHAR);
5483 l_org_update_rec.party_rec.Attribute21 := NVL( p_inst_flex_fld_tbl.Attribute21 , FND_API.G_MISS_CHAR);
5484 l_org_update_rec.party_rec.Attribute22 := NVL( p_inst_flex_fld_tbl.Attribute22 , FND_API.G_MISS_CHAR);
5485 l_org_update_rec.party_rec.Attribute23 := NVL( p_inst_flex_fld_tbl.Attribute23 , FND_API.G_MISS_CHAR);
5486 l_org_update_rec.party_rec.Attribute24 := NVL( p_inst_flex_fld_tbl.Attribute24 , FND_API.G_MISS_CHAR);
5487 l_party_object_version_number := p_inst_flex_fld_tbl.l_obj_ver_number;
5488
5489 HZ_PARTY_V2PUB.update_Organization( p_init_msg_list => fnd_api.g_true,
5490 x_return_status => p_return_status,
5491 x_msg_count => p_msg_count,
5492 x_msg_data => p_msg_data,
5493 x_profile_id => l_profile_id,
5494 p_organization_rec => l_org_update_rec,
5495 p_party_object_version_number => l_party_object_version_number
5496 );
5497
5498 IF p_return_status = fnd_api.g_ret_sts_success
5499 THEN
5500 p_obj_ver_no := l_party_object_version_number;
5501 END IF;
5502
5503 END IF;
5504
5505 IF p_return_status = fnd_api.g_ret_sts_error THEN
5506 RAISE fnd_api.g_exc_error;
5507 ELSIF p_return_status = fnd_api.g_ret_sts_unexp_error THEN
5508 RAISE fnd_api.g_exc_unexpected_error;
5509 END IF;
5510 EXCEPTION
5511 WHEN fnd_api.g_exc_error THEN
5512 ROLLBACK TO set_desc_field_attr;
5513 p_return_status := fnd_api.g_ret_sts_error;
5514 fnd_msg_pub.count_and_get (p_count => p_msg_count, p_data => p_msg_data);
5515
5516 WHEN fnd_api.g_exc_unexpected_error THEN
5517 ROLLBACK TO set_desc_field_attr;
5518 p_return_status := fnd_api.g_ret_sts_unexp_error;
5519 fnd_msg_pub.count_and_get (p_count => p_msg_count, p_data => p_msg_data);
5520
5521 WHEN OTHERS THEN
5522 ROLLBACK TO set_desc_field_attr;
5523 p_return_status := fnd_api.g_ret_sts_unexp_error;
5524 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
5525 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
5526 END IF;
5527 fnd_msg_pub.count_and_get (p_count => p_msg_count, p_data => p_msg_data);
5528
5529 END set_desc_field_attr;
5530
5531
5532 PROCEDURE update_task_attr (
5533 p_api_version IN NUMBER
5534 , p_init_msg_list IN VARCHAR2
5535 , p_commit IN VARCHAR2
5536 , x_return_status OUT NOCOPY VARCHAR2
5537 , x_msg_count OUT NOCOPY NUMBER
5538 , x_msg_data OUT NOCOPY VARCHAR2
5539 , p_task_id IN NUMBER
5540 , p_object_version_number IN OUT NOCOPY NUMBER
5541 , p_scheduled_start_date IN DATE DEFAULT NULL
5542 , p_scheduled_end_date IN DATE DEFAULT NULL
5543 , p_planned_start_date IN DATE DEFAULT NULL
5544 , p_planned_end_date IN DATE DEFAULT NULL
5545 , p_task_priority_id IN NUMBER DEFAULT NULL
5546 , p_planned_effort IN NUMBER DEFAULT NULL
5547 , p_planned_effort_uom IN VARCHAR2 DEFAULT NULL
5548 , ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL
5549 , ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL
5550 , ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL
5551 , ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL
5552 , ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL
5553 , ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL
5554 , ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL
5555 , ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL
5556 , ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL
5557 , ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL
5558 , ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL
5559 , ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL
5560 , ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL
5561 , ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL
5562 , ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL
5563 , ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL
5564 ) IS
5565 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_TASK_STATUS';
5566 l_api_version CONSTANT NUMBER := 1.0;
5567 BEGIN
5568 -- Standard start of API savepoint
5569 SAVEPOINT update_task_attr;
5570
5571 -- Standard call to check for call compatibility
5572 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
5573 RAISE fnd_api.g_exc_unexpected_error;
5574 END IF;
5575
5576 -- Initialize message list if p_init_msg_list is set to TRUE
5577 IF fnd_api.to_boolean (p_init_msg_list) THEN
5578 fnd_msg_pub.initialize;
5579 END IF;
5580
5581 -- Initialize API return status to success
5582 x_return_status := fnd_api.g_ret_sts_success;
5583
5584 -- Update the Task with the new Task Status Information
5585 jtf_tasks_pub.update_task (
5586 p_api_version => 1.0
5587 , x_return_status => x_return_status
5588 , x_msg_count => x_msg_count
5589 , x_msg_data => x_msg_data
5590 , p_task_id => p_task_id
5591 , p_object_version_number => p_object_version_number
5592 , p_planned_start_date => p_planned_start_date
5593 , p_planned_end_date => p_planned_end_date
5594 , p_scheduled_start_date => p_scheduled_start_date
5595 , p_scheduled_end_date => p_scheduled_end_date
5596 , p_task_priority_id => p_task_priority_id
5597 , p_planned_effort => p_planned_effort
5598 , p_planned_effort_uom => p_planned_effort_uom
5599 , P_ATTRIBUTE1 => ATTRIBUTE1
5600 , P_ATTRIBUTE2 => ATTRIBUTE2
5601 , P_ATTRIBUTE3 => ATTRIBUTE3
5602 , P_ATTRIBUTE4 => ATTRIBUTE4
5603 , P_ATTRIBUTE5 => ATTRIBUTE5
5604 , P_ATTRIBUTE6 => ATTRIBUTE6
5605 , P_ATTRIBUTE7 => ATTRIBUTE7
5606 , P_ATTRIBUTE8 => ATTRIBUTE8
5607 , P_ATTRIBUTE9 => ATTRIBUTE9
5608 , P_ATTRIBUTE10 => ATTRIBUTE10
5609 , P_ATTRIBUTE11 => ATTRIBUTE11
5610 , P_ATTRIBUTE12 => ATTRIBUTE12
5611 , P_ATTRIBUTE13 => ATTRIBUTE13
5612 , P_ATTRIBUTE14 => ATTRIBUTE14
5613 , P_ATTRIBUTE15 => ATTRIBUTE15
5614 , P_ATTRIBUTE_CATEGORY => ATTRIBUTE_CATEGORY
5615 );
5616
5617 IF x_return_status = fnd_api.g_ret_sts_error THEN
5618 RAISE fnd_api.g_exc_error;
5619 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5620 RAISE fnd_api.g_exc_unexpected_error;
5621 END IF;
5622
5623 -- Standard check of p_commit
5624 IF fnd_api.to_boolean (p_commit) THEN
5625 null;
5626 END IF;
5627
5628 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
5629 EXCEPTION
5630 WHEN fnd_api.g_exc_error THEN
5631 ROLLBACK TO update_task_attr;
5632 x_return_status := fnd_api.g_ret_sts_error;
5633 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
5634 WHEN fnd_api.g_exc_unexpected_error THEN
5635 ROLLBACK TO update_task_attr;
5636 x_return_status := fnd_api.g_ret_sts_unexp_error;
5637 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
5638 WHEN OTHERS THEN
5639 x_return_status := fnd_api.g_ret_sts_unexp_error;
5640 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
5641 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
5642 END IF;
5643 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
5644 ROLLBACK TO update_task_attr;
5645 END update_task_attr;
5646
5647
5648
5649
5650 PROCEDURE get_site_details_for_task
5651 ( p_task_id IN NUMBER
5652 , p_party_id IN NUMBER
5653 , p_party_site_id IN NUMBER DEFAULT NULL
5654 , p_location_id IN NUMBER DEFAULT NULL
5655 , p_party_site_no OUT NOCOPY VARCHAR
5656 , p_party_site_nm OUT NOCOPY VARCHAR
5657 , p_party_site_add OUT NOCOPY VARCHAR
5658 , p_party_site_ph OUT NOCOPY VARCHAR
5659 )
5660 IS
5661 CURSOR c_get_site_details(p_location_id number,p_party_id_no number)
5662 IS
5663 SELECT s.party_site_number,
5664 s.party_site_name,
5665 s.addressee,
5666 csf_tasks_pub.return_primary_phone(s.party_site_id) phone_no
5667 FROM hz_locations l,
5668 hz_party_sites s,
5669 hz_parties p
5670 WHERE s.location_id = p_location_id
5671 AND l.location_id = s.location_id
5672 AND s.party_id = p.party_id
5673 AND p.party_id =p_party_id_no;
5674 l_location_id NUMBER;
5675 BEGIN
5676 l_location_id := csf_tasks_pub.get_task_location_id(p_task_id, p_party_site_id, p_location_id);
5677 OPEN c_get_site_details(l_location_id,p_party_id);
5678 FETCH c_get_site_details INTO p_party_site_no,p_party_site_nm,p_party_site_add,p_party_site_ph;
5679 END;
5680
5681
5682 PROCEDURE CREATE_ACC_HRS( p_task_id IN NUMBER
5683 , x_return_status OUT NOCOPY VARCHAR2
5684 , x_msg_count OUT NOCOPY NUMBER
5685 , x_msg_data OUT NOCOPY VARCHAR2)
5686 IS
5687
5688 --The below cursor +variables are added for access hours 8869998
5689
5690 l_acc_hr_id NUMBER;
5691 l_acchr_loc_id NUMBER;
5692 l_acchr_ct_site_id NUMBER;
5693 l_acchr_ct_id NUMBER;
5694 l_acchrs_found BOOLEAN;
5695 l_address_id_to_pass NUMBER;
5696 l_location_id_to_pass NUMBER;
5697 conf_object_version_number NUMBER;
5698 x_object_version_number NUMBER;
5699 l_auto_acc_hrs VARCHAR2(1);
5700
5701 CURSOR c_acchrs_location_csr IS
5702 SELECT * from csf_map_access_hours_vl where
5703 customer_location_id = l_acchr_loc_id;
5704
5705 CURSOR c_acchrs_ctsite_csr IS
5706 SELECT * from csf_map_access_hours_vl where
5707 customer_id = l_acchr_ct_id and
5708 customer_site_id = l_acchr_ct_site_id;
5709
5710 CURSOR c_acchrs_ct_csr IS
5711 SELECT * from csf_map_access_hours_vl where
5712 customer_id = l_acchr_ct_id
5713 and customer_site_id is NULL
5714 and customer_location_id is NULL;
5715 l_acchrs_setups_rec c_acchrs_location_csr%ROWTYPE;
5716
5717 CURSOR c_task_details IS
5718 SELECT t.task_number,
5719 t.location_id,
5720 t.address_id,
5721 t.customer_id,
5722 NVL(t.location_id, ps.location_id) loc_id
5723 from jtf_tasks_b t, hz_party_sites ps
5724 where task_id=p_task_id
5725 AND ps.party_site_id(+) = t.address_id;
5726 l_task_dtls c_task_details%rowtype;
5727
5728 --end of cursor added access hours validation
5729
5730 BEGIN
5731 x_return_status := fnd_api.g_ret_sts_success;
5732 l_auto_acc_hrs := fnd_profile.value('CSF_AUTO_POPULATE_ACCESS_HRS');
5733 /*
5734 1) Check if access hours setups are done for the location
5735 2) Else, check if access hours setups are done for the ct + ct site combination
5736 3) Else, check if access hours setups are done for the ct
5737 4) Create access hours for the task, if acc hrs setups are found for the just created task
5738 */
5739 IF l_auto_acc_hrs ='Y'
5740 THEN
5741 OPEN c_task_details;
5742 FETCH c_task_details into l_task_dtls;
5743 CLOSE c_task_details;
5744 l_acchr_ct_id := l_task_dtls.customer_id;
5745
5746 IF (l_task_dtls.location_id IS NOT NULL) THEN
5747 l_acchr_loc_id := l_task_dtls.location_id;
5748 OPEN c_acchrs_location_csr;
5749 FETCH c_acchrs_location_csr INTO l_acchrs_setups_rec;
5750 IF (c_acchrs_location_csr%NOTFOUND) THEN
5751 OPEN c_acchrs_ct_csr;
5752 FETCH c_acchrs_ct_csr INTO l_acchrs_setups_rec;
5753 IF (c_acchrs_ct_csr%NOTFOUND) THEN
5754 l_acchrs_found := false;
5755 ELSE
5756 l_acchrs_found := true;
5757 END IF;
5758 close c_acchrs_ct_csr;
5759 ELSE
5760 l_acchrs_found := true;
5761 END IF;
5762 close c_acchrs_location_csr;
5763 ELSIF(l_task_dtls.ADDRESS_ID IS NOT NULL) THEN
5764 l_acchr_ct_site_id := l_task_dtls.address_id;
5765 OPEN c_acchrs_ctsite_csr;
5766 FETCH c_acchrs_ctsite_csr INTO l_acchrs_setups_rec;
5767 IF (c_acchrs_ctsite_csr%NOTFOUND) THEN
5768 OPEN c_acchrs_ct_csr;
5769 FETCH c_acchrs_ct_csr INTO l_acchrs_setups_rec;
5770 IF (c_acchrs_ct_csr%NOTFOUND) THEN
5771 l_acchrs_found := false;
5772 ELSE
5773 l_acchrs_found := true;
5774 END IF;
5775 close c_acchrs_ct_csr;
5776 ELSE
5777 l_acchrs_found := true;
5778 END IF;
5779 close c_acchrs_ctsite_csr;
5780 END IF;
5781
5782 IF (l_acchrs_found = true)
5783 THEN
5784
5785 CSF_ACCESS_HOURS_PUB.CREATE_ACCESS_HOURS(
5786 x_ACCESS_HOUR_ID => l_acc_hr_id,
5787 p_API_VERSION => 1.0 ,
5788 p_init_msg_list => NULL,
5789 p_TASK_ID => p_task_id,
5790 p_ACCESS_HOUR_REQD => l_acchrs_setups_rec.accesshour_required,
5791 p_AFTER_HOURS_FLAG => l_acchrs_setups_rec.after_hours_flag,
5792 p_MONDAY_FIRST_START => l_acchrs_setups_rec.MONDAY_FIRST_START,
5793 p_MONDAY_FIRST_END => l_acchrs_setups_rec.MONDAY_FIRST_END,
5794 p_MONDAY_SECOND_START => l_acchrs_setups_rec.MONDAY_SECOND_START,
5795 p_MONDAY_SECOND_END => l_acchrs_setups_rec.MONDAY_SECOND_END,
5796 p_TUESDAY_FIRST_START => l_acchrs_setups_rec.TUESDAY_FIRST_START,
5797 p_TUESDAY_FIRST_END => l_acchrs_setups_rec.TUESDAY_FIRST_END,
5798 p_TUESDAY_SECOND_START => l_acchrs_setups_rec.TUESDAY_SECOND_START,
5799 p_TUESDAY_SECOND_END => l_acchrs_setups_rec.TUESDAY_SECOND_END,
5800 p_WEDNESDAY_FIRST_START => l_acchrs_setups_rec.WEDNESDAY_FIRST_START,
5801 p_WEDNESDAY_FIRST_END => l_acchrs_setups_rec.WEDNESDAY_FIRST_END,
5802 p_WEDNESDAY_SECOND_START => l_acchrs_setups_rec.WEDNESDAY_SECOND_START,
5803 p_WEDNESDAY_SECOND_END => l_acchrs_setups_rec.WEDNESDAY_SECOND_END,
5804 p_THURSDAY_FIRST_START => l_acchrs_setups_rec.THURSDAY_FIRST_START,
5805 p_THURSDAY_FIRST_END => l_acchrs_setups_rec.THURSDAY_FIRST_END,
5806 p_THURSDAY_SECOND_START => l_acchrs_setups_rec.THURSDAY_SECOND_START,
5807 p_THURSDAY_SECOND_END => l_acchrs_setups_rec.THURSDAY_SECOND_END,
5808 p_FRIDAY_FIRST_START => l_acchrs_setups_rec.FRIDAY_FIRST_START,
5809 p_FRIDAY_FIRST_END => l_acchrs_setups_rec.FRIDAY_FIRST_END,
5810 p_FRIDAY_SECOND_START => l_acchrs_setups_rec.FRIDAY_SECOND_START,
5811 p_FRIDAY_SECOND_END => l_acchrs_setups_rec.FRIDAY_SECOND_END,
5812 p_SATURDAY_FIRST_START => l_acchrs_setups_rec.SATURDAY_FIRST_START,
5813 p_SATURDAY_FIRST_END => l_acchrs_setups_rec.SATURDAY_FIRST_END,
5814 p_SATURDAY_SECOND_START => l_acchrs_setups_rec.SATURDAY_SECOND_START,
5815 p_SATURDAY_SECOND_END => l_acchrs_setups_rec.SATURDAY_SECOND_END,
5816 p_SUNDAY_FIRST_START => l_acchrs_setups_rec.SUNDAY_FIRST_START,
5817 p_SUNDAY_FIRST_END => l_acchrs_setups_rec.SUNDAY_FIRST_END,
5818 p_SUNDAY_SECOND_START => l_acchrs_setups_rec.SUNDAY_SECOND_START,
5819 p_SUNDAY_SECOND_END => l_acchrs_setups_rec.SUNDAY_SECOND_END,
5820 p_DESCRIPTION => l_acchrs_setups_rec.DESCRIPTION,
5821 px_object_version_number => x_object_version_number,
5822 p_CREATED_BY => null,
5823 p_CREATION_DATE => null,
5824 p_LAST_UPDATED_BY => null,
5825 p_LAST_UPDATE_DATE => null,
5826 p_LAST_UPDATE_LOGIN => null,
5827 x_return_status => x_return_status,
5828 x_msg_count => x_msg_count,
5829 x_msg_data => x_msg_data );
5830
5831 /*fnd_message.set_name('CSF','CSF_TASK_ACC_UPDATE_ERROR');
5832 fnd_message.set_token('VALUE',l_task_dtls.task_number);
5833 fnd_msg_pub.add;
5834 Add_Err_Msg;*/
5835 IF x_return_status = fnd_api.g_ret_sts_error THEN
5836 RAISE fnd_api.g_exc_error;
5837 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5838 RAISE fnd_api.g_exc_unexpected_error;
5839 END IF;
5840 END IF;
5841 /* VAKULKAR - end - changes to associate access hours to the tasks */
5842 END IF;-- This end if is for l_auto_acc_hrs check
5843
5844 END CREATE_ACC_HRS;
5845
5846 PROCEDURE create_achrs( x_return_status out nocopy varchar2 )
5847 IS
5848
5849 l_msg_count number;
5850 l_return_status varchar2(10);
5851 l_msg_data varchar2(2000);
5852 l_version number;
5853 l_task_id number;
5854 l_customer_id number;
5855 l_address_id number;
5856 l_location_id number;
5857 l_tpl_id number;
5858 l_tpl_grp_id number;
5859 l_api_name constant varchar2(30) := 'CREATE_ACHRS';
5860 l_task_type number;
5861 l_dc_task varchar2(20);
5862
5863 cursor c_task_type
5864 is
5865 select task_type_id
5866 from jtf_tasks_b
5867 where task_id = l_task_id;
5868
5869
5870 BEGIN
5871 SAVEPOINT create_achrs_s;
5872
5873
5874
5875 l_task_id := jtf_tasks_pub.p_task_user_hooks.task_id;
5876 l_tpl_id := jtf_tasks_pub.p_task_user_hooks.template_id;
5877 l_tpl_grp_id := jtf_tasks_pub.p_task_user_hooks.template_group_id;
5878 IF l_tpl_id IS NOT NULL
5879 THEN
5880 open c_task_type;
5881 fetch c_task_type into l_task_type;
5882 close c_task_type;
5883 l_dc_task := csf_tasks_pub.has_field_service_rule(l_task_type);
5884 IF l_dc_task = fnd_api.g_true
5885 THEN
5886 csf_tasks_pub.CREATE_ACC_HRS(
5887 p_task_id => l_task_id
5888 , x_return_status => l_return_status
5889 , x_msg_count => l_msg_count
5890 , x_msg_data => l_msg_data
5891 );
5892 IF NOT(l_return_status = fnd_api.g_ret_sts_success)
5893 THEN
5894 l_return_status := fnd_api.g_ret_sts_unexp_error;
5895 RAISE fnd_api.g_exc_unexpected_error;
5896 END IF;
5897 END IF;
5898 END IF;
5899 x_return_status := nvl( l_return_status
5900 ,fnd_api.g_ret_sts_success );
5901 EXCEPTION
5902 WHEN fnd_api.g_exc_unexpected_error THEN
5903 ROLLBACK TO create_achrs_s;
5904 x_return_status := fnd_api.g_ret_sts_unexp_error;
5905 if fnd_msg_pub.check_msg_level ( fnd_msg_pub.g_msg_lvl_unexp_error )
5906 then
5907 fnd_msg_pub.add_exc_msg ( g_pkg_name, l_api_name );
5908 end if;
5909 WHEN OTHERS THEN
5910 ROLLBACK TO create_achrs_s;
5911 x_return_status := fnd_api.g_ret_sts_unexp_error;
5912 if fnd_msg_pub.check_msg_level ( fnd_msg_pub.g_msg_lvl_unexp_error )
5913 then
5914 fnd_msg_pub.add_exc_msg ( g_pkg_name, l_api_name );
5915 end if;
5916 END create_achrs;
5917 END csf_tasks_pub;