1 PACKAGE BODY csf_task_assignments_pub AS
2 /* $Header: CSFPTASB.pls 120.20 2008/05/19 08:35:05 venjayar ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'CSF_TASK_ASSIGNMENTS_PUB';
5
6 /**
7 * The Trip Information should be corrected so that it reflects
8 * the correct availability.
9 * Case#1
10 * New Assignment is created and is linked with a Trip.
11 * Decrease the trip availability.
12 * Case#2
13 * Assignment is updated and is linked with a different Trip.
14 * Decrease the new trip availability and increase the old
15 * trip availability.
16 * Case#3
17 * Assignment is cancelled. Increase the old trip
18 * availability.
19 * Case#4
20 * Assignment is updated. Same trip is used. Increase /
21 * Decrease the availability by the difference.
22 */
23 PROCEDURE update_trip_info(
24 x_return_status OUT NOCOPY VARCHAR2
25 , x_msg_count OUT NOCOPY NUMBER
26 , x_msg_data OUT NOCOPY VARCHAR2
27 , p_task_assignment_id IN NUMBER
28 , p_task_id IN NUMBER
29 , p_resource_id IN NUMBER
30 , p_resource_type_code IN VARCHAR2
31 , p_actual_start_date IN DATE DEFAULT NULL
32 , p_actual_end_date IN DATE DEFAULT NULL
33 , p_actual_effort IN NUMBER DEFAULT NULL
34 , p_actual_effort_uom IN VARCHAR2 DEFAULT NULL
35 , p_actual_travel_duration IN NUMBER DEFAULT NULL
36 , p_actual_travel_duration_uom IN VARCHAR2 DEFAULT NULL
37 , p_sched_travel_duration IN NUMBER DEFAULT NULL
38 , p_sched_travel_duration_uom IN VARCHAR2 DEFAULT NULL
39 , p_old_trip_id IN NUMBER DEFAULT NULL
40 , p_old_trip_ovn IN NUMBER DEFAULT NULL
41 , x_trip_id OUT NOCOPY NUMBER
42 ) IS
43 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRIP_INFO';
44
45 l_trip csf_trips_pub.trip_rec_type;
46
47 l_new_start_date DATE;
48 l_new_end_date DATE;
49 l_old_start_date DATE;
50 l_old_end_date DATE;
51
52 CURSOR c_task_info IS
53 SELECT t.scheduled_start_date
54 , t.scheduled_end_date
55 , csf_util_pvt.convert_to_minutes(planned_effort, planned_effort_uom) planned_effort
56 , ta.actual_start_date
57 , ta.actual_end_date
58 , ta.resource_id
59 , ta.resource_type_code
60 , csf_util_pvt.convert_to_minutes(ta.actual_effort, ta.actual_effort_uom) actual_effort
61 , csf_util_pvt.convert_to_minutes(ta.sched_travel_duration, ta.sched_travel_duration_uom) sched_travel_duration
62 , csf_util_pvt.convert_to_minutes(ta.actual_travel_duration, ta.actual_travel_duration_uom) actual_travel_duration
63 , cac.object_capacity_id old_trip_id
64 , cac.object_version_number old_trip_ovn
65 FROM jtf_tasks_b t
66 , jtf_task_assignments ta
67 , jtf_task_statuses_b ts
68 , cac_sr_object_capacity cac
69 WHERE t.task_id = p_task_id
70 AND ta.task_id (+) = t.task_id
71 AND ts.task_status_id (+) = ta.assignment_status_id
72 AND cac.object_capacity_id (+) = ta.object_capacity_id
73 AND NVL(ts.closed_flag, 'N') = 'N'
74 AND NVL(ts.completed_flag, 'N') = 'N'
75 AND NVL(ts.cancelled_flag, 'N') = 'N'
76 AND (p_task_assignment_id IS NULL OR ta.task_assignment_id = p_task_assignment_id);
77
78 l_task_info c_task_info%ROWTYPE;
79 l_travel_time NUMBER;
80 l_old_booked_time NUMBER;
81 l_new_booked_time NUMBER;
82
83 BEGIN
84
85 -- If Actuals are passed, then Trip has to be Queried based on the passed Actuals
86 IF NVL(p_actual_start_date, fnd_api.g_miss_date) <> fnd_api.g_miss_date THEN
87 l_new_start_date := p_actual_start_date;
88 l_new_end_date := p_actual_end_date;
89
90 IF NVL(l_new_end_date, fnd_api.g_miss_date) = fnd_api.g_miss_date
91 AND NVL(p_actual_effort, fnd_api.g_miss_num) <> fnd_api.g_miss_num
92 THEN
93 l_new_end_date := l_new_start_date
94 + csf_util_pvt.convert_to_minutes(
95 p_actual_effort
96 , p_actual_effort_uom) / (60 * 24);
97 END IF;
98 END IF;
99
100 OPEN c_task_info;
101 FETCH c_task_info INTO l_task_info;
102 CLOSE c_task_info;
103
104 -- If Actuals are not passed, then Trip has to be Queried based on the Task's Data (Actuals / Scheduled)
105 IF l_new_start_date IS NULL OR l_new_end_date IS NULL THEN
106 IF l_task_info.actual_start_date IS NOT NULL THEN
107 l_new_start_date := l_task_info.actual_start_date;
108 l_new_end_date := l_task_info.actual_end_date;
109
110 IF l_new_end_date IS NULL THEN
111 l_new_end_date := l_new_start_date + NVL(l_task_info.actual_effort, l_task_info.planned_effort) / (60*24);
112 END IF;
113 ELSE
114 l_new_start_date := l_task_info.scheduled_start_date;
115 l_new_end_date := l_task_info.scheduled_end_date;
116
117 IF l_new_end_date IS NULL AND l_task_info.planned_effort IS NOT NULL THEN
118 l_new_end_date := l_new_start_date + l_task_info.planned_effort / (60*24);
119 END IF;
120 END IF;
121 END IF;
122
123 -- If the Caller wants to treat the given Old Trip Id as the Old Trip Id, then change it in our DataStructure.
124 IF NVL(p_old_trip_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
125 AND NVL(p_old_trip_ovn, fnd_api.g_miss_num) <> fnd_api.g_miss_num
126 THEN
127 l_task_info.old_trip_id := p_old_trip_id;
128 l_task_info.old_trip_ovn := p_old_trip_id;
129 END IF;
130
131 -- If the Caller wants to treat the given Old Trip Id as the Old Trip Id, then change it in our DataStructure.
132 IF NVL(p_resource_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
133 THEN
134 l_task_info.resource_id := p_resource_id;
135 l_task_info.resource_type_code := p_resource_type_code;
136 END IF;
137
138 csf_trips_pub.find_trip(
139 p_api_version => 1
140 , p_init_msg_list => fnd_api.g_false
141 , x_return_status => x_return_status
142 , x_msg_data => x_msg_data
143 , x_msg_count => x_msg_count
144 , p_resource_id => l_task_info.resource_id
145 , p_resource_type => l_task_info.resource_type_code
146 , p_start_date_time => l_new_start_date
147 , p_end_date_time => l_new_end_date
148 , p_overtime_flag => fnd_api.g_true
149 , x_trip => l_trip
150 );
151
152 -- Error would be returned only if there are no trips or multiple trips
153 -- found. We should continue in those cases.
154 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
155 RAISE fnd_api.g_exc_unexpected_error;
156 END IF;
157
158 IF l_new_start_date IS NULL THEN
159 -- Start Date is NULL. That means there is no timings. Clear the Trip Id
160 l_trip.trip_id := NULL;
161 END IF;
162
163 --
164 -- Determine whether we have to update the availability of the Old Trip
165 --
166 l_old_booked_time := 0;
167 IF l_task_info.old_trip_id IS NOT NULL THEN
168 IF l_task_info.actual_start_date IS NOT NULL THEN
169 l_old_start_date := l_task_info.actual_start_date;
170 l_old_end_date := l_task_info.actual_end_date;
171
172 IF l_old_end_date IS NULL THEN
173 l_old_end_date := l_old_start_date + NVL(l_task_info.actual_effort, l_task_info.planned_effort) / (60*24);
174 END IF;
175 END IF;
176
177 IF l_old_start_date IS NULL OR l_old_end_date IS NULL THEN
178 l_old_start_date := l_task_info.scheduled_start_date;
179 l_old_end_date := l_task_info.scheduled_end_date;
180
181 IF l_old_end_date IS NULL AND l_task_info.planned_effort IS NOT NULL THEN
182 l_old_end_date := l_old_start_date + l_task_info.planned_effort / (60*24);
183 END IF;
184 END IF;
185
186 IF l_task_info.actual_travel_duration IS NOT NULL
187 OR l_task_info.sched_travel_duration IS NOT NULL THEN
188 l_old_start_date := l_old_start_date
189 - NVL(l_task_info.actual_travel_duration, l_task_info.sched_travel_duration)
190 / (60 * 24);
191 END IF;
192
193 l_old_booked_time := (l_old_end_date - l_old_start_date) * 24;
194
195 IF l_task_info.old_trip_id <> NVL(l_trip.trip_id, -999) THEN
196 csf_trips_pub.update_trip(
197 p_api_version => 1
198 , p_init_msg_list => fnd_api.g_false
199 , x_return_status => x_return_status
200 , x_msg_data => x_msg_data
201 , x_msg_count => x_msg_count
202 , p_trip_id => l_task_info.old_trip_id
203 , p_object_version_number => l_task_info.old_trip_ovn
204 , p_upd_available_hours => l_old_booked_time
205 );
206
207 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
208 RAISE fnd_api.g_exc_unexpected_error;
209 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
210 RAISE fnd_api.g_exc_error;
211 END IF;
212
213 l_old_booked_time := 0; -- Clear it so that it doesnt affect the new trip
214 END IF;
215 END IF;
216
217 --
218 -- Determine whether we have to update the availability of the New Trip
219 --
220 IF NVL(l_trip.trip_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
221 l_travel_time := 0;
222 IF NVL(p_actual_travel_duration, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
223 l_travel_time := csf_util_pvt.convert_to_minutes(p_actual_travel_duration, p_actual_travel_duration_uom);
224 ELSIF NVL(p_sched_travel_duration, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
225 l_travel_time := csf_util_pvt.convert_to_minutes(p_sched_travel_duration, p_sched_travel_duration);
226 ELSIF NVL(l_task_info.actual_travel_duration, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
227 l_travel_time := l_task_info.actual_travel_duration;
228 ELSIF NVL(l_task_info.sched_travel_duration, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
229 l_travel_time := l_task_info.sched_travel_duration;
230 END IF;
231
232 l_new_booked_time := (l_new_end_date - l_new_start_date) * 24 -- Scheduled Dates
233 + l_travel_time / 60 -- Travel Time (in mins)
234 - l_old_booked_time; -- Old Booked Time
235
236 IF ROUND(l_new_booked_time, 5) <> 0 THEN
237 csf_trips_pub.update_trip(
238 p_api_version => 1
239 , p_init_msg_list => fnd_api.g_false
240 , x_return_status => x_return_status
241 , x_msg_data => x_msg_data
242 , x_msg_count => x_msg_count
243 , p_trip_id => l_trip.trip_id
244 , p_object_version_number => l_trip.object_version_number
245 , p_upd_available_hours => - l_new_booked_time
246 );
247 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
248 RAISE fnd_api.g_exc_unexpected_error;
249 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
250 RAISE fnd_api.g_exc_error;
251 END IF;
252 END IF;
253
254 END IF;
255
256 x_trip_id := l_trip.trip_id;
257 EXCEPTION
258 WHEN fnd_api.g_exc_error THEN
259 x_trip_id := NULL;
260 x_return_status := fnd_api.g_ret_sts_error;
261 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
262 WHEN fnd_api.g_exc_unexpected_error THEN
263 x_trip_id := NULL;
264 x_return_status := fnd_api.g_ret_sts_unexp_error;
265 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
266 WHEN OTHERS THEN
267 x_return_status := fnd_api.g_ret_sts_unexp_error;
268 x_trip_id := NULL;
269 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
270 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
271 END IF;
272 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
273 END update_trip_info;
274
275 /**
276 * Propagate the Assignment Status Change to its dependent Objects like
277 * Task, Parent Task and Child Tasks, Spares, etc.
278 */
279 PROCEDURE propagate_status_change(
280 x_return_status OUT NOCOPY VARCHAR2
281 , x_msg_count OUT NOCOPY NUMBER
282 , x_msg_data OUT NOCOPY VARCHAR2
283 , p_task_assignment_id IN NUMBER
284 , p_object_version_number IN OUT NOCOPY NUMBER
285 , p_new_assignment_status_id IN NUMBER
286 , p_update_task IN VARCHAR2
287 , p_new_sts_cancelled_flag IN VARCHAR2
288 , x_task_object_version_number OUT NOCOPY NUMBER
289 , x_task_status_id OUT NOCOPY NUMBER
290 ) IS
291 l_api_name CONSTANT VARCHAR2(30) := 'PROPAGATE_STATUS_CHANGE';
292
293 -- Cursor to fetch Information about the Assignment, Task and Trip
294 CURSOR c_task_info IS
295 SELECT t.task_id
296 , t.object_version_number
297 , t.task_status_id
298 , t.scheduled_start_date
299 , t.scheduled_end_date
300 , t.task_split_flag
301 , t.parent_task_id
302 , (SELECT pt.object_version_number FROM jtf_tasks_b pt WHERE pt.task_id = t.parent_task_id) parent_task_ovn
303 , t.source_object_type_code
304 , ta.resource_id
305 , ta.resource_type_code
306 , cac.object_capacity_id trip_id
307 , cac.object_version_number trip_ovn
308 , NVL(
309 ( SELECT 'Y'
310 FROM jtf_task_assignments ta2, jtf_task_statuses_b ts2
311 WHERE ta2.task_id = t.task_id
312 AND ta2.task_assignment_id <> ta.task_assignment_id
313 AND ts2.task_status_id = ta2.assignment_status_id
317 AND ta2.assignment_status_id <> ta.assignment_status_id
314 AND NVL(ts2.cancelled_flag, 'N') <> 'Y'
315 AND NVL(ts2.rejected_flag, 'N') <> 'Y'
316 AND ta2.assignee_role = 'ASSIGNEE'
318 AND ROWNUM = 1
319 )
320 , 'N'
321 ) other_ta_exists
322 FROM jtf_tasks_b t
323 , jtf_task_assignments ta
324 , cac_sr_object_capacity cac
325 WHERE ta.task_assignment_id = p_task_assignment_id
326 AND t.task_id = ta.task_id
327 AND cac.object_capacity_id (+) = ta.object_capacity_id;
328
329 l_task_info c_task_info%ROWTYPE;
330 l_scheduled_start DATE;
331 l_scheduled_end DATE;
332 BEGIN
333 x_return_status := fnd_api.g_ret_sts_success;
334
335 OPEN c_task_info;
336 FETCH c_task_info INTO l_task_info;
337 CLOSE c_task_info;
338
339 -- If there is only one active task assignment (ignoring Closed, Completed, Cancelled
340 -- or Rejected Assignments), then the new Status should be propagated to Task also
341 -- for both of them to be in Sync.
342 x_task_object_version_number := l_task_info.object_version_number;
343 x_task_status_id := l_task_info.task_status_id;
344 IF p_update_task IS NULL OR p_update_task = fnd_api.g_true THEN
345 IF l_task_info.other_ta_exists = 'N' AND l_task_info.task_status_id <> p_new_assignment_status_id THEN
346 x_task_status_id := p_new_assignment_status_id;
347
348 -- The Task is going to be cancelled... Clear the Scheduled Dates
349 IF p_new_sts_cancelled_flag = 'Y' AND l_task_info.source_object_type_code = 'SR' THEN
350 l_scheduled_start := NULL;
351 l_scheduled_end := NULL;
352 ELSE
353 l_scheduled_start := csf_util_pvt.get_miss_date;
354 l_scheduled_end := csf_util_pvt.get_miss_date;
355 END IF;
356
357 -- No other open Task Assignments. Update the Task also.
358 jtf_tasks_pub.update_task(
359 p_api_version => 1.0
360 , x_return_status => x_return_status
361 , x_msg_count => x_msg_count
362 , x_msg_data => x_msg_data
363 , p_task_id => l_task_info.task_id
364 , p_task_status_id => x_task_status_id
365 , p_object_version_number => x_task_object_version_number
366 , p_scheduled_start_date => l_scheduled_start
367 , p_scheduled_end_date => l_scheduled_end
368 , p_enable_workflow => fnd_api.g_miss_char
369 , p_abort_workflow => fnd_api.g_miss_char
370 );
371
372 IF x_return_status = fnd_api.g_ret_sts_error THEN
373 RAISE fnd_api.g_exc_error;
374 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
375 RAISE fnd_api.g_exc_unexpected_error;
376 END IF;
377 END IF;
378
379 -- update the Parent Task so that it is having the correct Scheduled Dates.
380 IF l_task_info.task_split_flag = 'D' THEN
381 -- Sync up the Parent and all the other Siblings
382 csf_tasks_pub.update_task_longer_than_shift(
383 p_api_version => 1.0
384 , p_init_msg_list => fnd_api.g_false
385 , p_commit => fnd_api.g_false
386 , x_return_status => x_return_status
387 , x_msg_count => x_msg_count
388 , x_msg_data => x_msg_data
389 , p_task_id => l_task_info.parent_task_id
390 , p_object_version_number => l_task_info.parent_task_ovn
391 , p_action => csf_tasks_pub.g_action_normal_to_parent
392 );
393 IF x_return_status = fnd_api.g_ret_sts_error THEN
394 RAISE fnd_api.g_exc_error;
395 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
396 RAISE fnd_api.g_exc_unexpected_error;
397 END IF;
398 END IF;
399 END IF;
400
401 -- If the new Assignment Status has Cancelled Flag, Delete the Spares
402 -- Reservations created against the Task Assignment. Increase the Trip
403 -- Availability.
404 IF p_new_sts_cancelled_flag = 'Y' THEN
405 csp_sch_int_pvt.clean_material_transaction(
406 p_api_version_number => 1.0
407 , p_task_assignment_id => p_task_assignment_id
408 , x_return_status => x_return_status
409 , x_msg_count => x_msg_count
410 , x_msg_data => x_msg_data
411 );
412 IF x_return_status = fnd_api.g_ret_sts_error THEN
413 RAISE fnd_api.g_exc_error;
414 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
415 RAISE fnd_api.g_exc_unexpected_error;
416 END IF;
417
418 -- Assignment was previously linked to a trip. Increase its Availability
419 IF l_task_info.trip_id IS NOT NULL THEN
420 update_trip_info(
421 x_return_status => x_return_status
422 , x_msg_count => x_msg_count
423 , x_msg_data => x_msg_data
424 , p_task_assignment_id => p_task_assignment_id
425 , p_task_id => l_task_info.task_id
426 , p_resource_id => l_task_info.resource_id
430 , x_trip_id => l_task_info.trip_id
427 , p_resource_type_code => l_task_info.resource_type_code
428 , p_old_trip_id => l_task_info.trip_id
429 , p_old_trip_ovn => l_task_info.trip_ovn
431 );
432
433 -- Error out only when we have unexpected error.
434 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
435 RAISE fnd_api.g_exc_unexpected_error;
436 END IF;
437 END IF;
438 END IF;
439 END propagate_status_change;
440
441 /**
442 * Creates a New Task Assignment for the given Task with the given attributes.
443 *
444 * If there exists any Cancelled Task Assignment for the Task with the given
445 * Resource Information, then that Task Assignment is reused rather than creating a
446 * new Task Assignment afresh.
447 * <br>
448 * If the Trip ID corresponding to the Task Assignment is passed as FND_API.G_MISS_NUM
449 * then the user doesnt want to link the Assignment to any Trip. So the Trip ID will
450 * be saved as NULL corresponding to the Task Assignment.
451 * If Trip ID is passed as NULL or not passed at all, then the API will try to find a
452 * Trip corresponding to the Assignment. Since we are dependent on Trips Model, any
453 * Assignment created for a Field Service Task should be linked to a Trip (based on
454 * Actual Date / Scheduled Dates). If there exists no Trip or there exists multiple trips,
455 * then the API will error out. If Assignment shouldnt be linked to any Trip, then
456 * Trip ID should be passed as FND_API.G_MISS_NUM.
457 * <br>
458 * Except for Task ID, Resouce ID, Resource Type Code all other parameters are optional.
459 */
460 PROCEDURE create_task_assignment(
461 p_api_version IN NUMBER
462 , p_init_msg_list IN VARCHAR2
463 , p_commit IN VARCHAR2
464 , p_validation_level IN NUMBER
465 , x_return_status OUT NOCOPY VARCHAR2
466 , x_msg_count OUT NOCOPY NUMBER
467 , x_msg_data OUT NOCOPY VARCHAR2
468 , p_task_assignment_id IN NUMBER
469 , p_task_id IN NUMBER
470 , p_task_name IN VARCHAR2
471 , p_task_number IN VARCHAR2
472 , p_resource_type_code IN VARCHAR2
473 , p_resource_id IN NUMBER
474 , p_resource_name IN VARCHAR2
475 , p_actual_effort IN NUMBER
476 , p_actual_effort_uom IN VARCHAR2
477 , p_schedule_flag IN VARCHAR2
478 , p_alarm_type_code IN VARCHAR2
479 , p_alarm_contact IN VARCHAR2
480 , p_sched_travel_distance IN NUMBER
481 , p_sched_travel_duration IN NUMBER
482 , p_sched_travel_duration_uom IN VARCHAR2
483 , p_actual_travel_distance IN NUMBER
484 , p_actual_travel_duration IN NUMBER
485 , p_actual_travel_duration_uom IN VARCHAR2
486 , p_actual_start_date IN DATE
487 , p_actual_end_date IN DATE
488 , p_palm_flag IN VARCHAR2
489 , p_wince_flag IN VARCHAR2
490 , p_laptop_flag IN VARCHAR2
491 , p_device1_flag IN VARCHAR2
492 , p_device2_flag IN VARCHAR2
493 , p_device3_flag IN VARCHAR2
494 , p_resource_territory_id IN NUMBER
495 , p_assignment_status_id IN NUMBER
496 , p_shift_construct_id IN NUMBER
497 , p_object_capacity_id IN NUMBER
498 , p_update_task IN VARCHAR2
499 , x_task_assignment_id OUT NOCOPY NUMBER
500 , x_ta_object_version_number OUT NOCOPY NUMBER
501 , x_task_object_version_number OUT NOCOPY NUMBER
502 , x_task_status_id OUT NOCOPY NUMBER
503 ) IS
504 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TASK_ASSIGNMENT';
505 l_api_version CONSTANT NUMBER := 1.0;
506
507 CURSOR c_cancelled_assignments IS
508 SELECT ta.task_assignment_id
509 , ta.object_version_number
510 FROM jtf_task_assignments ta, jtf_task_statuses_b ts
511 WHERE ta.task_id = p_task_id
512 AND ta.resource_id = p_resource_id
513 AND ta.resource_type_code = p_resource_type_code
514 AND ta.assignment_status_id = ts.task_status_id
515 AND ta.actual_start_date IS NULL
516 AND ta.actual_end_date IS NULL
517 AND ts.cancelled_flag = 'Y';
518
519 CURSOR c_assignment_info IS
520 SELECT object_version_number
521 FROM jtf_task_assignments
522 WHERE task_assignment_id = x_task_assignment_id;
523
524 l_cancelled_assignments c_cancelled_assignments%ROWTYPE;
525 l_trans_valid VARCHAR2(1);
526 l_valid_statuses VARCHAR2(2000);
527 l_trip_id NUMBER;
528 l_start_date DATE;
529 l_end_date DATE;
530 BEGIN
531 SAVEPOINT csf_create_task_assignment_pub;
532
533 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
537 IF fnd_api.to_boolean(p_init_msg_list) THEN
534 RAISE fnd_api.g_exc_unexpected_error;
535 END IF;
536
538 fnd_msg_pub.initialize;
539 END IF;
540
541 x_return_status := fnd_api.g_ret_sts_success;
542 l_trip_id := p_object_capacity_id;
543
544 IF (p_validation_level IS NULL OR p_validation_level = fnd_api.g_valid_level_full) THEN
545 -- Validate Field Service status flow
546 csf_tasks_pub.validate_status_change(NULL, p_assignment_status_id);
547
548 -- Validate Trip ID passed. Trip ID has to a valid Trip given the Dates
549 -- and Resource Critieria.
550 -- If FND_API.G_MISS_NUM, then the caller wants to make Trip ID as NULL in the DB.
551 IF l_trip_id = fnd_api.g_miss_num THEN
552 l_trip_id := NULL;
553 ELSE
554 update_trip_info(
555 x_return_status => x_return_status
556 , x_msg_count => x_msg_count
557 , x_msg_data => x_msg_data
558 , p_task_assignment_id => p_task_assignment_id
559 , p_task_id => p_task_id
560 , p_resource_type_code => p_resource_type_code
561 , p_resource_id => p_resource_id
562 , p_actual_start_date => p_actual_start_date
563 , p_actual_end_date => p_actual_end_date
564 , p_actual_effort => p_actual_effort
565 , p_actual_effort_uom => p_actual_effort_uom
566 , p_actual_travel_duration => p_actual_travel_duration
567 , p_actual_travel_duration_uom => p_actual_travel_duration_uom
568 , p_sched_travel_duration => p_sched_travel_duration
569 , p_sched_travel_duration_uom => p_sched_travel_duration_uom
570 , x_trip_id => l_trip_id
571 );
572 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
573 RAISE fnd_api.g_exc_unexpected_error;
574 END IF;
575 END IF;
576 END IF;
577
578 -- Reuse a Cancelled Task Assignment of the Task rather than creating anew.
579 OPEN c_cancelled_assignments;
580 FETCH c_cancelled_assignments INTO l_cancelled_assignments;
581 CLOSE c_cancelled_assignments;
582
583 IF l_cancelled_assignments.task_assignment_id IS NOT NULL THEN
584 x_ta_object_version_number := l_cancelled_assignments.object_version_number;
585 x_task_assignment_id := l_cancelled_assignments.task_assignment_id;
586 update_task_assignment(
587 p_api_version => p_api_version
588 , p_init_msg_list => p_init_msg_list
589 , p_commit => fnd_api.g_false
590 , p_validation_level => fnd_api.g_valid_level_none
591 , x_return_status => x_return_status
592 , x_msg_count => x_msg_count
593 , x_msg_data => x_msg_data
594 , p_task_assignment_id => x_task_assignment_id
595 , p_object_version_number => x_ta_object_version_number
596 , p_task_id => p_task_id
597 , p_resource_type_code => p_resource_type_code
598 , p_resource_id => p_resource_id
599 , p_resource_territory_id => p_resource_territory_id
600 , p_assignment_status_id => p_assignment_status_id
601 , p_actual_start_date => p_actual_start_date
602 , p_actual_end_date => p_actual_end_date
603 , p_sched_travel_distance => p_sched_travel_distance
604 , p_sched_travel_duration => p_sched_travel_duration
605 , p_sched_travel_duration_uom => p_sched_travel_duration_uom
606 , p_shift_construct_id => p_shift_construct_id
607 , p_object_capacity_id => l_trip_id
608 , p_update_task => p_update_task
609 , x_task_object_version_number => x_task_object_version_number
610 , x_task_status_id => x_task_status_id
611 );
612 ELSE
613 jtf_task_assignments_pub.create_task_assignment(
614 p_api_version => 1.0
615 , x_return_status => x_return_status
616 , x_msg_count => x_msg_count
617 , x_msg_data => x_msg_data
618 , p_task_assignment_id => p_task_assignment_id
619 , p_task_id => p_task_id
620 , p_task_name => p_task_name
621 , p_task_number => p_task_number
622 , p_resource_type_code => p_resource_type_code
623 , p_resource_id => p_resource_id
624 , p_assignment_status_id => p_assignment_status_id
625 , p_object_capacity_id => l_trip_id
626 , p_actual_effort => p_actual_effort
627 , p_actual_effort_uom => p_actual_effort_uom
628 , p_schedule_flag => p_schedule_flag
629 , p_alarm_type_code => p_alarm_type_code
630 , p_alarm_contact => p_alarm_contact
631 , p_sched_travel_distance => p_sched_travel_distance
632 , p_sched_travel_duration => p_sched_travel_duration
633 , p_sched_travel_duration_uom => p_sched_travel_duration_uom
634 , p_actual_travel_distance => p_actual_travel_distance
635 , p_actual_travel_duration => p_actual_travel_duration
639 , p_palm_flag => p_palm_flag
636 , p_actual_travel_duration_uom => p_actual_travel_duration_uom
637 , p_actual_start_date => p_actual_start_date
638 , p_actual_end_date => p_actual_end_date
640 , p_wince_flag => p_wince_flag
641 , p_laptop_flag => p_laptop_flag
642 , p_device1_flag => p_device1_flag
643 , p_device2_flag => p_device2_flag
644 , p_device3_flag => p_device3_flag
645 , p_resource_territory_id => p_resource_territory_id
646 , p_shift_construct_id => p_shift_construct_id
647 , p_enable_workflow => fnd_api.g_miss_char
648 , p_abort_workflow => fnd_api.g_miss_char
649 , x_task_assignment_id => x_task_assignment_id
650 );
651
652 IF x_return_status = fnd_api.g_ret_sts_error THEN
653 RAISE fnd_api.g_exc_error;
654 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
655 RAISE fnd_api.g_exc_unexpected_error;
656 END IF;
657
658 OPEN c_assignment_info;
659 FETCH c_assignment_info INTO x_ta_object_version_number;
660 CLOSE c_assignment_info;
661
662 -- Update the Assignment Status and thereby Synchronizing with Task
663 propagate_status_change(
664 x_return_status => x_return_status
665 , x_msg_count => x_msg_count
666 , x_msg_data => x_msg_data
667 , p_task_assignment_id => x_task_assignment_id
668 , p_object_version_number => x_ta_object_version_number
669 , p_new_assignment_status_id => p_assignment_status_id
670 , p_update_task => p_update_task
671 , p_new_sts_cancelled_flag => 'N'
672 , x_task_object_version_number => x_task_object_version_number
673 , x_task_status_id => x_task_status_id
674 );
675 END IF;
676
677 -- Standard check of p_commit
678 IF fnd_api.to_boolean(p_commit) THEN
679 COMMIT WORK;
680 END IF;
681 EXCEPTION
682 WHEN fnd_api.g_exc_error THEN
683 ROLLBACK TO csf_create_task_assignment_pub;
684 x_return_status := fnd_api.g_ret_sts_error;
685 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
686 WHEN fnd_api.g_exc_unexpected_error THEN
687 ROLLBACK TO csf_create_task_assignment_pub;
688 x_return_status := fnd_api.g_ret_sts_unexp_error;
689 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
690 WHEN OTHERS THEN
691 x_return_status := fnd_api.g_ret_sts_unexp_error;
692 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
693 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
694 END IF;
695 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
696 ROLLBACK TO csf_create_task_assignment_pub;
697 END create_task_assignment;
698
699 /**
700 * Update an existing Task Assignment with new Task Attributes
701 *
702 * Given the Task Assignment ID and Task Object Version Number, it calls
703 * JTF Task Assignment API to update the Task Assignment with the new Attributes.
704 * It is actually a two step process
705 * 1. Updating the Task Assignment with the new Task Attributes except Status
706 * 2. Updating the Task Assignment with the new Task Status (if not FND_API.G_MISS_NUM)
707 * by calling UPDATE_ASSIGNMENT_STATUS.
708 * <br>
709 * Because of the two step process, the returned Task Assignment Object
710 * Version Number might be incremented by 2 when user might have expected an
711 * increment of only 1.
712 * <br>
713 * Except Task Assignment ID and Object Version Number parameters, all are optional.
714 */
715 PROCEDURE update_task_assignment(
716 p_api_version IN NUMBER
717 , p_init_msg_list IN VARCHAR2
718 , p_commit IN VARCHAR2
719 , p_validation_level IN NUMBER
720 , x_return_status OUT NOCOPY VARCHAR2
721 , x_msg_count OUT NOCOPY NUMBER
722 , x_msg_data OUT NOCOPY VARCHAR2
723 , p_task_assignment_id IN NUMBER
724 , p_object_version_number IN OUT NOCOPY NUMBER
725 , p_task_id IN NUMBER
726 , p_resource_type_code IN VARCHAR2
727 , p_resource_id IN NUMBER
728 , p_resource_territory_id IN NUMBER
729 , p_assignment_status_id IN NUMBER
730 , p_actual_start_date IN DATE
731 , p_actual_end_date IN DATE
732 , p_sched_travel_distance IN NUMBER
733 , p_sched_travel_duration IN NUMBER
734 , p_sched_travel_duration_uom IN VARCHAR2
735 , p_shift_construct_id IN NUMBER
736 , p_object_capacity_id IN NUMBER
737 , p_update_task IN VARCHAR2
738 , p_task_number IN VARCHAR2
739 , p_task_name IN VARCHAR2
740 , p_resource_name IN VARCHAR2
741 , p_actual_effort IN NUMBER
745 , p_actual_travel_duration_uom IN VARCHAR2
742 , p_actual_effort_uom IN VARCHAR2
743 , p_actual_travel_distance IN NUMBER
744 , p_actual_travel_duration IN NUMBER
746 , p_attribute1 IN VARCHAR2
747 , p_attribute2 IN VARCHAR2
748 , p_attribute3 IN VARCHAR2
749 , p_attribute4 IN VARCHAR2
750 , p_attribute5 IN VARCHAR2
751 , p_attribute6 IN VARCHAR2
752 , p_attribute7 IN VARCHAR2
753 , p_attribute8 IN VARCHAR2
754 , p_attribute9 IN VARCHAR2
755 , p_attribute10 IN VARCHAR2
756 , p_attribute11 IN VARCHAR2
757 , p_attribute12 IN VARCHAR2
758 , p_attribute13 IN VARCHAR2
759 , p_attribute14 IN VARCHAR2
760 , p_attribute15 IN VARCHAR2
761 , p_attribute_category IN VARCHAR2
762 , p_show_on_calendar IN VARCHAR2
763 , p_category_id IN NUMBER
764 , p_schedule_flag IN VARCHAR2
765 , p_alarm_type_code IN VARCHAR2
766 , p_alarm_contact IN VARCHAR2
767 , p_palm_flag IN VARCHAR2
768 , p_wince_flag IN VARCHAR2
769 , p_laptop_flag IN VARCHAR2
770 , p_device1_flag IN VARCHAR2
771 , p_device2_flag IN VARCHAR2
772 , p_device3_flag IN VARCHAR2
773 , p_enable_workflow IN VARCHAR2
774 , p_abort_workflow IN VARCHAR2
775 , x_task_object_version_number OUT NOCOPY NUMBER
776 , x_task_status_id OUT NOCOPY NUMBER
777 ) IS
778 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TASK_ASSIGNMENT';
779 l_api_version CONSTANT NUMBER := 1.0;
780
781 -- cursor to fetch the Cancelled Flag corresponding to the new Task Status.
782 CURSOR c_task_status_info IS
783 SELECT NVL (ts.cancelled_flag, 'N') cancelled_flag
784 FROM jtf_task_statuses_b ts
785 WHERE ts.task_status_id = p_assignment_status_id;
786
787 -- cursor to fetch Information about the Task Assignment.
788 CURSOR c_task_assignment_info IS
789 SELECT ta.assignment_status_id, ta.task_id
790 FROM jtf_task_assignments ta
791 WHERE task_assignment_id = p_task_assignment_id;
792
793 l_task_id NUMBER;
794 l_old_assignment_status_id NUMBER;
795 l_new_sts_cancelled_flag VARCHAR2(1);
796 l_trip_id NUMBER;
797 l_distance NUMBER;
798 l_duration NUMBER;
799 l_duration_uom VARCHAR2(3);
800 BEGIN
801 SAVEPOINT csf_update_task_assignment_pub;
802
803 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
804 RAISE fnd_api.g_exc_unexpected_error;
805 END IF;
806
807 IF fnd_api.to_boolean(p_init_msg_list) THEN
808 fnd_msg_pub.initialize;
809 END IF;
810
811 x_return_status := fnd_api.g_ret_sts_success;
812
813 -- Fetch the Task Assignment Information
814 OPEN c_task_assignment_info;
815 FETCH c_task_assignment_info INTO l_old_assignment_status_id, l_task_id;
816 CLOSE c_task_assignment_info;
817
818 -- We require Task Id for computations. If the caller doesnt pass Task Id
819 -- lets retrieve it from JTF_TASK_ASSIGNMENTS. If the caller has indeeed
820 -- passed it, then use that value.
821 IF p_task_id <> fnd_api.g_miss_num AND p_task_id IS NOT NULL THEN
822 l_task_id := p_task_id;
823 END IF;
824
825 l_trip_id := p_object_capacity_id;
826
827 -- If Assignment is cancelled, then we have to clear the Scheduled Travel
828 -- Duration, Distance and Trip ID.
829 l_distance := p_sched_travel_distance;
830 l_duration := p_sched_travel_duration;
831 l_duration_uom := p_sched_travel_duration_uom;
832 l_new_sts_cancelled_flag := 'N';
833 IF p_assignment_status_id <> fnd_api.g_miss_num THEN
834 OPEN c_task_status_info;
835 FETCH c_task_status_info INTO l_new_sts_cancelled_flag;
836 CLOSE c_task_status_info;
837
838 IF l_new_sts_cancelled_flag = 'Y' THEN
839 l_distance := NULL;
840 l_duration := NULL;
841 l_duration_uom := NULL;
842 l_trip_id := NULL;
843 END IF;
844 END IF;
845
846 IF (p_validation_level IS NULL OR p_validation_level = fnd_api.g_valid_level_full) THEN
847
848 -- Validate Field Service status flow
849 IF p_assignment_status_id <> fnd_api.g_miss_num
850 AND NVL(l_old_assignment_status_id, -1) <> NVL(p_assignment_status_id, -1)
851 THEN
852 csf_tasks_pub.validate_status_change(l_old_assignment_status_id, p_assignment_status_id);
853 END IF;
854
855 -- If Trip ID is passed as FND_API.G_MISS_NUM.. and Actuals are passed, we need to link
856 -- the Task Assignment to the correct Trip.
860 , x_msg_count => x_msg_count
857 IF l_trip_id IS NOT NULL AND l_new_sts_cancelled_flag = 'N' THEN
858 update_trip_info(
859 x_return_status => x_return_status
861 , x_msg_data => x_msg_data
862 , p_task_id => l_task_id
863 , p_task_assignment_id => p_task_assignment_id
864 , p_resource_type_code => p_resource_type_code
865 , p_resource_id => p_resource_id
866 , p_actual_start_date => p_actual_start_date
867 , p_actual_end_date => p_actual_end_date
868 , p_actual_effort => p_actual_effort
869 , p_actual_effort_uom => p_actual_effort_uom
870 , p_actual_travel_duration => p_actual_travel_duration
871 , p_actual_travel_duration_uom => p_actual_travel_duration_uom
872 , p_sched_travel_duration => p_sched_travel_duration
873 , p_sched_travel_duration_uom => p_sched_travel_duration_uom
874 , x_trip_id => l_trip_id
875 );
876 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
877 RAISE fnd_api.g_exc_unexpected_error;
878 END IF;
879 END IF;
880 END IF;
881
882 jtf_task_assignments_pub.update_task_assignment(
883 p_api_version => 1.0
884 , x_return_status => x_return_status
885 , x_msg_count => x_msg_count
886 , x_msg_data => x_msg_data
887 , p_task_assignment_id => p_task_assignment_id
888 , p_object_version_number => p_object_version_number
889 , p_task_id => l_task_id
890 , p_resource_type_code => p_resource_type_code
891 , p_resource_id => p_resource_id
892 , p_resource_territory_id => p_resource_territory_id
893 , p_assignment_status_id => p_assignment_status_id
894 , p_actual_start_date => p_actual_start_date
895 , p_actual_end_date => p_actual_end_date
896 , p_sched_travel_distance => l_distance
897 , p_sched_travel_duration => l_duration
898 , p_sched_travel_duration_uom => l_duration_uom
899 , p_shift_construct_id => p_shift_construct_id
900 , p_object_capacity_id => l_trip_id
901 , p_task_number => p_task_number
902 , p_task_name => p_task_name
903 , p_resource_name => p_resource_name
904 , p_actual_effort => p_actual_effort
905 , p_actual_effort_uom => p_actual_effort_uom
906 , p_actual_travel_distance => p_actual_travel_distance
907 , p_actual_travel_duration => p_actual_travel_duration
908 , p_actual_travel_duration_uom => p_actual_travel_duration_uom
909 , p_attribute1 => p_attribute1
910 , p_attribute2 => p_attribute2
911 , p_attribute3 => p_attribute3
912 , p_attribute4 => p_attribute4
913 , p_attribute5 => p_attribute5
914 , p_attribute6 => p_attribute6
915 , p_attribute7 => p_attribute7
916 , p_attribute8 => p_attribute8
917 , p_attribute9 => p_attribute9
918 , p_attribute10 => p_attribute10
919 , p_attribute11 => p_attribute11
920 , p_attribute12 => p_attribute12
921 , p_attribute13 => p_attribute13
922 , p_attribute14 => p_attribute14
923 , p_attribute15 => p_attribute15
924 , p_attribute_category => p_attribute_category
925 , p_show_on_calendar => p_show_on_calendar
926 , p_category_id => p_category_id
927 , p_schedule_flag => p_schedule_flag
928 , p_alarm_type_code => p_alarm_type_code
929 , p_alarm_contact => p_alarm_contact
930 , p_palm_flag => p_palm_flag
931 , p_wince_flag => p_wince_flag
932 , p_laptop_flag => p_laptop_flag
933 , p_device1_flag => p_device1_flag
934 , p_device2_flag => p_device2_flag
935 , p_device3_flag => p_device3_flag
936 , p_enable_workflow => p_enable_workflow
937 , p_abort_workflow => p_abort_workflow
938 );
939
940 IF x_return_status = fnd_api.g_ret_sts_error THEN
941 RAISE fnd_api.g_exc_error;
942 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
943 RAISE fnd_api.g_exc_unexpected_error;
944 END IF;
945
946 -- If Assignment Status is updated, then propagate the status to other objects
947 IF p_assignment_status_id <> fnd_api.g_miss_num THEN
948 propagate_status_change(
949 x_return_status => x_return_status
950 , x_msg_count => x_msg_count
951 , x_msg_data => x_msg_data
952 , p_task_assignment_id => p_task_assignment_id
953 , p_object_version_number => p_object_version_number
954 , p_new_assignment_status_id => p_assignment_status_id
955 , p_update_task => p_update_task
956 , p_new_sts_cancelled_flag => l_new_sts_cancelled_flag
957 , x_task_object_version_number => x_task_object_version_number
958 , x_task_status_id => x_task_status_id
959 );
963 RAISE fnd_api.g_exc_unexpected_error;
960 IF x_return_status = fnd_api.g_ret_sts_error THEN
961 RAISE fnd_api.g_exc_error;
962 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
964 END IF;
965 END IF;
966
967 -- Standard check of p_commit
968 IF fnd_api.to_boolean(p_commit) THEN
969 COMMIT WORK;
970 END IF;
971
972 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
973 EXCEPTION
974 WHEN fnd_api.g_exc_error THEN
975 ROLLBACK TO csf_update_task_assignment_pub;
976 x_return_status := fnd_api.g_ret_sts_error;
977 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
978 WHEN fnd_api.g_exc_unexpected_error THEN
979 ROLLBACK TO csf_update_task_assignment_pub;
980 x_return_status := fnd_api.g_ret_sts_unexp_error;
981 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
982 WHEN OTHERS THEN
983 x_return_status := fnd_api.g_ret_sts_unexp_error;
984 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
985 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
986 END IF;
987 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
988 ROLLBACK TO csf_update_task_assignment_pub;
989 END update_task_assignment;
990
991 /**
992 * Update the Status of the Task Assignment with the given Status and propagate to the
993 * Task also if required.
994 * <br>
995 * Task Assignment is updated with the new Status if the Transition from the current
996 * status to the new status is allowed as determined by
997 * CSF_TASKS_PUB.VALIDATE_STATE_TRANSITION. Transition validation is done only
998 * when Validation Level is passed as FULL.
999 * <br>
1000 * In addition to updating the Task Assignment Status, the following operations are also
1001 * done
1002 * 1. If the Task corresponding to the given Task Assignment has no other
1003 * Open / Active Task Assignments other than the given one, then the Assignment
1004 * Status is propagated to the Task also. If there exists any other Active
1005 * Assignment, then the Task is not updated.
1006 * The parameters P_TASK_OBJECT_VERSION_NUMBER and X_TASK_STATUS_ID reflect
1007 * the Object Version Number and Task Status ID of the Task in Database
1008 * irrespective of the fact whether the update has taken place or not. <br>
1009 *
1010 * 2. If the Assignment goes to Cancelled (as per the new status), then if any
1011 * Spares Order is linked to the Assignment, they are cleaned up by calling
1012 * CLEAN_MATERIAL_TRANSACTION of Spares. <br>
1013 *
1014 * 3. If the Assignment goes to Assigned (as per the new status), and the
1015 * old status is not Assigned, then Orders are created and linked to the
1016 * Task Assignment. <br>
1017 *
1018 * 4. If the Assignnment goes to Working (as per the new status), then it means
1019 * that the Resource is working on the Task and so his location should be updated
1020 * to reflect the location of the Task. This is required by Map Functionality.
1021 * THIS IS WRONG AND SHOULD BE REMOVED. MAP SHOULD BE USING HZ_LOCATIONS TABLE. <br>
1022 *
1023 * @param p_api_version API Version (1.0)
1024 * @param p_init_msg_list Initialize Message List
1025 * @param p_commit Commit the Work
1026 * @param p_validation_level Validate the given Parameters
1027 * @param x_return_status Return Status of the Procedure.
1028 * @param x_msg_count Number of Messages in the Stack.
1029 * @param x_msg_data Stack of Error Messages.
1030 * @param p_task_assignment_id Task Assignment ID of the Assignment to be updated
1031 * @param p_assignment_status_id New Task Status ID for the Task Assignment.
1032 * @param p_show_on_calendar <Dont Know>
1033 * @param p_object_version_number Current Task Version and also container for new one.
1034 * @param x_task_object_version_number Task Object Version Number (either old or new)
1035 * @param x_task_status_id Task Status ID (either old or new)
1036 */
1037 PROCEDURE update_assignment_status(
1038 p_api_version IN NUMBER
1039 , p_init_msg_list IN VARCHAR2
1040 , p_commit IN VARCHAR2
1041 , p_validation_level IN NUMBER
1042 , x_return_status OUT NOCOPY VARCHAR2
1043 , x_msg_count OUT NOCOPY NUMBER
1044 , x_msg_data OUT NOCOPY VARCHAR2
1045 , p_task_assignment_id IN NUMBER
1046 , p_object_version_number IN OUT NOCOPY NUMBER
1047 , p_assignment_status_id IN NUMBER
1048 , p_update_task IN VARCHAR2
1049 , p_show_on_calendar IN VARCHAR2
1050 , x_task_object_version_number OUT NOCOPY NUMBER
1051 , x_task_status_id OUT NOCOPY NUMBER
1052 ) IS
1053 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ASSIGNMENT_STATUS';
1054 l_api_version CONSTANT NUMBER := 1.0;
1055
1056 -- cursor to fetch Information about the Task Assignment.
1057 CURSOR c_task_assignment_info IS
1058 SELECT ta.assignment_status_id
1059 FROM jtf_task_assignments ta
1063 CURSOR c_task_status_info IS
1060 WHERE task_assignment_id = p_task_assignment_id;
1061
1062 -- Fetch the Cancelled Flag corresponding to the new Task Status.
1064 SELECT NVL (ts.cancelled_flag, 'N') cancelled_flag
1065 FROM jtf_task_statuses_b ts
1066 WHERE ts.task_status_id = p_assignment_status_id;
1067
1068 l_old_assignment_status_id NUMBER;
1069 l_new_sts_cancelled_flag VARCHAR2(1);
1070 l_distance NUMBER;
1071 l_duration NUMBER;
1072 l_duration_uom VARCHAR2(3);
1073 l_trip_id NUMBER;
1074 BEGIN
1075 SAVEPOINT csf_update_assign_status_pub;
1076
1077 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1078 RAISE fnd_api.g_exc_unexpected_error;
1079 END IF;
1080
1081 IF fnd_api.to_boolean(p_init_msg_list) THEN
1082 fnd_msg_pub.initialize;
1083 END IF;
1084
1085 x_return_status := fnd_api.g_ret_sts_success;
1086
1087 -- Check whether there is anything update in Assignment Status.
1088 IF p_assignment_status_id = fnd_api.g_miss_num THEN
1089 RETURN;
1090 END IF;
1091
1092 OPEN c_task_assignment_info;
1093 FETCH c_task_assignment_info INTO l_old_assignment_status_id;
1094 CLOSE c_task_assignment_info;
1095
1096 IF l_old_assignment_status_id = p_assignment_status_id THEN
1097 RETURN;
1098 END IF;
1099
1100 IF (p_validation_level IS NULL OR p_validation_level = fnd_api.g_valid_level_full) THEN
1101 -- Validate Field Service status flow
1102 csf_tasks_pub.validate_status_change(l_old_assignment_status_id, p_assignment_status_id);
1103 END IF;
1104
1105 OPEN c_task_status_info;
1106 FETCH c_task_status_info INTO l_new_sts_cancelled_flag;
1107 CLOSE c_task_status_info;
1108
1109 IF l_new_sts_cancelled_flag = 'Y' THEN
1110 l_distance := NULL;
1111 l_duration := NULL;
1112 l_duration_uom := NULL;
1113 l_trip_id := NULL;
1114 ELSE
1115 l_distance := csf_util_pvt.get_miss_num;
1116 l_duration := csf_util_pvt.get_miss_num;
1117 l_duration_uom := csf_util_pvt.get_miss_char;
1118 l_trip_id := csf_util_pvt.get_miss_num;
1119 END IF;
1120
1121 -- Update the Task Assignment.
1122 jtf_task_assignments_pub.update_task_assignment(
1123 p_api_version => 1.0
1124 , x_return_status => x_return_status
1125 , x_msg_count => x_msg_count
1126 , x_msg_data => x_msg_data
1127 , p_object_version_number => p_object_version_number
1128 , p_task_assignment_id => p_task_assignment_id
1129 , p_assignment_status_id => p_assignment_status_id
1130 , p_sched_travel_distance => l_distance
1131 , p_sched_travel_duration => l_duration
1132 , p_sched_travel_duration_uom => l_duration_uom
1133 , p_object_capacity_id => l_trip_id
1134 , p_show_on_calendar => p_show_on_calendar
1135 , p_category_id => NULL
1136 , p_enable_workflow => fnd_api.g_false
1137 , p_abort_workflow => fnd_api.g_false
1138 );
1139
1140 IF x_return_status = fnd_api.g_ret_sts_error THEN
1141 RAISE fnd_api.g_exc_error;
1142 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1143 RAISE fnd_api.g_exc_unexpected_error;
1144 END IF;
1145
1146 -- Propagate the changes to Task, Parent Task, Child Tasks, Spares, etc.
1147 propagate_status_change(
1148 x_return_status => x_return_status
1149 , x_msg_count => x_msg_count
1150 , x_msg_data => x_msg_data
1151 , p_task_assignment_id => p_task_assignment_id
1152 , p_object_version_number => p_object_version_number
1153 , p_new_assignment_status_id => p_assignment_status_id
1154 , p_update_task => p_update_task
1155 , p_new_sts_cancelled_flag => l_new_sts_cancelled_flag
1156 , x_task_object_version_number => x_task_object_version_number
1157 , x_task_status_id => x_task_status_id
1158 );
1159
1160 IF x_return_status = fnd_api.g_ret_sts_error THEN
1161 RAISE fnd_api.g_exc_error;
1162 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1163 RAISE fnd_api.g_exc_unexpected_error;
1164 END IF;
1165
1166 IF fnd_api.to_boolean(p_commit) THEN
1167 COMMIT WORK;
1168 END IF;
1169
1170 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1171 EXCEPTION
1172 WHEN fnd_api.g_exc_error THEN
1173 ROLLBACK TO csf_update_assign_status_pub;
1174 x_return_status := fnd_api.g_ret_sts_error;
1175 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1176 WHEN fnd_api.g_exc_unexpected_error THEN
1177 ROLLBACK TO csf_update_assign_status_pub;
1178 x_return_status := fnd_api.g_ret_sts_unexp_error;
1179 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1180 WHEN OTHERS THEN
1181 x_return_status := fnd_api.g_ret_sts_unexp_error;
1182 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1183 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1184 END IF;
1185 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1189
1186 ROLLBACK TO csf_update_assign_status_pub;
1187 END update_assignment_status;
1188
1190 END csf_task_assignments_pub;
[Home] [Help]