1 PACKAGE BODY csf_task_assignments_pub AS
2 /* $Header: CSFPTASB.pls 120.38.12020000.3 2013/04/25 11:29:39 aditysin ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'CSF_TASK_ASSIGNMENTS_PUB';
5 g_debug_level NUMBER := NVL(fnd_profile.value_specific('AFLOG_LEVEL'), fnd_log.level_event);
6
7 g_status VARCHAR2(30);
8 /**
9 * The Trip Information should be corrected so that it reflects
10 * the correct availability.
11 * Case#1
12 * New Assignment is created and is linked with a Trip.
13 * Decrease the trip availability.
14 * Case#2
15 * Assignment is updated and is linked with a different Trip.
16 * Decrease the new trip availability and increase the old
17 * trip availability.
18 * Case#3
19 * Assignment is cancelled. Increase the old trip
20 * availability.
21 * Case#4
22 * Assignment is updated. Same trip is used. Increase /
23 * Decrease the availability by the difference.
24 */
25
26 FUNCTION cross_task_val(p_task_assignment_id NUMBER,p_assignment_status NUMBER,p_task out NOCOPY NUMBER)
27 RETURN VARCHAR2;
28
29 PROCEDURE debug(p_message VARCHAR2, p_module VARCHAR2, p_level NUMBER) IS
30 BEGIN
31 IF p_level >= g_debug_level
32 THEN
33 IF ( p_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
34 THEN
35 fnd_log.string(p_level, 'csf.plsql.CSF_TASK_ASSIGNMENTS_PUB.' || p_module, p_message);
36 END IF;
37 END IF;
38 END;
39
40
41 PROCEDURE update_trip_info(
42 x_return_status OUT NOCOPY VARCHAR2
43 , x_msg_count OUT NOCOPY NUMBER
44 , x_msg_data OUT NOCOPY VARCHAR2
45 , p_task_assignment_id IN NUMBER
46 , p_task_id IN NUMBER
47 , p_resource_id IN NUMBER
48 , p_resource_type_code IN VARCHAR2
49 , p_actual_start_date IN DATE DEFAULT NULL
50 , p_actual_end_date IN DATE DEFAULT NULL
51 , p_actual_effort IN NUMBER DEFAULT NULL
52 , p_actual_effort_uom IN VARCHAR2 DEFAULT NULL
53 , p_actual_travel_duration IN NUMBER DEFAULT NULL
54 , p_actual_travel_duration_uom IN VARCHAR2 DEFAULT NULL
55 , p_sched_travel_duration IN NUMBER DEFAULT NULL
56 , p_sched_travel_duration_uom IN VARCHAR2 DEFAULT NULL
57 , p_old_trip_id IN NUMBER DEFAULT NULL
58 , p_old_trip_ovn IN NUMBER DEFAULT NULL
59 , x_trip_id OUT NOCOPY NUMBER
60 ) IS
61 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRIP_INFO';
62
63 l_trip csf_trips_pub.trip_rec_type;
64
65 l_new_start_date DATE;
66 l_new_end_date DATE;
67 l_old_start_date DATE;
68 l_old_end_date DATE;
69
70 CURSOR c_task_info IS
71 SELECT t.scheduled_start_date
72 , t.scheduled_end_date
73 , csf_util_pvt.convert_to_minutes(planned_effort, planned_effort_uom) planned_effort
74 , ta.actual_start_date
75 , ta.actual_end_date
76 , ta.resource_id
77 , ta.resource_type_code
78 , csf_util_pvt.convert_to_minutes(ta.actual_effort, ta.actual_effort_uom) actual_effort
79 , csf_util_pvt.convert_to_minutes(ta.sched_travel_duration, ta.sched_travel_duration_uom) sched_travel_duration
80 , csf_util_pvt.convert_to_minutes(ta.actual_travel_duration, ta.actual_travel_duration_uom) actual_travel_duration
81 , cac.object_capacity_id old_trip_id
82 , cac.object_version_number old_trip_ovn
83 FROM jtf_tasks_b t
84 , jtf_task_assignments ta
85 , jtf_task_statuses_b ts
86 , cac_sr_object_capacity cac
87 WHERE t.task_id = p_task_id
88 AND ta.task_id (+) = t.task_id
89 AND ts.task_status_id (+) = ta.assignment_status_id
90 AND cac.object_capacity_id (+) = ta.object_capacity_id
91 AND NVL(ts.closed_flag, 'N') = 'N'
92 AND NVL(ts.completed_flag, 'N') = 'N'
93 AND NVL(ts.cancelled_flag, 'N') = 'N'
94 AND (p_task_assignment_id IS NULL OR ta.task_assignment_id = p_task_assignment_id);
95
96 l_task_info c_task_info%ROWTYPE;
97 l_travel_time NUMBER;
98 l_old_booked_time NUMBER;
99 l_new_booked_time NUMBER;
100
101 BEGIN
102
103 -- If Actuals are passed, then Trip has to be Queried based on the passed Actuals
104 IF NVL(p_actual_start_date, fnd_api.g_miss_date) <> fnd_api.g_miss_date THEN
105 l_new_start_date := p_actual_start_date;
106 l_new_end_date := p_actual_end_date;
107
108 IF NVL(l_new_end_date, fnd_api.g_miss_date) = fnd_api.g_miss_date
109 AND NVL(p_actual_effort, fnd_api.g_miss_num) <> fnd_api.g_miss_num
110 THEN
111 l_new_end_date := l_new_start_date
112 + csf_util_pvt.convert_to_minutes(
113 p_actual_effort
114 , p_actual_effort_uom) / (60 * 24);
115 END IF;
116 END IF;
117
118 OPEN c_task_info;
119 FETCH c_task_info INTO l_task_info;
120 CLOSE c_task_info;
121
122 -- If Actuals are not passed, then Trip has to be Queried based on the Task's Data (Actuals / Scheduled)
123 IF l_new_start_date IS NULL OR l_new_end_date IS NULL THEN
124 IF l_task_info.actual_start_date IS NOT NULL THEN
125 l_new_start_date := l_task_info.actual_start_date;
126 l_new_end_date := l_task_info.actual_end_date;
127
128 IF l_new_end_date IS NULL THEN
129 l_new_end_date := l_new_start_date + NVL(l_task_info.actual_effort, l_task_info.planned_effort) / (60*24);
130 END IF;
131 ELSE
132 l_new_start_date := l_task_info.scheduled_start_date;
133 l_new_end_date := l_task_info.scheduled_end_date;
134
135 IF l_new_end_date IS NULL AND l_task_info.planned_effort IS NOT NULL THEN
136 l_new_end_date := l_new_start_date + l_task_info.planned_effort / (60*24);
137 END IF;
138 END IF;
139 END IF;
140
141 -- If the Caller wants to treat the given Old Trip Id as the Old Trip Id, then change it in our DataStructure.
142 IF NVL(p_old_trip_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
143 AND NVL(p_old_trip_ovn, fnd_api.g_miss_num) <> fnd_api.g_miss_num
144 THEN
145 l_task_info.old_trip_id := p_old_trip_id;
146 l_task_info.old_trip_ovn := p_old_trip_id;
147 END IF;
148
149 -- If the Caller wants to treat the given Old Trip Id as the Old Trip Id, then change it in our DataStructure.
150 IF NVL(p_resource_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
151 THEN
152 l_task_info.resource_id := p_resource_id;
153 l_task_info.resource_type_code := p_resource_type_code;
154 END IF;
155
156 csf_trips_pub.find_trip(
157 p_api_version => 1
158 , p_init_msg_list => fnd_api.g_false
159 , x_return_status => x_return_status
160 , x_msg_data => x_msg_data
161 , x_msg_count => x_msg_count
162 , p_resource_id => l_task_info.resource_id
163 , p_resource_type => l_task_info.resource_type_code
164 , p_start_date_time => l_new_start_date
165 , p_end_date_time => l_new_end_date
166 , p_overtime_flag => fnd_api.g_true
167 , x_trip => l_trip
168 );
169
170 -- Error would be returned only if there are no trips or multiple trips
171 -- found. We should continue in those cases.
172 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
173 RAISE fnd_api.g_exc_unexpected_error;
174 END IF;
175
176 IF l_new_start_date IS NULL THEN
177 -- Start Date is NULL. That means there is no timings. Clear the Trip Id
178 l_trip.trip_id := NULL;
179 END IF;
180
181 --
182 -- Determine whether we have to update the availability of the Old Trip
183 --
184 l_old_booked_time := 0;
185 IF l_task_info.old_trip_id IS NOT NULL THEN
186 IF l_task_info.actual_start_date IS NOT NULL THEN
187 l_old_start_date := l_task_info.actual_start_date;
188 l_old_end_date := l_task_info.actual_end_date;
189
190 IF l_old_end_date IS NULL THEN
191 l_old_end_date := l_old_start_date + NVL(l_task_info.actual_effort, l_task_info.planned_effort) / (60*24);
192 END IF;
193 END IF;
194
195 IF l_old_start_date IS NULL OR l_old_end_date IS NULL THEN
196 l_old_start_date := l_task_info.scheduled_start_date;
197 l_old_end_date := l_task_info.scheduled_end_date;
198
199 IF l_old_end_date IS NULL AND l_task_info.planned_effort IS NOT NULL THEN
200 l_old_end_date := l_old_start_date + l_task_info.planned_effort / (60*24);
201 END IF;
202 END IF;
203
204 IF l_task_info.actual_travel_duration IS NOT NULL
205 OR l_task_info.sched_travel_duration IS NOT NULL THEN
206 l_old_start_date := l_old_start_date
207 - NVL(l_task_info.actual_travel_duration, l_task_info.sched_travel_duration)
208 / (60 * 24);
209 END IF;
210
211 l_old_booked_time := (l_old_end_date - l_old_start_date) * 24;
212
213 IF l_task_info.old_trip_id <> NVL(l_trip.trip_id, -999) THEN
214 csf_trips_pub.update_trip(
215 p_api_version => 1
216 , p_init_msg_list => fnd_api.g_false
217 , x_return_status => x_return_status
218 , x_msg_data => x_msg_data
219 , x_msg_count => x_msg_count
220 , p_trip_id => l_task_info.old_trip_id
221 , p_object_version_number => l_task_info.old_trip_ovn
222 , p_upd_available_hours => l_old_booked_time
223 , p_available_hours_before => fnd_api.g_miss_num
224 , p_available_hours_after => fnd_api.g_miss_num
225 );
226
227 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
228 RAISE fnd_api.g_exc_unexpected_error;
229 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
230 RAISE fnd_api.g_exc_error;
231 END IF;
232
233 l_old_booked_time := 0; -- Clear it so that it doesnt affect the new trip
234 END IF;
235 END IF;
236
237 --
238 -- Determine whether we have to update the availability of the New Trip
239 --
240 IF NVL(l_trip.trip_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
241 l_travel_time := 0;
242 IF NVL(p_actual_travel_duration, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
243 l_travel_time := csf_util_pvt.convert_to_minutes(p_actual_travel_duration, p_actual_travel_duration_uom);
244 ELSIF NVL(p_sched_travel_duration, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
245 l_travel_time := csf_util_pvt.convert_to_minutes(p_sched_travel_duration, p_sched_travel_duration);
246 ELSIF NVL(l_task_info.actual_travel_duration, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
247 l_travel_time := l_task_info.actual_travel_duration;
248 ELSIF NVL(l_task_info.sched_travel_duration, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
249 l_travel_time := l_task_info.sched_travel_duration;
250 END IF;
251
252 l_new_booked_time := (l_new_end_date - l_new_start_date) * 24 -- Scheduled Dates
253 + l_travel_time / 60 -- Travel Time (in mins)
254 - l_old_booked_time; -- Old Booked Time
255
256 IF ROUND(l_new_booked_time, 5) <> 0 THEN
257 csf_trips_pub.update_trip(
258 p_api_version => 1
259 , p_init_msg_list => fnd_api.g_false
260 , x_return_status => x_return_status
261 , x_msg_data => x_msg_data
262 , x_msg_count => x_msg_count
263 , p_trip_id => l_trip.trip_id
264 , p_object_version_number => l_trip.object_version_number
265 , p_upd_available_hours => - l_new_booked_time
266 , p_available_hours_before => fnd_api.g_miss_num
267 , p_available_hours_after => fnd_api.g_miss_num
268 );
269 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
270 RAISE fnd_api.g_exc_unexpected_error;
271 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
272 RAISE fnd_api.g_exc_error;
273 END IF;
274 END IF;
275
276 END IF;
277
278 x_trip_id := l_trip.trip_id;
279 EXCEPTION
280 WHEN fnd_api.g_exc_error THEN
281 x_trip_id := NULL;
282 x_return_status := fnd_api.g_ret_sts_error;
283 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
284 WHEN fnd_api.g_exc_unexpected_error THEN
285 x_trip_id := NULL;
286 x_return_status := fnd_api.g_ret_sts_unexp_error;
287 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
288 WHEN OTHERS THEN
289 x_return_status := fnd_api.g_ret_sts_unexp_error;
290 x_trip_id := NULL;
291 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
292 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
293 END IF;
294 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
295 END update_trip_info;
296
297 /**
298 * Propagate the Assignment Status Change to its dependent Objects like
299 * Task, Parent Task and Child Tasks, Spares, etc.
300 */
301 PROCEDURE propagate_status_change(
302 x_return_status OUT NOCOPY VARCHAR2
303 , x_msg_count OUT NOCOPY NUMBER
304 , x_msg_data OUT NOCOPY VARCHAR2
305 , p_task_assignment_id IN NUMBER
306 , p_object_version_number IN OUT NOCOPY NUMBER
307 , p_new_assignment_status_id IN NUMBER
308 , p_update_task IN VARCHAR2
309 , p_new_sts_cancelled_flag IN VARCHAR2
310 , x_task_object_version_number OUT NOCOPY NUMBER
311 , x_task_status_id OUT NOCOPY NUMBER
312 ) IS
313 l_api_name CONSTANT VARCHAR2(30) := 'PROPAGATE_STATUS_CHANGE';
314
315 -- Cursor to fetch Information about the Assignment, Task and Trip
316 CURSOR c_task_info IS
317 SELECT t.task_id
318 , t.object_version_number
319 , t.task_status_id
320 , t.scheduled_start_date
321 , t.scheduled_end_date
322 , t.task_split_flag
323 , t.parent_task_id
324 , (SELECT pt.object_version_number FROM jtf_tasks_b pt WHERE pt.task_id = t.parent_task_id) parent_task_ovn
325 , t.source_object_type_code
326 , ta.resource_id
327 , ta.resource_type_code
328 , cac.object_capacity_id trip_id
329 , cac.object_version_number trip_ovn
330 , NVL(
331 ( SELECT 'Y'
332 FROM jtf_task_assignments ta2, jtf_task_statuses_b ts2
333 WHERE ta2.task_id = t.task_id
334 AND ta2.task_assignment_id <> ta.task_assignment_id
335 AND ts2.task_status_id = ta2.assignment_status_id
336 AND NVL(ts2.cancelled_flag, 'N') <> 'Y'
337 AND NVL(ts2.rejected_flag, 'N') <> 'Y'
338 AND ta2.assignee_role = 'ASSIGNEE'
339 AND ta2.assignment_status_id <> ta.assignment_status_id
340 AND ROWNUM = 1
341 )
342 , 'N'
343 ) other_ta_exists
344 FROM jtf_tasks_b t
345 , jtf_task_assignments ta
346 , cac_sr_object_capacity cac
347 WHERE ta.task_assignment_id = p_task_assignment_id
348 AND t.task_id = ta.task_id
349 AND cac.object_capacity_id (+) = ta.object_capacity_id;
350
351 cursor c_tasks is
352 select jta.assignment_status_id,jtb.validation_start_date,jtb.validation_end_date,jta.task_id,JTA.OBJECT_CAPACITY_ID
353 from jtf_Task_assignments jta , jtf_task_statuses_b jtb
354 where jta.assignment_status_id= jtb.task_status_id
355 and jta.task_assignment_id=p_task_assignment_id
356 and jtb.enforce_validation_flag = 'Y'
357 and nvl(jtb.validation_start_date,sysdate) <= sysdate
358 and nvl(jtb.validation_end_date,sysdate) >= sysdate;
359
360 l_task_info c_task_info%ROWTYPE;
361 l_scheduled_start DATE;
362 l_scheduled_end DATE;
363 L_TRIP NUMBER;
364 val VARCHAR2(100):= 'TRUE';
365 l_trip_id NUMBER;
366 L_TASK NUMBER;
367 L_TRIP_START DATE;
368
369 BEGIN
370 x_return_status := fnd_api.g_ret_sts_success;
371
372 OPEN c_task_info;
373 FETCH c_task_info INTO l_task_info;
374 CLOSE c_task_info;
375
376 -- If there is only one active task assignment (ignoring Closed, Completed, Cancelled
377 -- or Rejected Assignments), then the new Status should be propagated to Task also
378 -- for both of them to be in Sync.
379 x_task_object_version_number := l_task_info.object_version_number;
380 x_task_status_id := l_task_info.task_status_id;
381 IF p_update_task IS NULL OR p_update_task = fnd_api.g_true THEN
382 IF l_task_info.other_ta_exists = 'N' AND l_task_info.task_status_id <> p_new_assignment_status_id THEN
383 x_task_status_id := p_new_assignment_status_id;
384
385 -- The Task is going to be cancelled... Clear the Scheduled Dates
386 IF p_new_sts_cancelled_flag = 'Y' AND l_task_info.source_object_type_code = 'SR' THEN
387 l_scheduled_start := NULL;
388 l_scheduled_end := NULL;
389 ELSE
390 l_scheduled_start := csf_util_pvt.get_miss_date;
391 l_scheduled_end := csf_util_pvt.get_miss_date;
392 END IF;
393 -- cross task validation
394
395
396 -- No other open Task Assignments. Update the Task also.
397 jtf_tasks_pub.update_task(
398 p_api_version => 1.0
399 , x_return_status => x_return_status
400 , x_msg_count => x_msg_count
401 , x_msg_data => x_msg_data
402 , p_task_id => l_task_info.task_id
403 , p_task_status_id => x_task_status_id
404 , p_object_version_number => x_task_object_version_number
405 , p_scheduled_start_date => l_scheduled_start
406 , p_scheduled_end_date => l_scheduled_end
407 , p_enable_workflow => fnd_api.g_miss_char
408 , p_abort_workflow => fnd_api.g_miss_char
409 );
410
411 IF x_return_status = fnd_api.g_ret_sts_error THEN
412 RAISE fnd_api.g_exc_error;
413 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
414 RAISE fnd_api.g_exc_unexpected_error;
415 END IF;
416 END IF;
417
418 -- update the Parent Task so that it is having the correct Scheduled Dates.
419 IF l_task_info.task_split_flag = 'D' THEN
420 -- Sync up the Parent and all the other Siblings
421 csf_tasks_pub.update_task_longer_than_shift(
422 p_api_version => 1.0
423 , p_init_msg_list => fnd_api.g_false
424 , p_commit => fnd_api.g_false
425 , x_return_status => x_return_status
426 , x_msg_count => x_msg_count
427 , x_msg_data => x_msg_data
428 , p_task_id => l_task_info.parent_task_id
429 , p_object_version_number => l_task_info.parent_task_ovn
430 , p_action => csf_tasks_pub.g_action_normal_to_parent
431 );
432 IF x_return_status = fnd_api.g_ret_sts_error THEN
433 RAISE fnd_api.g_exc_error;
434 ELSIF 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
440 -- If the new Assignment Status has Cancelled Flag, Delete the Spares
441 -- Reservations created against the Task Assignment. Increase the Trip
442 -- Availability.
443 IF p_new_sts_cancelled_flag = 'Y' THEN
444 csp_sch_int_pvt.clean_material_transaction(
445 p_api_version_number => 1.0
446 , p_task_assignment_id => p_task_assignment_id
447 , x_return_status => x_return_status
448 , x_msg_count => x_msg_count
449 , x_msg_data => x_msg_data
450 );
451 IF x_return_status = fnd_api.g_ret_sts_error THEN
452 RAISE fnd_api.g_exc_error;
453 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
454 RAISE fnd_api.g_exc_unexpected_error;
455 END IF;
456
457 -- Assignment was previously linked to a trip. Increase its Availability
458 IF l_task_info.trip_id IS NOT NULL THEN
459 update_trip_info(
460 x_return_status => x_return_status
461 , x_msg_count => x_msg_count
462 , x_msg_data => x_msg_data
463 , p_task_assignment_id => p_task_assignment_id
464 , p_task_id => l_task_info.task_id
465 , p_resource_id => l_task_info.resource_id
466 , p_resource_type_code => l_task_info.resource_type_code
467 , p_old_trip_id => l_task_info.trip_id
468 , p_old_trip_ovn => l_task_info.trip_ovn
469 , x_trip_id => l_task_info.trip_id
470 );
471
472 -- Error out only when we have unexpected error.
473 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
474 RAISE fnd_api.g_exc_unexpected_error;
475 END IF;
476 END IF;
477 END IF;
478 END propagate_status_change;
479
480 /**
481 * Creates a New Task Assignment for the given Task with the given attributes.
482 *
483 * If there exists any Cancelled Task Assignment for the Task with the given
484 * Resource Information, then that Task Assignment is reused rather than creating a
485 * new Task Assignment afresh.
486 * <br>
487 * If the Trip ID corresponding to the Task Assignment is passed as FND_API.G_MISS_NUM
488 * then the user doesnt want to link the Assignment to any Trip. So the Trip ID will
489 * be saved as NULL corresponding to the Task Assignment.
490 * If Trip ID is passed as NULL or not passed at all, then the API will try to find a
491 * Trip corresponding to the Assignment. Since we are dependent on Trips Model, any
492 * Assignment created for a Field Service Task should be linked to a Trip (based on
493 * Actual Date / Scheduled Dates). If there exists no Trip or there exists multiple trips,
494 * then the API will error out. If Assignment shouldnt be linked to any Trip, then
495 * Trip ID should be passed as FND_API.G_MISS_NUM.
496 * <br>
497 * Except for Task ID, Resouce ID, Resource Type Code all other parameters are optional.
498 */
499 PROCEDURE create_task_assignment(
500 p_api_version IN NUMBER
501 , p_init_msg_list IN VARCHAR2
502 , p_commit IN VARCHAR2
503 , p_validation_level IN NUMBER
504 , x_return_status OUT NOCOPY VARCHAR2
505 , x_msg_count OUT NOCOPY NUMBER
506 , x_msg_data OUT NOCOPY VARCHAR2
507 , p_task_assignment_id IN NUMBER
508 , p_task_id IN NUMBER
509 , p_task_name IN VARCHAR2
510 , p_task_number IN VARCHAR2
511 , p_resource_type_code IN VARCHAR2
512 , p_resource_id IN NUMBER
513 , p_resource_name IN VARCHAR2
514 , p_actual_effort IN NUMBER
515 , p_actual_effort_uom IN VARCHAR2
516 , p_schedule_flag IN VARCHAR2
517 , p_alarm_type_code IN VARCHAR2
518 , p_alarm_contact IN VARCHAR2
519 , p_sched_travel_distance IN NUMBER
520 , p_sched_travel_duration IN NUMBER
521 , p_sched_travel_duration_uom IN VARCHAR2
522 , p_actual_travel_distance IN NUMBER
523 , p_actual_travel_duration IN NUMBER
524 , p_actual_travel_duration_uom IN VARCHAR2
525 , p_actual_start_date IN DATE
526 , p_actual_end_date IN DATE
527 , p_palm_flag IN VARCHAR2
528 , p_wince_flag IN VARCHAR2
529 , p_laptop_flag IN VARCHAR2
530 , p_device1_flag IN VARCHAR2
531 , p_device2_flag IN VARCHAR2
532 , p_device3_flag IN VARCHAR2
533 , p_resource_territory_id IN NUMBER
534 , p_assignment_status_id IN NUMBER
535 , p_shift_construct_id IN NUMBER
536 , p_object_capacity_id IN NUMBER
537 , p_update_task IN VARCHAR2
538 , p_attribute1 IN VARCHAR2 DEFAULT NULL
539 , p_attribute2 IN VARCHAR2 DEFAULT NULL
540 , p_attribute3 IN VARCHAR2 DEFAULT NULL
541 , p_attribute4 IN VARCHAR2 DEFAULT NULL
542 , p_attribute5 IN VARCHAR2 DEFAULT NULL
543 , p_attribute6 IN VARCHAR2 DEFAULT NULL
544 , p_attribute7 IN VARCHAR2 DEFAULT NULL
545 , p_attribute8 IN VARCHAR2 DEFAULT NULL
546 , p_attribute9 IN VARCHAR2 DEFAULT NULL
547 , p_attribute10 IN VARCHAR2 DEFAULT NULL
548 , p_attribute11 IN VARCHAR2 DEFAULT NULL
549 , p_attribute12 IN VARCHAR2 DEFAULT NULL
550 , p_attribute13 IN VARCHAR2 DEFAULT NULL
551 , p_attribute14 IN VARCHAR2 DEFAULT NULL
552 , p_attribute15 IN VARCHAR2 DEFAULT NULL
553 , p_attribute_category IN VARCHAR2 DEFAULT NULL
554 , x_task_assignment_id OUT NOCOPY NUMBER
555 , x_ta_object_version_number OUT NOCOPY NUMBER
556 , x_task_object_version_number OUT NOCOPY NUMBER
557 , x_task_status_id OUT NOCOPY NUMBER
558 ) IS
559 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TASK_ASSIGNMENT';
560 l_api_version CONSTANT NUMBER := 1.0;
561
562 CURSOR c_cancelled_assignments IS
563 SELECT ta.task_assignment_id
564 , ta.object_version_number
565 FROM jtf_task_assignments ta, jtf_task_statuses_b ts
566 WHERE ta.task_id = p_task_id
567 AND ta.resource_id = p_resource_id
568 AND ta.resource_type_code = p_resource_type_code
569 AND ta.assignment_status_id = ts.task_status_id
570 AND ta.actual_start_date IS NULL
571 AND ta.actual_end_date IS NULL
572 AND ts.cancelled_flag = 'Y';
573
574 CURSOR c_assignment_info IS
575 SELECT object_version_number
576 FROM jtf_task_assignments
577 WHERE task_assignment_id = x_task_assignment_id;
578
579 l_cancelled_assignments c_cancelled_assignments%ROWTYPE;
580 l_trans_valid VARCHAR2(1);
581 l_valid_statuses VARCHAR2(2000);
582 l_trip_id NUMBER;
583 l_start_date DATE;
584 l_end_date DATE;
585 BEGIN
586 SAVEPOINT csf_create_task_assignment_pub;
587
588 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
589 RAISE fnd_api.g_exc_unexpected_error;
590 END IF;
591
592 IF fnd_api.to_boolean(p_init_msg_list) THEN
593 fnd_msg_pub.initialize;
594 END IF;
595
596 x_return_status := fnd_api.g_ret_sts_success;
597 l_trip_id := p_object_capacity_id;
598
599 IF (p_validation_level IS NULL OR p_validation_level = fnd_api.g_valid_level_full) THEN
600 -- Validate Field Service status flow
601 csf_tasks_pub.validate_status_change(NULL, p_assignment_status_id);
602
603 -- Validate Trip ID passed. Trip ID has to a valid Trip given the Dates
604 -- and Resource Critieria.
605 -- If FND_API.G_MISS_NUM, then the caller wants to make Trip ID as NULL in the DB.
606 IF l_trip_id = fnd_api.g_miss_num THEN
607 l_trip_id := NULL;
608 ELSE
609 update_trip_info(
610 x_return_status => x_return_status
611 , x_msg_count => x_msg_count
612 , x_msg_data => x_msg_data
613 , p_task_assignment_id => p_task_assignment_id
614 , p_task_id => p_task_id
615 , p_resource_type_code => p_resource_type_code
616 , p_resource_id => p_resource_id
617 , p_actual_start_date => p_actual_start_date
618 , p_actual_end_date => p_actual_end_date
619 , p_actual_effort => p_actual_effort
620 , p_actual_effort_uom => p_actual_effort_uom
621 , p_actual_travel_duration => p_actual_travel_duration
622 , p_actual_travel_duration_uom => p_actual_travel_duration_uom
623 , p_sched_travel_duration => p_sched_travel_duration
624 , p_sched_travel_duration_uom => p_sched_travel_duration_uom
625 , x_trip_id => l_trip_id
626 );
627 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
628 RAISE fnd_api.g_exc_unexpected_error;
629 END IF;
630 END IF;
631 END IF;
632
633 -- Reuse a Cancelled Task Assignment of the Task rather than creating anew.
634 OPEN c_cancelled_assignments;
635 FETCH c_cancelled_assignments INTO l_cancelled_assignments;
636 CLOSE c_cancelled_assignments;
637
638 IF l_cancelled_assignments.task_assignment_id IS NOT NULL THEN
639 x_ta_object_version_number := l_cancelled_assignments.object_version_number;
640 x_task_assignment_id := l_cancelled_assignments.task_assignment_id;
641 update_task_assignment(
642 p_api_version => p_api_version
643 , p_init_msg_list => p_init_msg_list
644 , p_commit => fnd_api.g_false
645 , p_validation_level => fnd_api.g_valid_level_none
646 , x_return_status => x_return_status
647 , x_msg_count => x_msg_count
648 , x_msg_data => x_msg_data
649 , p_task_assignment_id => x_task_assignment_id
650 , p_object_version_number => x_ta_object_version_number
651 , p_task_id => p_task_id
652 , p_resource_type_code => p_resource_type_code
653 , p_resource_id => p_resource_id
654 , p_resource_territory_id => p_resource_territory_id
655 , p_assignment_status_id => p_assignment_status_id
656 , p_actual_start_date => p_actual_start_date
657 , p_actual_end_date => p_actual_end_date
658 , p_sched_travel_distance => p_sched_travel_distance
659 , p_sched_travel_duration => p_sched_travel_duration
660 , p_sched_travel_duration_uom => p_sched_travel_duration_uom
661 , p_shift_construct_id => p_shift_construct_id
662 , p_object_capacity_id => l_trip_id
663 , p_update_task => p_update_task
664 , p_attribute1 => p_attribute1
665 , p_attribute2 => p_attribute2
666 , p_attribute3 => p_attribute3
667 , p_attribute4 => p_attribute4
668 , p_attribute5 => p_attribute5
669 , p_attribute6 => p_attribute6
670 , p_attribute7 => p_attribute7
671 , p_attribute8 => p_attribute8
672 , p_attribute9 => p_attribute9
673 , p_attribute10 => p_attribute10
674 , p_attribute11 => p_attribute11
675 , p_attribute12 => p_attribute12
676 , p_attribute13 => p_attribute13
677 , p_attribute14 => p_attribute14
678 , p_attribute15 => p_attribute15
679 , p_attribute_category => p_attribute_category
680 , x_task_object_version_number => x_task_object_version_number
681 , x_task_status_id => x_task_status_id
682 );
683 ELSE
684 jtf_task_assignments_pub.create_task_assignment(
685 p_api_version => 1.0
686 , x_return_status => x_return_status
687 , x_msg_count => x_msg_count
688 , x_msg_data => x_msg_data
689 , p_task_assignment_id => p_task_assignment_id
690 , p_task_id => p_task_id
691 , p_task_name => p_task_name
692 , p_task_number => p_task_number
693 , p_resource_type_code => p_resource_type_code
694 , p_resource_id => p_resource_id
695 , p_assignment_status_id => p_assignment_status_id
696 , p_object_capacity_id => l_trip_id
697 , p_actual_effort => p_actual_effort
698 , p_actual_effort_uom => p_actual_effort_uom
699 , p_schedule_flag => p_schedule_flag
700 , p_alarm_type_code => p_alarm_type_code
701 , p_alarm_contact => p_alarm_contact
702 , p_sched_travel_distance => p_sched_travel_distance
703 , p_sched_travel_duration => p_sched_travel_duration
704 , p_sched_travel_duration_uom => p_sched_travel_duration_uom
705 , p_actual_travel_distance => p_actual_travel_distance
706 , p_actual_travel_duration => p_actual_travel_duration
707 , p_actual_travel_duration_uom => p_actual_travel_duration_uom
708 , p_actual_start_date => p_actual_start_date
709 , p_actual_end_date => p_actual_end_date
710 , p_palm_flag => p_palm_flag
711 , p_wince_flag => p_wince_flag
712 , p_laptop_flag => p_laptop_flag
713 , p_device1_flag => p_device1_flag
714 , p_device2_flag => p_device2_flag
715 , p_device3_flag => p_device3_flag
716 , p_resource_territory_id => p_resource_territory_id
717 , p_shift_construct_id => p_shift_construct_id
718 , p_enable_workflow => fnd_api.g_miss_char
719 , p_abort_workflow => fnd_api.g_miss_char
720 , p_attribute1 => p_attribute1
721 , p_attribute2 => p_attribute2
722 , p_attribute3 => p_attribute3
723 , p_attribute4 => p_attribute4
724 , p_attribute5 => p_attribute5
725 , p_attribute6 => p_attribute6
726 , p_attribute7 => p_attribute7
727 , p_attribute8 => p_attribute8
728 , p_attribute9 => p_attribute9
729 , p_attribute10 => p_attribute10
730 , p_attribute11 => p_attribute11
731 , p_attribute12 => p_attribute12
732 , p_attribute13 => p_attribute13
733 , p_attribute14 => p_attribute14
734 , p_attribute15 => p_attribute15
735 , p_attribute_category => p_attribute_category
736 , x_task_assignment_id => x_task_assignment_id
737 );
738
739 IF x_return_status = fnd_api.g_ret_sts_error THEN
740 RAISE fnd_api.g_exc_error;
741 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
742 RAISE fnd_api.g_exc_unexpected_error;
743 END IF;
744
745 OPEN c_assignment_info;
746 FETCH c_assignment_info INTO x_ta_object_version_number;
747 CLOSE c_assignment_info;
748
749 -- Update the Assignment Status and thereby Synchronizing with Task
750 propagate_status_change(
751 x_return_status => x_return_status
752 , x_msg_count => x_msg_count
753 , x_msg_data => x_msg_data
754 , p_task_assignment_id => x_task_assignment_id
755 , p_object_version_number => x_ta_object_version_number
756 , p_new_assignment_status_id => p_assignment_status_id
757 , p_update_task => p_update_task
758 , p_new_sts_cancelled_flag => 'N'
759 , x_task_object_version_number => x_task_object_version_number
760 , x_task_status_id => x_task_status_id
761 );
762 END IF;
763
764 -- Standard check of p_commit
765 IF fnd_api.to_boolean(p_commit) THEN
766 COMMIT WORK;
767 END IF;
768 EXCEPTION
769 WHEN fnd_api.g_exc_error THEN
770 ROLLBACK TO csf_create_task_assignment_pub;
771 x_return_status := fnd_api.g_ret_sts_error;
772 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
773 WHEN fnd_api.g_exc_unexpected_error THEN
774 ROLLBACK TO csf_create_task_assignment_pub;
775 x_return_status := fnd_api.g_ret_sts_unexp_error;
776 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
777 WHEN OTHERS THEN
778 x_return_status := fnd_api.g_ret_sts_unexp_error;
779 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
780 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
781 END IF;
782 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
783 ROLLBACK TO csf_create_task_assignment_pub;
784 END create_task_assignment;
785
786 /**
787 * Update an existing Task Assignment with new Task Attributes
788 *
789 * Given the Task Assignment ID and Task Object Version Number, it calls
790 * JTF Task Assignment API to update the Task Assignment with the new Attributes.
791 * It is actually a two step process
792 * 1. Updating the Task Assignment with the new Task Attributes except Status
793 * 2. Updating the Task Assignment with the new Task Status (if not FND_API.G_MISS_NUM)
794 * by calling UPDATE_ASSIGNMENT_STATUS.
795 * <br>
796 * Because of the two step process, the returned Task Assignment Object
797 * Version Number might be incremented by 2 when user might have expected an
798 * increment of only 1.
799 * <br>
800 * Except Task Assignment ID and Object Version Number parameters, all are optional.
801 */
802 PROCEDURE update_task_assignment(
803 p_api_version IN NUMBER
804 , p_init_msg_list IN VARCHAR2
805 , p_commit IN VARCHAR2
806 , p_validation_level IN NUMBER
807 , x_return_status OUT NOCOPY VARCHAR2
808 , x_msg_count OUT NOCOPY NUMBER
809 , x_msg_data OUT NOCOPY VARCHAR2
810 , p_task_assignment_id IN NUMBER
811 , p_object_version_number IN OUT NOCOPY NUMBER
812 , p_task_id IN NUMBER
813 , p_resource_type_code IN VARCHAR2
814 , p_resource_id IN NUMBER
815 , p_resource_territory_id IN NUMBER
816 , p_assignment_status_id IN NUMBER
817 , p_actual_start_date IN DATE
818 , p_actual_end_date IN DATE
819 , p_sched_travel_distance IN NUMBER
820 , p_sched_travel_duration IN NUMBER
821 , p_sched_travel_duration_uom IN VARCHAR2
822 , p_shift_construct_id IN NUMBER
823 , p_object_capacity_id IN NUMBER
824 , p_update_task IN VARCHAR2
825 , p_task_number IN VARCHAR2
826 , p_task_name IN VARCHAR2
827 , p_resource_name IN VARCHAR2
828 , p_actual_effort IN NUMBER
829 , p_actual_effort_uom IN VARCHAR2
830 , p_actual_travel_distance IN NUMBER
831 , p_actual_travel_duration IN NUMBER
832 , p_actual_travel_duration_uom IN VARCHAR2
833 , p_attribute1 IN VARCHAR2
834 , p_attribute2 IN VARCHAR2
835 , p_attribute3 IN VARCHAR2
836 , p_attribute4 IN VARCHAR2
837 , p_attribute5 IN VARCHAR2
838 , p_attribute6 IN VARCHAR2
839 , p_attribute7 IN VARCHAR2
840 , p_attribute8 IN VARCHAR2
841 , p_attribute9 IN VARCHAR2
842 , p_attribute10 IN VARCHAR2
843 , p_attribute11 IN VARCHAR2
844 , p_attribute12 IN VARCHAR2
845 , p_attribute13 IN VARCHAR2
846 , p_attribute14 IN VARCHAR2
847 , p_attribute15 IN VARCHAR2
848 , p_attribute_category IN VARCHAR2
849 , p_show_on_calendar IN VARCHAR2
850 , p_category_id IN NUMBER
851 , p_schedule_flag IN VARCHAR2
852 , p_alarm_type_code IN VARCHAR2
853 , p_alarm_contact IN VARCHAR2
854 , p_palm_flag IN VARCHAR2
855 , p_wince_flag IN VARCHAR2
856 , p_laptop_flag IN VARCHAR2
857 , p_device1_flag IN VARCHAR2
858 , p_device2_flag IN VARCHAR2
859 , p_device3_flag IN VARCHAR2
860 , p_enable_workflow IN VARCHAR2
861 , p_abort_workflow IN VARCHAR2
862 , x_task_object_version_number OUT NOCOPY NUMBER
863 , x_task_status_id OUT NOCOPY NUMBER
864 ) IS
865 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TASK_ASSIGNMENT';
866 l_api_version CONSTANT NUMBER := 1.0;
867
868 -- cursor to fetch the Cancelled Flag corresponding to the new Task Status.
869 CURSOR c_task_status_info IS
870 SELECT NVL (ts.cancelled_flag, 'N') cancelled_flag
871 FROM jtf_task_statuses_b ts
872 WHERE ts.task_status_id = p_assignment_status_id;
873
874 -- cursor to fetch Information about the Task Assignment.
875 CURSOR c_task_assignment_info IS
876 SELECT ta.assignment_status_id, ta.task_id
877 FROM jtf_task_assignments ta
878 WHERE task_assignment_id = p_task_assignment_id;
879
880 cursor c_tasks is
881 select jta.assignment_status_id,jta.task_id,JTA.OBJECT_CAPACITY_ID,object_version_number
882 from jtf_Task_assignments jta
883 where jta.task_assignment_id=p_task_assignment_id;
884
885 CURSOR c_cross_task(p_sched_start_date date, p_sched_end_date date)
886 IS
887 select jtB.TASK_status_id,jtb.validation_start_date,jtb.validation_end_date
888 from jtf_task_statuses_b jtb
889 where jtb.enforce_validation_flag = 'Y'
890 and nvl(jtb.validation_start_date,nvl(trunc(p_sched_start_date),sysdate)) <= nvl(trunc(p_sched_start_date),sysdate)
891 and nvl(jtb.validation_end_date,nvl(trunc(p_sched_end_date),sysdate)) >= nvl(trunc(p_sched_end_date),sysdate);
892
893 CURSOR TRIP_SD(L_TRIP_ID NUMBER)
894 IS
895 SELECT START_dATE_TIME
896 FROM CAC_SR_OBJECT_CAPACITY
897 WHERE OBJECT_CAPACITY_ID = L_TRIP_ID;
898 cursor c_Task_number(l_task number)
899 is
900 select task_number
901 from jtf_tasks_b
902 where task_id=l_task;
903
904 cursor c_task_status(l_task number)
905 is
906 select name
907 from jtf_task_statuses_tl jl
908 where jl.task_status_id=l_task
909 and language=userenv('lang');
910
911 cursor c_scheduled_dates(p_task_id number)
912 IS
913 select scheduled_start_date, scheduled_end_date
914 from jtf_tasks_b
915 where task_id =p_task_id
916 and nvl(deleted_flag,'N')<>'Y';
917
918 scheduled_dates c_scheduled_dates%rowtype;
919
920
921 l_task_id NUMBER;
922 l_old_assignment_status_id NUMBER;
923 l_new_sts_cancelled_flag VARCHAR2(1);
924 l_trip_id NUMBER;
925 l_distance NUMBER;
926 l_duration NUMBER;
927 l_duration_uom VARCHAR2(3);
928 l_trip NUMBER;
929 val VARCHAR2(100):= 'TRUE';
930 --l_trip_id NUMBER;
931 L_TASK NUMBER;
932 L_TRIP_START DATE;
933 l_sts NUMBER;
934 l_task_name VARCHAR2(200);
935 l_task_number VARCHAR2(200);
936 l_modified_ver_no NUMBER;
937
938
939
940 BEGIN
941 SAVEPOINT csf_update_task_assignment_pub;
942
943 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
944 RAISE fnd_api.g_exc_unexpected_error;
945 END IF;
946
947 IF fnd_api.to_boolean(p_init_msg_list) THEN
948 fnd_msg_pub.initialize;
949 END IF;
950
951 x_return_status := fnd_api.g_ret_sts_success;
952
953 -- Fetch the Task Assignment Information
954 OPEN c_task_assignment_info;
955 FETCH c_task_assignment_info INTO l_old_assignment_status_id, l_task_id;
956 CLOSE c_task_assignment_info;
957
958 -- We require Task Id for computations. If the caller doesnt pass Task Id
959 -- lets retrieve it from JTF_TASK_ASSIGNMENTS. If the caller has indeeed
960 -- passed it, then use that value.
961 IF p_task_id <> fnd_api.g_miss_num AND p_task_id IS NOT NULL THEN
962 l_task_id := p_task_id;
963 END IF;
964
965 l_trip_id := p_object_capacity_id;
966
967 -- If Assignment is cancelled, then we have to clear the Scheduled Travel
968 -- Duration, Distance and Trip ID.
969 l_distance := p_sched_travel_distance;
970 l_duration := p_sched_travel_duration;
971 l_duration_uom := p_sched_travel_duration_uom;
972 l_new_sts_cancelled_flag := 'N';
973 IF p_assignment_status_id <> fnd_api.g_miss_num THEN
974 OPEN c_task_status_info;
975 FETCH c_task_status_info INTO l_new_sts_cancelled_flag;
976 CLOSE c_task_status_info;
977
978 IF l_new_sts_cancelled_flag = 'Y' THEN
979 l_distance := NULL;
980 l_duration := NULL;
981 l_duration_uom := NULL;
982 l_trip_id := NULL;
983 END IF;
984 END IF;
985
986 open c_scheduled_dates(l_task_id);
987 fetch c_scheduled_dates into scheduled_dates;
988 close c_scheduled_dates;
989
990
991 -- cross task validation
992 FOR i IN c_cross_task(scheduled_dates.scheduled_start_date,scheduled_dates.scheduled_end_date)
993 LOOP
994
995 open c_tasks;
996 fetch c_tasks into l_sts,l_task,l_trip,l_modified_ver_no;
997 close c_tasks;
998 IF i.task_status_id = p_assignment_status_id
999 THEN
1000 val := cross_task_val(p_task_assignment_id,p_assignment_status_id,l_task);
1001 IF val ='FALSE'
1002 THEN
1003
1004 OPEN TRIP_SD(L_TRIP);
1005 FETCH TRIP_SD INTO L_TRIP_START;
1006 CLOSE TRIP_SD;
1007 open c_task_number(l_task);
1008 fetch c_task_number into l_task_number;
1009 close c_task_number;
1010 open c_task_status(g_status);
1011 fetch c_task_status into l_task_name;
1012 close c_task_status;
1013 fnd_message.set_name('CSF','CSF_CROSSTASK_VALIDATION');
1014 fnd_message.set_token('TASK_NUMBER',l_task_number);
1015 fnd_message.set_token('TASK_ASSIGNMENT_STATUS',l_task_name);
1016 --fnd_message.set_token('TRIP_START_DATE',TO_CHAR(L_TRIP_START,'DD/MM/YYYY HH24:MI:SS'));
1017 fnd_msg_pub.add;
1018 raise fnd_api.g_exc_error;
1019 END IF;
1020 END IF;
1021 END LOOP;
1022
1023
1024 IF (p_validation_level IS NULL OR p_validation_level = fnd_api.g_valid_level_full) THEN
1025
1026 -- Validate Field Service status flow
1027 IF p_assignment_status_id <> fnd_api.g_miss_num
1028 AND NVL(l_old_assignment_status_id, -1) <> NVL(p_assignment_status_id, -1)
1029 THEN
1030 csf_tasks_pub.validate_status_change(l_old_assignment_status_id, p_assignment_status_id);
1031 END IF;
1032
1033
1034
1035 -- If Trip ID is passed as FND_API.G_MISS_NUM.. and Actuals are passed, we need to link
1036 -- the Task Assignment to the correct Trip.
1037 IF l_trip_id IS NOT NULL AND l_new_sts_cancelled_flag = 'N' THEN
1038
1039 update_trip_info(
1040 x_return_status => x_return_status
1041 , x_msg_count => x_msg_count
1042 , x_msg_data => x_msg_data
1043 , p_task_id => l_task_id
1044 , p_task_assignment_id => p_task_assignment_id
1045 , p_resource_type_code => p_resource_type_code
1046 , p_resource_id => p_resource_id
1047 , p_actual_start_date => p_actual_start_date
1048 , p_actual_end_date => p_actual_end_date
1049 , p_actual_effort => p_actual_effort
1050 , p_actual_effort_uom => p_actual_effort_uom
1051 , p_actual_travel_duration => p_actual_travel_duration
1052 , p_actual_travel_duration_uom => p_actual_travel_duration_uom
1053 , p_sched_travel_duration => p_sched_travel_duration
1054 , p_sched_travel_duration_uom => p_sched_travel_duration_uom
1055 , x_trip_id => l_trip_id
1056 );
1057 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1058 RAISE fnd_api.g_exc_unexpected_error;
1059 ELSIF x_return_status = fnd_api.g_ret_sts_success
1060 THEN
1061 open c_tasks;
1062 fetch c_tasks into l_sts,l_task,l_trip,l_modified_ver_no;
1063 close c_tasks;
1064
1065
1066 END IF;
1067 END IF;
1068 END IF;
1069 IF l_modified_ver_no IS NULL
1070 THEN
1071 l_modified_ver_no :=p_object_version_number;
1072 END IF;
1073 jtf_task_assignments_pub.update_task_assignment(
1074 p_api_version => 1.0
1075 , x_return_status => x_return_status
1076 , x_msg_count => x_msg_count
1077 , x_msg_data => x_msg_data
1078 , p_task_assignment_id => p_task_assignment_id
1079 , p_object_version_number => l_modified_ver_no
1080 , p_task_id => l_task_id
1081 , p_resource_type_code => p_resource_type_code
1082 , p_resource_id => p_resource_id
1083 , p_resource_territory_id => p_resource_territory_id
1084 , p_assignment_status_id => p_assignment_status_id
1085 , p_actual_start_date => p_actual_start_date
1086 , p_actual_end_date => p_actual_end_date
1087 , p_sched_travel_distance => l_distance
1088 , p_sched_travel_duration => l_duration
1089 , p_sched_travel_duration_uom => l_duration_uom
1090 , p_shift_construct_id => p_shift_construct_id
1091 , p_object_capacity_id => l_trip_id
1092 , p_task_number => p_task_number
1093 , p_task_name => p_task_name
1094 , p_resource_name => p_resource_name
1095 , p_actual_effort => p_actual_effort
1096 , p_actual_effort_uom => p_actual_effort_uom
1097 , p_actual_travel_distance => p_actual_travel_distance
1098 , p_actual_travel_duration => p_actual_travel_duration
1099 , p_actual_travel_duration_uom => p_actual_travel_duration_uom
1100 , p_attribute1 => p_attribute1
1101 , p_attribute2 => p_attribute2
1102 , p_attribute3 => p_attribute3
1103 , p_attribute4 => p_attribute4
1104 , p_attribute5 => p_attribute5
1105 , p_attribute6 => p_attribute6
1106 , p_attribute7 => p_attribute7
1107 , p_attribute8 => p_attribute8
1108 , p_attribute9 => p_attribute9
1109 , p_attribute10 => p_attribute10
1110 , p_attribute11 => p_attribute11
1111 , p_attribute12 => p_attribute12
1112 , p_attribute13 => p_attribute13
1113 , p_attribute14 => p_attribute14
1114 , p_attribute15 => p_attribute15
1115 , p_attribute_category => p_attribute_category
1116 , p_show_on_calendar => p_show_on_calendar
1117 , p_category_id => p_category_id
1118 , p_schedule_flag => p_schedule_flag
1119 , p_alarm_type_code => p_alarm_type_code
1120 , p_alarm_contact => p_alarm_contact
1121 , p_palm_flag => p_palm_flag
1122 , p_wince_flag => p_wince_flag
1123 , p_laptop_flag => p_laptop_flag
1124 , p_device1_flag => p_device1_flag
1125 , p_device2_flag => p_device2_flag
1126 , p_device3_flag => p_device3_flag
1127 , p_enable_workflow => p_enable_workflow
1128 , p_abort_workflow => p_abort_workflow
1129 );
1130
1131 IF x_return_status = fnd_api.g_ret_sts_error THEN
1132 RAISE fnd_api.g_exc_error;
1133 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1134 RAISE fnd_api.g_exc_unexpected_error;
1135 END IF;
1136
1137 -- We have JTF_TASK_ASSIGNMENT.UPDATE_TASK_ASSIGNMENT we are passing IN/OUT parameter
1138 -- as l_modified_ver_no. So when assignment is updated this variable will have incremented
1139 -- assignment number. This incremented object version number was not passed to
1140 -- propogate_status_change procedure instead p_object_version_number this is wrong
1141 -- so i am assigning l_modified_ver_no to p_object_version_number so that it has
1142 -- current object version number
1143 p_object_version_number:=l_modified_ver_no;
1144 -- If Assignment Status is updated, then propagate the status to other objects
1145 IF p_assignment_status_id <> fnd_api.g_miss_num THEN
1146 propagate_status_change(
1147 x_return_status => x_return_status
1148 , x_msg_count => x_msg_count
1149 , x_msg_data => x_msg_data
1150 , p_task_assignment_id => p_task_assignment_id
1151 , p_object_version_number => p_object_version_number
1152 , p_new_assignment_status_id => p_assignment_status_id
1153 , p_update_task => p_update_task
1154 , p_new_sts_cancelled_flag => l_new_sts_cancelled_flag
1155 , x_task_object_version_number => x_task_object_version_number
1156 , x_task_status_id => x_task_status_id
1157 );
1158 IF x_return_status = fnd_api.g_ret_sts_error THEN
1159 RAISE fnd_api.g_exc_error;
1160 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1161 RAISE fnd_api.g_exc_unexpected_error;
1162 END IF;
1163 END IF;
1164
1165 -- Standard check of p_commit
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_task_assignment_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_task_assignment_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);
1186 ROLLBACK TO csf_update_task_assignment_pub;
1187 END update_task_assignment;
1188
1189 /**
1190 * Update the Status of the Task Assignment with the given Status and propagate to the
1191 * Task also if required.
1192 * <br>
1193 * Task Assignment is updated with the new Status if the Transition from the current
1194 * status to the new status is allowed as determined by
1195 * CSF_TASKS_PUB.VALIDATE_STATE_TRANSITION. Transition validation is done only
1196 * when Validation Level is passed as FULL.
1197 * <br>
1198 * In addition to updating the Task Assignment Status, the following operations are also
1199 * done
1200 * 1. If the Task corresponding to the given Task Assignment has no other
1201 * Open / Active Task Assignments other than the given one, then the Assignment
1202 * Status is propagated to the Task also. If there exists any other Active
1203 * Assignment, then the Task is not updated.
1204 * The parameters P_TASK_OBJECT_VERSION_NUMBER and X_TASK_STATUS_ID reflect
1205 * the Object Version Number and Task Status ID of the Task in Database
1206 * irrespective of the fact whether the update has taken place or not. <br>
1207 *
1208 * 2. If the Assignment goes to Cancelled (as per the new status), then if any
1209 * Spares Order is linked to the Assignment, they are cleaned up by calling
1210 * CLEAN_MATERIAL_TRANSACTION of Spares. <br>
1211 *
1212 * 3. If the Assignment goes to Assigned (as per the new status), and the
1213 * old status is not Assigned, then Orders are created and linked to the
1214 * Task Assignment. <br>
1215 *
1216 * 4. If the Assignnment goes to Working (as per the new status), then it means
1217 * that the Resource is working on the Task and so his location should be updated
1218 * to reflect the location of the Task. This is required by Map Functionality.
1219 * THIS IS WRONG AND SHOULD BE REMOVED. MAP SHOULD BE USING HZ_LOCATIONS TABLE. <br>
1220 *
1221 * @param p_api_version API Version (1.0)
1222 * @param p_init_msg_list Initialize Message List
1223 * @param p_commit Commit the Work
1224 * @param p_validation_level Validate the given Parameters
1225 * @param x_return_status Return Status of the Procedure.
1226 * @param x_msg_count Number of Messages in the Stack.
1227 * @param x_msg_data Stack of Error Messages.
1228 * @param p_task_assignment_id Task Assignment ID of the Assignment to be updated
1229 * @param p_assignment_status_id New Task Status ID for the Task Assignment.
1230 * @param p_show_on_calendar <Dont Know>
1231 * @param p_object_version_number Current Task Version and also container for new one.
1232 * @param x_task_object_version_number Task Object Version Number (either old or new)
1233 * @param x_task_status_id Task Status ID (either old or new)
1234 */
1235 PROCEDURE update_assignment_status(
1236 p_api_version IN NUMBER
1237 , p_init_msg_list IN VARCHAR2
1238 , p_commit IN VARCHAR2
1239 , p_validation_level IN NUMBER
1240 , x_return_status OUT NOCOPY VARCHAR2
1241 , x_msg_count OUT NOCOPY NUMBER
1242 , x_msg_data OUT NOCOPY VARCHAR2
1243 , p_task_assignment_id IN NUMBER
1244 , p_object_version_number IN OUT NOCOPY NUMBER
1245 , p_assignment_status_id IN NUMBER
1246 , p_update_task IN VARCHAR2
1247 , p_show_on_calendar IN VARCHAR2
1248 , x_task_object_version_number OUT NOCOPY NUMBER
1249 , x_task_status_id OUT NOCOPY NUMBER
1250 ) IS
1251 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ASSIGNMENT_STATUS';
1252 l_api_version CONSTANT NUMBER := 1.0;
1253
1254 -- cursor to fetch Information about the Task Assignment.
1255 CURSOR c_task_assignment_info IS
1256 SELECT ta.assignment_status_id,ta.task_id
1257 FROM jtf_task_assignments ta
1258 WHERE task_assignment_id = p_task_assignment_id;
1259
1260 -- Fetch the Cancelled Flag corresponding to the new Task Status.
1261 CURSOR c_task_status_info IS
1262 SELECT NVL (ts.cancelled_flag, 'N') cancelled_flag
1263 FROM jtf_task_statuses_b ts
1264 WHERE ts.task_status_id = p_assignment_status_id;
1265
1266 cursor c_tasks is
1267 select jta.assignment_status_id,jta.task_id,JTA.OBJECT_CAPACITY_ID
1268 from jtf_Task_assignments jta
1269 where jta.task_assignment_id=p_task_assignment_id;
1270
1271 CURSOR c_cross_task(p_sched_start_date date, p_sched_end_date date)
1272 IS
1273 select jtB.TASK_status_id,jtb.validation_start_date,jtb.validation_end_date
1274 from jtf_task_statuses_b jtb
1275 where jtb.enforce_validation_flag = 'Y'
1276 and nvl(jtb.validation_start_date,nvl(trunc(p_sched_start_date),sysdate)) <= nvl(trunc(p_sched_start_date),sysdate)
1277 and nvl(jtb.validation_end_date,nvl(trunc(p_sched_end_date),sysdate)) >= nvl(trunc(p_sched_end_date),sysdate);
1278
1279
1280
1281 CURSOR c_scheduled_dates(p_task_id number)
1282 IS
1283 SELECT scheduled_start_date, scheduled_end_date
1284 FROM jtf_tasks_b
1285 WHERE task_id =p_task_id
1286 AND nvl(deleted_flag,'N')<>'Y';
1287
1288 scheduled_dates c_scheduled_dates%rowtype;
1289
1290 CURSOR TRIP_SD(L_TRIP_ID NUMBER)
1291 IS
1292 SELECT START_dATE_TIME
1293 FROM CAC_SR_OBJECT_CAPACITY
1294 WHERE OBJECT_CAPACITY_ID = L_TRIP_ID;
1295
1296 cursor c_Task_number(l_task number)
1297 is
1298 select task_number
1299 from jtf_tasks_b
1300 where task_id=l_task;
1301 Cursor c_task_status(l_task number)
1302 IS
1303 select name
1304 from jtf_task_statuses_tl jl
1305 where jl.task_status_id=l_task
1306 and language=userenv('lang');
1307
1308
1309
1310
1311 l_old_assignment_status_id NUMBER;
1312 l_new_sts_cancelled_flag VARCHAR2(1);
1313 l_distance NUMBER;
1314 l_duration NUMBER;
1315 l_duration_uom VARCHAR2(3);
1316 l_trip_id NUMBER;
1317 val VARCHAR2(100):= 'TRUE';
1318 --l_trip_id NUMBER;
1319 L_TASK NUMBER;
1320 L_TRIP_START DATE;
1321 l_sts NUMBER;
1322 l_task_name VARCHAR2(200);
1323 l_task_number VARCHAR2(200);
1324 l_trip NUMBER;
1325 l_task_id NUMBER;
1326
1327
1328
1329
1330
1331 BEGIN
1332 SAVEPOINT csf_update_assign_status_pub;
1333
1334
1335 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1336 RAISE fnd_api.g_exc_unexpected_error;
1337 END IF;
1338
1339 IF fnd_api.to_boolean(p_init_msg_list) THEN
1340 fnd_msg_pub.initialize;
1341 END IF;
1342
1343 x_return_status := fnd_api.g_ret_sts_success;
1344
1345 -- Check whether there is anything update in Assignment Status.
1346 IF p_assignment_status_id = fnd_api.g_miss_num THEN
1347 RETURN;
1348 END IF;
1349
1350 OPEN c_task_assignment_info;
1351 FETCH c_task_assignment_info INTO l_old_assignment_status_id,l_task_id;
1352 CLOSE c_task_assignment_info;
1353
1354 IF l_old_assignment_status_id = p_assignment_status_id THEN
1355 RETURN;
1356 END IF;
1357
1358 IF (p_validation_level IS NULL OR p_validation_level = fnd_api.g_valid_level_full) THEN
1359 -- Validate Field Service status flow
1360 csf_tasks_pub.validate_status_change(l_old_assignment_status_id, p_assignment_status_id);
1361 END IF;
1362
1363 OPEN c_task_status_info;
1364 FETCH c_task_status_info INTO l_new_sts_cancelled_flag;
1365 CLOSE c_task_status_info;
1366
1367 IF l_new_sts_cancelled_flag = 'Y' THEN
1368 l_distance := NULL;
1369 l_duration := NULL;
1370 l_duration_uom := NULL;
1371 l_trip_id := NULL;
1372 ELSE
1373 l_distance := csf_util_pvt.get_miss_num;
1374 l_duration := csf_util_pvt.get_miss_num;
1375 l_duration_uom := csf_util_pvt.get_miss_char;
1376 l_trip_id := csf_util_pvt.get_miss_num;
1377 END IF;
1378
1379 open c_scheduled_dates(l_task_id);
1380 fetch c_scheduled_dates into scheduled_dates;
1381 close c_scheduled_dates;
1382
1383 -- cross task validation
1384 FOR i IN c_cross_task(scheduled_dates.scheduled_start_date,scheduled_dates.scheduled_end_date)
1385 LOOP
1386 open c_tasks;
1387 fetch c_tasks into l_sts,l_task,l_trip;
1388 close c_tasks;
1389 IF i.task_status_id = p_assignment_status_id
1390 THEN
1391 val := cross_task_val(p_task_assignment_id,p_assignment_status_id,l_task);
1392 IF val ='FALSE'
1393 THEN
1394
1395 OPEN TRIP_SD(L_TRIP);
1396 FETCH TRIP_SD INTO L_TRIP_START;
1397 CLOSE TRIP_SD;
1398 open c_task_number(l_task);
1399 fetch c_task_number into l_task_number;
1400 close c_task_number;
1401 open c_task_status(g_status);
1402 fetch c_task_status into l_task_name;
1403 close c_task_status;
1404 fnd_message.set_name('CSF','CSF_CROSSTASK_VALIDATION');
1405 fnd_message.set_token('TASK_NUMBER',l_task_number);
1406 fnd_message.set_token('TASK_ASSIGNMENT_STATUS',l_task_name);
1407 --fnd_message.set_token('TRIP_START_DATE',TO_CHAR(L_TRIP_START,'DD/MM/YYYY HH24:MI:SS'));
1408 fnd_msg_pub.add;
1409 raise fnd_api.g_exc_error;
1410 END IF;
1411 END IF;
1412 END LOOP;
1413 -- Update the Task Assignment.
1414
1415
1416 -- Update the Task Assignment.
1417 jtf_task_assignments_pub.update_task_assignment(
1418 p_api_version => 1.0
1419 , x_return_status => x_return_status
1420 , x_msg_count => x_msg_count
1421 , x_msg_data => x_msg_data
1422 , p_object_version_number => p_object_version_number
1423 , p_task_assignment_id => p_task_assignment_id
1424 , p_assignment_status_id => p_assignment_status_id
1425 , p_sched_travel_distance => l_distance
1426 , p_sched_travel_duration => l_duration
1427 , p_sched_travel_duration_uom => l_duration_uom
1428 , p_object_capacity_id => l_trip_id
1429 , p_show_on_calendar => p_show_on_calendar
1430 , p_category_id => NULL
1431 , p_enable_workflow => fnd_api.g_false
1432 , p_abort_workflow => fnd_api.g_false
1433 );
1434
1435 IF x_return_status = fnd_api.g_ret_sts_error THEN
1436 RAISE fnd_api.g_exc_error;
1437 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1438 RAISE fnd_api.g_exc_unexpected_error;
1439 END IF;
1440
1441 -- Propagate the changes to Task, Parent Task, Child Tasks, Spares, etc.
1442 propagate_status_change(
1443 x_return_status => x_return_status
1444 , x_msg_count => x_msg_count
1445 , x_msg_data => x_msg_data
1446 , p_task_assignment_id => p_task_assignment_id
1447 , p_object_version_number => p_object_version_number
1448 , p_new_assignment_status_id => p_assignment_status_id
1449 , p_update_task => p_update_task
1450 , p_new_sts_cancelled_flag => l_new_sts_cancelled_flag
1451 , x_task_object_version_number => x_task_object_version_number
1452 , x_task_status_id => x_task_status_id
1453 );
1454
1455 IF x_return_status = fnd_api.g_ret_sts_error THEN
1456 RAISE fnd_api.g_exc_error;
1457 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1458 RAISE fnd_api.g_exc_unexpected_error;
1459 END IF;
1460
1461
1462 IF fnd_api.to_boolean(p_commit) THEN
1463 COMMIT WORK;
1464 END IF;
1465
1466 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1467 EXCEPTION
1468 WHEN fnd_api.g_exc_error THEN
1469 ROLLBACK TO csf_update_assign_status_pub;
1470 x_return_status := fnd_api.g_ret_sts_error;
1471 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1472 WHEN fnd_api.g_exc_unexpected_error THEN
1473 ROLLBACK TO csf_update_assign_status_pub;
1474 x_return_status := fnd_api.g_ret_sts_unexp_error;
1475 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1476 WHEN OTHERS THEN
1477 x_return_status := fnd_api.g_ret_sts_unexp_error;
1478 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1479 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1480 END IF;
1481 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1482 ROLLBACK TO csf_update_assign_status_pub;
1483 END update_assignment_status;
1484
1485 FUNCTION cross_task_val(p_task_assignment_id NUMBER,p_assignment_status NUMBER,p_task out NOCOPY NUMBER)
1486 RETURN VARCHAR2
1487 IS
1488 CURSOR c_trip_info(p_task_assignment_id number)
1489 IS
1490 SELECT jtb.task_id ,
1491 jtb.customer_id ,
1492 jtb.address_id ,
1493 cac.object_capacity_id,
1494 cac.start_date_time ,
1495 cac.end_date_time
1496 FROM jtf_tasks_b jtb ,
1497 jtf_task_assignments jta,
1498 cac_sr_object_capacity cac
1499 WHERE jta.task_assignment_id = p_task_assignment_id
1500 AND jta.task_id = jtb.task_id
1501 AND cac.object_capacity_id (+) = jta.object_capacity_id;
1502
1503 CURSOR c_task_info(p_trip number,p_task number)
1504 IS
1505 SELECT b.task_id ,
1506 a.task_assignment_id,
1507 a.assignment_status_id
1508 FROM jtf_task_assignments a ,
1509 jtf_tasks_b b
1510 WHERE object_capacity_id = p_trip
1511 AND a.task_id =b.task_id
1512 AND b.task_id NOT IN (p_task)
1513 AND b.task_type_id NOT IN (20,21);
1514
1515 CURSOR c_site_info(p_task number)
1516 IS
1517 SELECT task_id,customer_id,address_id
1518 from jtf_tasks_b
1519 where task_id = p_task;
1520
1521 CURSOR c_working
1522 IS
1523 select task_status_id from jtf_task_statuses_b
1524 where assigned_flag = 'Y'
1525 and working_flag = 'Y'
1526 and seeded_flag = 'Y'
1527 and nvl(approved_flag,'N') = 'N'
1528 and task_status_flag = 'Y'
1529 and assignment_status_flag = 'Y';
1530
1531 CURSOR c_travel
1532 IS
1533 select task_status_id from jtf_task_statuses_b
1534 where travel_flag = 'Y'
1535 and seeded_flag= 'Y';
1536
1537 CURSOR c_task_geometry(p_task_id number) IS
1538 SELECT l.geometry
1539 FROM jtf_tasks_b t, hz_locations l
1540 WHERE t.task_id = p_task_id
1541 AND l.location_id = csf_tasks_pub.get_task_location_id(t.task_id, t.address_id, t.location_id);
1542
1543
1544 c_trip_rec c_trip_info%rowtype;
1545 l_task_id number;
1546 l_customer_id number;
1547 l_address_id number;
1548 l_work number;
1549 l_travel number;
1550 l_geometry MDSYS.SDO_GEOMETRY;
1551 t_geometry MDSYS.SDO_GEOMETRY;
1552 l_longitude NUMBER;
1553 l_latitude NUMBER;
1554 t_longitude NUMBER;
1555 t_latitude NUMBER;
1556 l_geocode VARCHAR2(1) := 'N';
1557 l_msg_count NUMBER;
1558 l_msg_data VARCHAR2(2000);
1559 l_return_status VARCHAR2(1);
1560 l_valid_geo VARCHAR2(5);
1561 l_api_name CONSTANT VARCHAR2(30) := 'cross_task_val';
1562
1563
1564
1565 a number := 0;
1566
1567 l_debug boolean := TRUE;
1568 BEGIN
1569 IF l_debug
1570 THEN
1571 debug('Input Parameters: p_task_assignment_id:'||p_task_assignment_id||',p_assignment_status: '||p_assignment_status, l_api_name, fnd_log.level_statement);
1572 END IF;
1573 open c_trip_info(p_task_assignment_id);
1574 fetch c_trip_info into c_trip_rec;
1575 close c_trip_info;
1576
1577 IF l_debug
1578 THEN
1579 debug('Task Id :'||c_trip_rec.task_id, l_api_name, fnd_log.level_statement);
1580 END IF;
1581 -- Fetch the Geocode information of the task whose status is going to be changed
1582 OPEN c_task_geometry(c_trip_rec.task_id);
1583 FETCH c_task_geometry INTO l_geometry;
1584 CLOSE c_task_geometry;
1585
1586 IF l_geometry IS NULL THEN
1587 IF l_debug
1588 THEN
1589 debug('l_geometry is null', l_api_name, fnd_log.level_statement);
1590 END IF;
1591 l_longitude := NULL;
1592 l_latitude := NULL;
1593 ELSE
1594 csf_locus_pub.verify_locus(
1595 p_api_version => 1.0
1596 , x_msg_count => l_msg_count
1597 , x_msg_data => l_msg_data
1598 , x_return_status => l_return_status
1599 , p_locus => l_geometry
1600 , x_result => l_valid_geo
1601 );
1602
1603 IF l_valid_geo = 'FALSE' THEN
1604 l_longitude := NULL;
1605 l_latitude := NULL;
1606 IF l_debug
1607 THEN
1608 debug('l_geometry is FALSE', l_api_name, fnd_log.level_statement);
1609 END IF;
1610 ELSE
1611 IF l_geometry.sdo_ordinates IS NOT NULL
1612 THEN
1613 l_longitude := l_geometry.sdo_ordinates(1);
1614 l_latitude := l_geometry.sdo_ordinates(2);
1615 IF l_debug
1616 THEN
1617 debug('Longitude : '||l_longitude, l_api_name, fnd_log.level_statement);
1618 debug('Latitude : '||l_latitude, l_api_name, fnd_log.level_statement);
1619 END IF;
1620 END IF;
1621 END IF;
1622 END IF;
1623 IF l_debug
1624 THEN
1625 debug('Object Capacity ID : '||c_trip_rec.object_capacity_id, l_api_name, fnd_log.level_statement);
1626 END IF;
1627 FOR i in c_task_info(c_trip_rec.object_capacity_id,c_trip_rec.task_id)
1628 LOOP
1629 IF l_debug
1630 THEN
1631 debug('TaskId in the trip : '||i.task_id, l_api_name, fnd_log.level_statement);
1632 END IF;
1633 open c_site_info(i.task_id);
1634 fetch c_site_info into l_task_id,l_customer_id,l_address_id;
1635 close c_site_info;
1636
1637 IF l_debug
1638 THEN
1639 debug('TaskId : '||i.task_id||', Customer id :'||l_customer_id||', Address Id: '||l_address_id, l_api_name, fnd_log.level_statement);
1640 END IF;
1641
1642 --Get Geo code of the task in the trip
1643 OPEN c_task_geometry(i.task_id);
1644 FETCH c_task_geometry INTO t_geometry;
1645 CLOSE c_task_geometry;
1646
1647 IF t_geometry IS NULL THEN
1648 t_longitude := NULL;
1649 t_latitude := NULL;
1650 IF l_debug
1651 THEN
1652 debug('t_geometry is NULL', l_api_name, fnd_log.level_statement);
1653 END IF;
1654 ELSE
1655 csf_locus_pub.verify_locus(
1656 p_api_version => 1.0
1657 , x_msg_count => l_msg_count
1658 , x_msg_data => l_msg_data
1659 , x_return_status => l_return_status
1660 , p_locus => t_geometry
1661 , x_result => l_valid_geo
1662 );
1663
1664
1665 IF l_valid_geo = 'FALSE' THEN
1666 t_longitude := NULL;
1667 t_latitude := NULL;
1668 IF l_debug
1669 THEN
1670 debug('l_valid_geo is false', l_api_name, fnd_log.level_statement);
1671 END IF;
1672 ELSE
1673 IF t_geometry.sdo_ordinates IS NOT NULL
1674 THEN
1675 t_longitude := t_geometry.sdo_ordinates(1);
1676 t_latitude := t_geometry.sdo_ordinates(2);
1677 IF l_debug
1678 THEN
1679 debug('Longitude of tasks in the trip: '||t_longitude, l_api_name, fnd_log.level_statement);
1680 debug('Latitude of tasks in the trip : '||t_latitude, l_api_name, fnd_log.level_statement);
1681 END IF;
1682 END IF;
1683 END IF;
1684 END IF;
1685
1686 OPEN c_working;
1687 FETCH c_working into l_work;
1688 CLOSE c_working;
1689
1690 IF l_debug
1691 THEN
1692 debug('Working status ID: '||l_work, l_api_name, fnd_log.level_statement);
1693 END IF;
1694
1695 OPEN c_travel;
1696 FETCH c_travel into l_travel;
1697 CLOSE c_travel;
1698
1699 IF l_debug
1700 THEN
1701 debug('Traveling status ID: '||l_travel, l_api_name, fnd_log.level_statement);
1702 END IF;
1703 IF p_assignment_status = l_work
1704 THEN
1705 IF l_debug
1706 THEN
1707 debug('assignment_status of the task: '||i.assignment_status_id, l_api_name, fnd_log.level_statement);
1708 END IF;
1709 IF i.assignment_status_id in (l_travel,l_work)
1710 THEN
1711 IF l_debug
1712 THEN
1713 debug('Already there is task with status Working or Traveling so return false ', l_api_name, fnd_log.level_statement);
1714 debug('Out parameter p_task:'||i.task_id, l_api_name, fnd_log.level_statement);
1715 END IF;
1716 p_task := i.task_id;
1717 g_status := i.assignment_status_id;
1718 RETURN 'FALSE';
1719 END IF;
1720 ELSIF p_assignment_status = l_travel
1721 THEN
1722 IF l_debug
1723 THEN
1724 debug('assignment_status of the task: '||i.assignment_status_id, l_api_name, fnd_log.level_statement);
1725 END IF;
1726 IF i.assignment_status_id = l_work
1727 THEN
1728 IF l_debug
1729 THEN
1730 debug('Already there is task with status Working or Traveling so return false ', l_api_name, fnd_log.level_statement);
1731 debug('Out parameter p_task:'||i.task_id, l_api_name, fnd_log.level_statement);
1732 END IF;
1733 p_task := i.task_id;
1734 g_status := i.assignment_status_id;
1735 RETURN 'FALSE';
1736 END IF;
1737 END IF;
1738
1739 IF i.assignment_status_id = p_assignment_status
1740 THEN
1741 IF l_debug
1742 THEN
1743 debug('Customer ID:'||c_trip_rec.customer_id||', Address ID: '||c_trip_rec.address_id, l_api_name, fnd_log.level_statement);
1744 END IF;
1745
1746 IF c_trip_rec.customer_id = l_customer_id and c_trip_rec.address_id = l_address_id
1747 THEN
1748 IF l_debug
1749 THEN
1750 debug('Already there is task with status Working or Traveling so return false', l_api_name, fnd_log.level_statement);
1751 END IF;
1752 RETURN 'TRUE';
1753 ELSE
1754 l_geocode := 'Y';
1755 END IF;
1756
1757 IF l_geocode = 'Y'
1758 THEN
1759 IF l_longitude = t_longitude and l_latitude = t_latitude
1760 THEN
1761 IF l_debug
1762 THEN
1763 debug('G-code of the tasks are equal', l_api_name, fnd_log.level_statement);
1764 END IF;
1765 RETURN 'TRUE';
1766 ELSE
1767 IF l_debug
1768 THEN
1769 debug('G-code of the tasks are not equal', l_api_name, fnd_log.level_statement);
1770 debug('Out parameter p_task:'||i.task_id, l_api_name, fnd_log.level_statement);
1771 END IF;
1772 p_task := i.task_id;
1773 g_status := i.assignment_status_id;
1774 RETURN 'FALSE';
1775 END IF;
1776 END IF;
1777 END IF;
1778 END LOOP;
1779 IF c_task_info%isopen
1780 THEN
1781 close c_task_info;
1782 end IF;
1783 RETURN 'TRUE';
1784 EXCEPTION
1785 WHEN others THEN
1786 IF l_debug
1787 THEN
1788 debug('Exception encountered: '||substr(sqlerrm,1,200), l_api_name, fnd_log.level_statement);
1789 END IF;
1790 IF c_task_info%isopen
1791 THEN
1792 close c_task_info;
1793 END IF;
1794 debug('Out parameter p_task:'||p_task, l_api_name, fnd_log.level_statement);
1795 RETURN 'TRUE';
1796
1797 END;
1798
1799
1800 PROCEDURE cross_task_validation (x_return_status out nocopy varchar2)
1801 IS
1802 l_task_assignment_id NUMBER;
1803 l_assignment_status NUMBER;
1804 l_task NUMBER;
1805 l_task_update VARCHAR2(10);
1806 l_task_id NUMBER;
1807 l_task_number NUMBER;
1808 l_task_name VARCHAR2(200);
1809 l_api_name constant varchar2(30) := 'CROSS_TASK_VALIDATION';
1810 CURSOR c_cross_task(p_sched_start_date date, p_sched_end_date date)
1811 IS
1812 select jtB.TASK_status_id,jtb.validation_start_date,jtb.validation_end_date
1813 from jtf_task_statuses_b jtb
1814 where jtb.enforce_validation_flag = 'Y'
1815 and nvl(jtb.validation_start_date,nvl(trunc(p_sched_start_date),sysdate)) <= nvl(trunc(p_sched_start_date),sysdate)
1816 and nvl(jtb.validation_end_date,nvl(trunc(p_sched_end_date),sysdate)) >= nvl(trunc(p_sched_end_date),sysdate);
1817
1818
1819 cursor c_scheduled_dates(p_task_id number)
1820 IS
1821 select scheduled_start_date, scheduled_end_date
1822 from jtf_tasks_b
1823 where task_id =p_task_id
1824 and task_type_id not in (20,21)
1825 and nvl(deleted_flag,'N')<>'Y';
1826
1827 cursor c_Task_number(l_task number)
1828 is
1829 select task_number
1830 from jtf_tasks_b
1831 where task_id=l_task;
1832
1833 cursor c_task_status(l_task number)
1834 is
1835 select name
1836 from jtf_task_statuses_tl jl
1837 where jl.task_status_id=l_task
1838 and language=userenv('lang');
1839
1840 scheduled_dates c_scheduled_dates%rowtype;
1841
1842 BEGIN
1843
1844 l_task_id := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.task_id;
1845 l_task_assignment_id := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.task_assignment_id;
1846 l_assignment_status := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.assignment_status_id;
1847
1848 open c_scheduled_dates(l_task_id);
1849 fetch c_scheduled_dates into scheduled_dates;
1850 IF c_scheduled_dates%FOUND then
1851
1852
1853 FOR i IN c_cross_task(scheduled_dates.scheduled_start_date,scheduled_dates.scheduled_end_date)
1854 LOOP
1855
1856 IF i.task_status_id = l_assignment_status
1857 THEN
1858 l_task_update := cross_task_val(l_task_assignment_id,l_assignment_status,l_task);
1859 IF l_task_update = 'FALSE'
1860 THEN
1861 open c_task_number(l_task);
1862 fetch c_task_number into l_task_number;
1863 close c_task_number;
1864 open c_task_status(g_status);
1865 fetch c_task_status into l_task_name;
1866 close c_task_status;
1867 fnd_message.set_name('CSF','CSF_CROSSTASK_VALIDATION');
1868 fnd_message.set_token('TASK_NUMBER',l_task_number);
1869 fnd_message.set_token('TASK_ASSIGNMENT_STATUS',l_task_name);
1870 --fnd_message.set_token('TRIP_START_DATE',TO_CHAR(L_TRIP_START,'DD/MM/YYYY HH24:MI:SS'));
1871 fnd_msg_pub.add;
1872 raise fnd_api.g_exc_error;
1873 END IF;
1874 END IF;
1875 END LOOP;
1876 END IF;
1877 close c_scheduled_dates;
1878 x_return_status := fnd_api.g_ret_sts_success;
1879 EXCEPTION
1880 WHEN others THEN
1881 x_return_status := fnd_api.g_ret_sts_unexp_error;
1882 IF fnd_msg_pub.check_msg_level ( fnd_msg_pub.g_msg_lvl_unexp_error )
1883 THEN
1884 fnd_msg_pub.add_exc_msg ( g_pkg_name, l_api_name );
1885 END IF;
1886
1887 END;
1888
1889 END csf_task_assignments_pub;
1890
1891
[Home] [Help]