[Home] [Help]
PACKAGE BODY: APPS.AHL_UA_COMMON_PVT
Source
1 PACKAGE BODY AHL_UA_COMMON_PVT AS
2 /* $Header: AHLVUACB.pls 120.1 2006/06/15 10:03:43 priyan noship $ */
3
4 /* Constants to define event type */
5 G_EVENT_TYPE_VISIT CONSTANT VARCHAR2(12) := 'VISIT';
6 G_EVENT_TYPE_FLIGHT CONSTANT VARCHAR2(12) := 'FLIGHT';
7
8 -------------------
9 -- Common variables
10 -------------------
11 l_dummy_varchar VARCHAR2(1);
12 l_dummy_number NUMBER;
13
14
15 -----------------------------------
16 -- Spec Procedure Get_All_Events --
17 -----------------------------------
18 PROCEDURE Get_All_Events
19 (
20 p_api_version IN NUMBER,
21 x_return_status OUT NOCOPY VARCHAR2,
22 x_msg_count OUT NOCOPY NUMBER,
23 x_msg_data OUT NOCOPY VARCHAR2,
24 p_unit_config_id IN NUMBER,
25 p_start_date_time IN DATE,
26 p_end_date_time IN DATE,
27 p_use_actuals IN VARCHAR2,
28 x_event_schedules OUT NOCOPY Event_Schedule_Tbl_Type
29 )
30 IS
31 -- 1. Declare local variables
32 l_api_name CONSTANT VARCHAR2(30) := 'Get_All_Events';
33 l_api_version CONSTANT NUMBER := 1.0;
34 l_return_status VARCHAR2(1);
35 l_msg_count NUMBER;
36 l_msg_data VARCHAR2(2000);
37 L_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
38
39 l_event_id NUMBER;
40 l_event_type VARCHAR2(10);
41 l_event_start_time DATE;
42 l_event_end_time DATE;
43 l_Event_Schedule_Rec Event_Schedule_Rec_Type;
44 l_tbl_idx NUMBER := 0;
45
46 -- 3. Define cursor get_event_details_act to retrieve unit flight information for a particular unit configuration for a time period from start time to end time (using actual times if available)
47 cursor get_event_details_act
48 (
49 p_unit_config_id number,
50 p_start_time date,
51 p_end_time date
52 )
53 is
54 SELECT
55 events.event_id,
56 events.event_type,
57 events.event_start_time,
58 events.event_end_time
59 FROM
60 (
61 select
62 unit_schedule_id event_id,
63 G_EVENT_TYPE_FLIGHT event_type,
64 nvl(actual_departure_time, est_departure_time) event_start_time,
65 nvl(actual_arrival_time, est_arrival_time) event_end_time
66 from ahl_unit_schedules
67 where unit_config_header_id = p_unit_config_id
68 and
69 (
70 (
71 p_start_time <= nvl(actual_departure_time, est_departure_time) and
72 nvl(actual_departure_time, est_departure_time) < p_end_time
73 )
74 or
75 (
76 p_start_time < nvl(actual_arrival_time, est_arrival_time) and
77 nvl(actual_arrival_time, est_arrival_time) <= p_end_time
78 )
79 or
80 (
81 nvl(actual_departure_time, est_departure_time) <= p_start_time and
82 p_end_time <= nvl(actual_arrival_time, est_arrival_time)
83 )
84 )
85 UNION ALL
86 select
87 vwp.visit_id event_id,
88 G_EVENT_TYPE_VISIT event_type,
89 vwp.start_date_time event_start_time,
90 AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(vwp.VISIT_ID , FND_API.G_FALSE) event_end_time
91 from ahl_visits_b vwp, ahl_unit_config_headers uc
92 where uc.unit_config_header_id = p_unit_config_id
93 and vwp.item_instance_id = uc.csi_item_instance_id
94 --priyan Bug # 5303188
95 and ahl_util_uc_pkg.get_uc_status_code (p_unit_config_id) IN ('COMPLETE', 'INCOMPLETE')
96 --and uc.unit_config_status_code IN ('COMPLETE', 'INCOMPLETE')
97 and vwp.status_code NOT IN ('CANCELLED', 'DELETED')
98 and vwp.start_date_time IS NOT NULL
99 and
100 (
101 (
102 p_start_time <= vwp.start_date_time and
103 vwp.start_date_time < p_end_time
104 )
105 or
106 (
107 p_start_time < nvl(AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(vwp.VISIT_ID , FND_API.G_FALSE), vwp.start_date_time + 1/1440 ) and
108 nvl(AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(vwp.VISIT_ID , FND_API.G_FALSE), vwp.start_date_time + 1/1440 ) <= p_end_time
109 )
110 or
111 (
112 vwp.start_date_time <= p_start_time and
113 p_end_time <= nvl(AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(vwp.VISIT_ID , FND_API.G_FALSE), vwp.start_date_time + 1/1440 )
114 )
115 )
116 ) events
117 order by event_start_time asc, NVL(event_end_time, event_start_time + 1/1440) asc, event_type desc;
118
119 -- 4. Define cursor get_event_details_est to retrieve unit flight information for a particular unit configuration for a time period from start time to end time (without using any actual times)
120 cursor get_event_details_est
121 (
122 p_unit_config_id number,
123 p_start_time date,
124 p_end_time date
125 )
126 is
127 SELECT
128 event_id,
129 event_type,
130 event_start_time,
131 event_end_time
132 FROM
133 (
134 select
135 unit_schedule_id event_id,
136 G_EVENT_TYPE_FLIGHT event_type,
137 est_departure_time event_start_time,
138 est_arrival_time event_end_time
139 from ahl_unit_schedules
140 where unit_config_header_id = p_unit_config_id
141 and
142 (
143 (
144 p_start_time <= est_departure_time and
145 est_departure_time < p_end_time
146 )
147 or
148 (
149 p_start_time < est_arrival_time and
150 est_arrival_time <= p_end_time
151 )
152 or
153 (
154 est_departure_time <= p_start_time and
155 p_end_time <= est_arrival_time
156 )
157 )
158 UNION ALL
159 select
160 vwp.visit_id event_id,
161 G_EVENT_TYPE_VISIT event_type,
162 vwp.start_date_time event_start_time,
163 AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(vwp.VISIT_ID , FND_API.G_FALSE) event_end_time
164 from ahl_visits_b vwp, ahl_unit_config_headers uc
165 where uc.unit_config_header_id = p_unit_config_id
166 and vwp.item_instance_id = uc.csi_item_instance_id
167 --priyan Bug # 5303188
168 and ahl_util_uc_pkg.get_uc_status_code (p_unit_config_id) IN ('COMPLETE', 'INCOMPLETE')
169 --and uc.unit_config_status_code IN ('COMPLETE', 'INCOMPLETE')
170 and vwp.status_code NOT IN ('CANCELLED', 'DELETED')
171 and vwp.start_date_time IS NOT NULL
172 and
173 (
174 (
175 p_start_time <= vwp.start_date_time and
176 vwp.start_date_time < p_end_time
177 )
178 or
179 (
180 p_start_time < nvl(AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(vwp.VISIT_ID , FND_API.G_FALSE), vwp.start_date_time + 1/1440 ) and
181 nvl(AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(vwp.VISIT_ID , FND_API.G_FALSE), vwp.start_date_time + 1/1440 ) <= p_end_time
182 )
183 or
184 (
185 vwp.start_date_time <= p_start_time and
186 p_end_time <= nvl(AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(vwp.VISIT_ID , FND_API.G_FALSE), vwp.start_date_time + 1/1440 )
187 )
188 )
189 )
190 order by event_start_time asc, NVL(event_end_time, event_start_time + 1/1440) asc, event_type desc;
191
192 BEGIN
193 -- Standard call to check for call compatibility
194 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
195 THEN
196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
197 END IF;
198
199 -- Initialize API return status to success
200 x_return_status := FND_API.G_RET_STS_SUCCESS;
201
202 -- Log API entry point
203 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
204 THEN
205 fnd_log.string
206 (
207 fnd_log.level_procedure,
208 L_DEBUG_MODULE||'.begin',
209 'At the start of PLSQL procedure'
210 );
211 END IF;
212
213 -- API body starts here
214 -- 5. If (p_unit_config_id is null or p_start_date_time is null or p_end_date_time is null), then display error "Unit Configuration Id and Start Time are mandatory parameters"
215 IF (
216 p_unit_config_id IS NULL OR p_unit_config_id = FND_API.G_MISS_NUM OR
217 p_start_date_time IS NULL OR p_start_date_time = FND_API.G_MISS_DATE OR
218 p_end_date_time IS NULL OR p_end_date_time = FND_API.G_MISS_DATE
219 )
220 THEN
221 FND_MESSAGE.SET_NAME('AHL', 'AHL_COM_INVALID_PROCEDURE_CALL');
222 FND_MESSAGE.SET_TOKEN('PROCEDURE', l_api_name);
223 FND_MSG_PUB.ADD;
224 RAISE FND_API.G_EXC_ERROR;
225 END IF;
226
227 /*
228 -- 6. Validate unit configuration with unit_config_id = p_unit_config_id exists and is complete and active, using cursor check_unit_exists. If no record found, throw error "Unit Configuration does not exist or is not complete"
229 OPEN check_unit_exists (p_unit_config_id);
230 FETCH check_unit_exists INTO l_dummy_varchar;
231 IF (check_unit_exists%NOTFOUND)
232 THEN
233 FND_MESSAGE.SET_NAME('AHL', 'AHL_UA_UNIT_ID_NOT_FOUND');
234 FND_MSG_PUB.ADD;
235 CLOSE check_unit_exists;
236 RAISE FND_API.G_EXC_ERROR;
237 END IF;
238 CLOSE check_unit_exists;
239 */
240
241 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
242 THEN
243 fnd_log.string
244 (
245 fnd_log.level_statement,
246 L_DEBUG_MODULE,
247 'Basic validations done'
248 );
249 END IF;
250
251 -- 7. If (p_use_actuals = 'T'), open cursor get_prec_flight_act else open cursor get_prec_flight_est
252 IF (p_use_actuals IS NOT NULL AND p_use_actuals = FND_API.G_TRUE)
253 THEN
254 OPEN get_event_details_act (p_unit_config_id, p_start_date_time, p_end_date_time);
255 LOOP
256 FETCH get_event_details_act INTO l_event_id, l_event_type, l_event_start_time, l_event_end_time;
257 EXIT WHEN get_event_details_act%NOTFOUND;
258
259 l_Event_Schedule_Rec.event_id := l_event_id;
260 l_Event_Schedule_Rec.event_type := l_event_type;
261 l_Event_Schedule_Rec.event_start_time := l_event_start_time;
262 l_Event_Schedule_Rec.event_end_time := l_event_end_time;
263
264 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
265 THEN
266 fnd_log.string
267 (
268 fnd_log.level_statement,
269 L_DEBUG_MODULE,
270 'adding event_id [' || l_event_id || '] ' ||
271 'event_type [' || l_event_type || '] ' ||
272 'actual event_start_time [' || to_char(l_event_start_time,'MM-DD-YYYY HH24:MI:SS') || '] ' ||
273 'actual event_end_time [' || to_char(l_event_end_time,'MM-DD-YYYY HH24:MI:SS') || ']'
274 );
275 END IF;
276
277 l_tbl_idx := l_tbl_idx + 1;
278
279 x_event_schedules(l_tbl_idx) := l_Event_Schedule_Rec;
280 END LOOP;
281 CLOSE get_event_details_act;
282 ELSE
283 OPEN get_event_details_est (p_unit_config_id, p_start_date_time, p_end_date_time);
284 LOOP
285 FETCH get_event_details_est INTO l_event_id, l_event_type, l_event_start_time, l_event_end_time;
286 EXIT WHEN get_event_details_est%NOTFOUND;
287
288 l_Event_Schedule_Rec.event_id := l_event_id;
289 l_Event_Schedule_Rec.event_type := l_event_type;
290 l_Event_Schedule_Rec.event_start_time := l_event_start_time;
291 l_Event_Schedule_Rec.event_end_time := l_event_end_time;
292
293 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
294 THEN
295 fnd_log.string
296 (
297 fnd_log.level_statement,
298 L_DEBUG_MODULE,
299 'adding event_id [' || l_event_id || '] ' ||
300 'event_type [' || l_event_type || '] ' ||
301 'est event_start_time [' || to_char(l_event_start_time,'MM-DD-YYYY HH24:MI:SS') || '] ' ||
302 'est event_end_time [' || to_char(l_event_end_time,'MM-DD-YYYY HH24:MI:SS') || ']'
303 );
304 END IF;
305
306 l_tbl_idx := l_tbl_idx + 1;
307
308 x_event_schedules(l_tbl_idx) := l_Event_Schedule_Rec;
309 END LOOP;
310 CLOSE get_event_details_est;
311 END IF;
312 -- API body ends here
313
314 -- Log API exit point
315 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
316 THEN
317 fnd_log.string
318 (
319 fnd_log.level_procedure,
320 L_DEBUG_MODULE||'.end',
321 'At the end of PLSQL procedure'
322 );
323 END IF;
324
325 -- Check Error Message stack.
326 x_msg_count := FND_MSG_PUB.count_msg;
327 IF x_msg_count > 0
328 THEN
329 RAISE FND_API.G_EXC_ERROR;
330 END IF;
331
332 -- Standard call to get message count and if count is 1, get message info
333 FND_MSG_PUB.count_and_get
334 (
335 p_count => x_msg_count,
336 p_data => x_msg_data,
337 p_encoded => FND_API.G_FALSE
338 );
339
340 EXCEPTION
341 WHEN FND_API.G_EXC_ERROR THEN
342 x_return_status := FND_API.G_RET_STS_ERROR;
343 FND_MSG_PUB.count_and_get
344 (
345 p_count => x_msg_count,
346 p_data => x_msg_data,
347 p_encoded => FND_API.G_FALSE
348 );
349
350 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
351 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
352 FND_MSG_PUB.count_and_get
353 (
354 p_count => x_msg_count,
355 p_data => x_msg_data,
356 p_encoded => FND_API.G_FALSE
357 );
358
359 WHEN OTHERS THEN
360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
361 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
362 THEN
363 FND_MSG_PUB.add_exc_msg
364 (
365 p_pkg_name => G_PKG_NAME,
366 p_procedure_name => 'Get_All_Events',
367 p_error_text => SUBSTR(SQLERRM,1,240)
368 );
369 END IF;
370 FND_MSG_PUB.count_and_get
371 (
372 p_count => x_msg_count,
373 p_data => x_msg_data,
374 p_encoded => FND_API.G_FALSE
375 );
376 END Get_All_Events;
377
378 -----------------------------------------
379 -- Spec Procedure Get_Prec_Flight_Info --
380 -----------------------------------------
384 x_return_status OUT NOCOPY VARCHAR2,
381 PROCEDURE Get_Prec_Flight_Info
382 (
383 p_api_version IN NUMBER,
385 x_msg_count OUT NOCOPY NUMBER,
386 x_msg_data OUT NOCOPY VARCHAR2,
387 p_unit_config_id IN NUMBER,
388 p_start_date_time IN DATE,
389 p_use_actuals IN VARCHAR2,
390 x_prec_flight_schedule OUT NOCOPY AHL_UA_FLIGHT_SCHEDULES_PVT.Flight_Schedule_Rec_Type,
391 x_is_conflict OUT NOCOPY VARCHAR2
392 )
393 IS
394 -- 1. Declare local variables
395 l_api_name CONSTANT VARCHAR2(30) := 'Get_Prec_Flight_Info';
396 l_api_version CONSTANT NUMBER := 1.0;
397 l_return_status VARCHAR2(1);
398 l_msg_count NUMBER;
399 l_msg_data VARCHAR2(2000);
400 L_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
401
402 -- 2. Define cursor get_prec_flight_act to retrieve all flights for which the arrival time is before or equal to start time (using actual times if available)
403 cursor get_prec_flight_act
404 (
405 p_unit_config_id number,
406 p_start_time date
407 )
408 is
409 select *
410 from ahl_unit_schedules_v
411 where unit_config_header_id = p_unit_config_id
412 --Modifying to capture preceding overlapping flights too...
413 --and nvl(actual_arrival_time, est_arrival_time) <= p_start_time
414 --order by nvl(actual_arrival_time, est_arrival_time) desc;
415 and nvl(actual_departure_time, est_departure_time) < p_start_time
416 order by nvl(actual_departure_time, est_departure_time) desc, nvl(actual_arrival_time, est_arrival_time) desc;
417
418 -- 3. Define cursor get_prec_flight_est to retrieve all flights for which the arrival time is before or equal to start time (without using actual times)
419 cursor get_prec_flight_est
420 (
421 p_unit_config_id number,
422 p_start_time date
423 )
424 is
425 select *
426 from ahl_unit_schedules_v
427 where unit_config_header_id = p_unit_config_id
428 --Modifying to capture preceding overlapping flights too...
429 --and est_arrival_time <= p_start_time
430 --order by est_arrival_time desc;
431 and est_departure_time < p_start_time
432 order by est_departure_time desc, est_arrival_time desc;
433
434 l_act_flight_rec get_prec_flight_act%rowtype;
435 l_est_flight_rec get_prec_flight_est%rowtype;
436
437 BEGIN
438 -- Standard call to check for call compatibility
439 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
440 THEN
441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
442 END IF;
443
444 -- Initialize API return status to success
445 x_return_status := FND_API.G_RET_STS_SUCCESS;
446
447 -- Log API entry point
448 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
449 THEN
450 fnd_log.string
451 (
452 fnd_log.level_procedure,
453 L_DEBUG_MODULE||'.begin',
454 'At the start of PLSQL procedure'
455 );
456 END IF;
457
458 -- API body starts here
459 -- 4. If (p_unit_config_id is null or p_start_date_time is null), then display error "Unit Configuration Id and Start Time are mandatory parameters"
460 IF (
461 p_unit_config_id IS NULL OR p_unit_config_id = FND_API.G_MISS_NUM OR
462 p_start_date_time IS NULL OR p_start_date_time = FND_API.G_MISS_DATE
463 )
464 THEN
465 FND_MESSAGE.SET_NAME('AHL', 'AHL_COM_INVALID_PROCEDURE_CALL');
466 FND_MESSAGE.SET_TOKEN('PROCEDURE', l_api_name);
467 FND_MSG_PUB.ADD;
468 RAISE FND_API.G_EXC_ERROR;
469 END IF;
470
471 /*
472 -- 5. Validate unit configuration with unit_config_id = p_unit_config_id exists and is complete and active, using cursor check_unit_exists. If no record found, throw error "Unit Configuration does not exist or is not complete"
473 OPEN check_unit_exists (p_unit_config_id);
474 FETCH check_unit_exists INTO l_dummy_varchar;
475 IF (check_unit_exists%NOTFOUND)
476 THEN
477 FND_MESSAGE.SET_NAME('AHL', 'AHL_UA_UNIT_ID_NOT_FOUND');
478 FND_MSG_PUB.ADD;
479 CLOSE check_unit_exists;
480 RAISE FND_API.G_EXC_ERROR;
481 END IF;
482 CLOSE check_unit_exists;
483 */
484
485 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
486 THEN
487 fnd_log.string
488 (
489 fnd_log.level_statement,
490 L_DEBUG_MODULE,
491 'Basic validations done'
492 );
493 END IF;
494
495 x_is_conflict := FND_API.G_FALSE;
496
497 -- 6. If (p_use_actuals = FND_API.G_TRUE), open cursor get_prec_flight_act else open cursor get_prec_flight_est
498 IF (p_use_actuals IS NOT NULL AND p_use_actuals = FND_API.G_TRUE)
499 THEN
500 -- 7. Fetch one record from cursor into and populate x_Flight_Schedule_Rec_Type with values from this record
501 OPEN get_prec_flight_act (p_unit_config_id, p_start_date_time);
502 FETCH get_prec_flight_act INTO l_act_flight_rec;
503 IF (get_prec_flight_act%FOUND)
504 THEN
505 x_prec_flight_schedule.unit_schedule_id := l_act_flight_rec.unit_schedule_id;
506 x_prec_flight_schedule.flight_number := l_act_flight_rec.flight_number;
507 x_prec_flight_schedule.segment := l_act_flight_rec.segment;
508 x_prec_flight_schedule.est_departure_time := l_act_flight_rec.est_departure_time;
509 x_prec_flight_schedule.actual_departure_time := l_act_flight_rec.actual_departure_time;
510 x_prec_flight_schedule.departure_dept_id := l_act_flight_rec.departure_dept_id;
511 x_prec_flight_schedule.departure_dept_code := l_act_flight_rec.departure_dept_code;
512 x_prec_flight_schedule.departure_org_id := l_act_flight_rec.departure_org_id;
513 x_prec_flight_schedule.departure_org_code := l_act_flight_rec.departure_org_code;
514 x_prec_flight_schedule.est_arrival_time := l_act_flight_rec.est_arrival_time;
515 x_prec_flight_schedule.actual_arrival_time := l_act_flight_rec.actual_arrival_time;
519 x_prec_flight_schedule.arrival_org_code := l_act_flight_rec.arrival_org_code;
516 x_prec_flight_schedule.arrival_dept_id := l_act_flight_rec.arrival_dept_id;
517 x_prec_flight_schedule.arrival_dept_code := l_act_flight_rec.arrival_dept_code;
518 x_prec_flight_schedule.arrival_org_id := l_act_flight_rec.arrival_org_id;
520 x_prec_flight_schedule.preceding_us_id := l_act_flight_rec.preceding_us_id;
521 x_prec_flight_schedule.unit_config_header_id := l_act_flight_rec.unit_config_header_id;
522 x_prec_flight_schedule.unit_config_name := l_act_flight_rec.unit_config_name;
523 x_prec_flight_schedule.csi_instance_id := l_act_flight_rec.csi_item_instance_id;
524 x_prec_flight_schedule.instance_number := l_act_flight_rec.instance_number;
525 x_prec_flight_schedule.item_number := l_act_flight_rec.item_number;
526 x_prec_flight_schedule.serial_number := l_act_flight_rec.serial_number;
527 x_prec_flight_schedule.visit_reschedule_mode := l_act_flight_rec.visit_reschedule_mode;
528 x_prec_flight_schedule.visit_reschedule_meaning := l_act_flight_rec.visit_reschedule_meaning;
529 x_prec_flight_schedule.object_version_number := l_act_flight_rec.object_version_number;
530 x_prec_flight_schedule.attribute_category := l_act_flight_rec.attribute_category;
531 x_prec_flight_schedule.attribute1 := l_act_flight_rec.attribute1;
532 x_prec_flight_schedule.attribute2 := l_act_flight_rec.attribute2;
533 x_prec_flight_schedule.attribute3 := l_act_flight_rec.attribute3;
534 x_prec_flight_schedule.attribute4 := l_act_flight_rec.attribute4;
535 x_prec_flight_schedule.attribute5 := l_act_flight_rec.attribute5;
536 x_prec_flight_schedule.attribute6 := l_act_flight_rec.attribute6;
537 x_prec_flight_schedule.attribute7 := l_act_flight_rec.attribute7;
538 x_prec_flight_schedule.attribute8 := l_act_flight_rec.attribute8;
539 x_prec_flight_schedule.attribute9 := l_act_flight_rec.attribute9;
540 x_prec_flight_schedule.attribute10 := l_act_flight_rec.attribute10;
541 x_prec_flight_schedule.attribute11 := l_act_flight_rec.attribute11;
542 x_prec_flight_schedule.attribute12 := l_act_flight_rec.attribute12;
543 x_prec_flight_schedule.attribute13 := l_act_flight_rec.attribute13;
544 x_prec_flight_schedule.attribute14 := l_act_flight_rec.attribute14;
545 x_prec_flight_schedule.attribute15 := l_act_flight_rec.attribute15;
546
547 -- 8. Fetch another record from cursor, and if the record's arrival time (actual / estimated based on p_use_actuals) = previous record's arrival time, then populate x_is_conflict = FND_API.G_TRUE
548 FETCH get_prec_flight_act INTO l_act_flight_rec;
549 IF (
550 get_prec_flight_act%FOUND
551 AND
552 --Modifying to capture preceding overlapping flights too...
553 --nvl(l_act_flight_rec.actual_arrival_time, l_act_flight_rec.est_arrival_time) =
554 --nvl(x_prec_flight_schedule.actual_arrival_time, x_prec_flight_schedule.est_arrival_time)
555 nvl(l_act_flight_rec.actual_departure_time, l_act_flight_rec.est_departure_time) =
556 nvl(x_prec_flight_schedule.actual_departure_time, x_prec_flight_schedule.est_departure_time)
557 -- If both start times and end time match, then conflict...
558 AND
559 nvl(l_act_flight_rec.actual_arrival_time, l_act_flight_rec.est_arrival_time) =
560 nvl(x_prec_flight_schedule.actual_arrival_time, x_prec_flight_schedule.est_arrival_time)
561 )
562 THEN
563 x_is_conflict := FND_API.G_TRUE;
564 END IF;
565 END IF;
566 CLOSE get_prec_flight_act;
567 ELSE
568 -- 7. Fetch one record from cursor into and populate x_Flight_Schedule_Rec_Type with values from this record
569 OPEN get_prec_flight_est (p_unit_config_id, p_start_date_time);
570 FETCH get_prec_flight_est INTO l_est_flight_rec;
571 IF (get_prec_flight_est%FOUND)
572 THEN
573 x_prec_flight_schedule.unit_schedule_id := l_est_flight_rec.unit_schedule_id;
574 x_prec_flight_schedule.flight_number := l_est_flight_rec.flight_number;
575 x_prec_flight_schedule.segment := l_est_flight_rec.segment;
576 x_prec_flight_schedule.est_departure_time := l_est_flight_rec.est_departure_time;
577 x_prec_flight_schedule.actual_departure_time := l_est_flight_rec.actual_departure_time;
578 x_prec_flight_schedule.departure_dept_id := l_est_flight_rec.departure_dept_id;
579 x_prec_flight_schedule.departure_dept_code := l_est_flight_rec.departure_dept_code;
580 x_prec_flight_schedule.departure_org_id := l_est_flight_rec.departure_org_id;
581 x_prec_flight_schedule.departure_org_code := l_est_flight_rec.departure_org_code;
582 x_prec_flight_schedule.est_arrival_time := l_est_flight_rec.est_arrival_time;
583 x_prec_flight_schedule.actual_arrival_time := l_est_flight_rec.actual_arrival_time;
584 x_prec_flight_schedule.arrival_dept_id := l_est_flight_rec.arrival_dept_id;
585 x_prec_flight_schedule.arrival_dept_code := l_est_flight_rec.arrival_dept_code;
586 x_prec_flight_schedule.arrival_org_id := l_est_flight_rec.arrival_org_id;
587 x_prec_flight_schedule.arrival_org_code := l_est_flight_rec.arrival_org_code;
588 x_prec_flight_schedule.preceding_us_id := l_est_flight_rec.preceding_us_id;
589 x_prec_flight_schedule.unit_config_header_id := l_est_flight_rec.unit_config_header_id;
590 x_prec_flight_schedule.unit_config_name := l_est_flight_rec.unit_config_name;
591 x_prec_flight_schedule.csi_instance_id := l_est_flight_rec.csi_item_instance_id;
592 x_prec_flight_schedule.instance_number := l_est_flight_rec.instance_number;
593 x_prec_flight_schedule.item_number := l_est_flight_rec.item_number;
594 x_prec_flight_schedule.serial_number := l_est_flight_rec.serial_number;
595 x_prec_flight_schedule.visit_reschedule_mode := l_est_flight_rec.visit_reschedule_mode;
596 x_prec_flight_schedule.visit_reschedule_meaning := l_est_flight_rec.visit_reschedule_meaning;
597 x_prec_flight_schedule.object_version_number := l_est_flight_rec.object_version_number;
598 x_prec_flight_schedule.attribute_category := l_est_flight_rec.attribute_category;
599 x_prec_flight_schedule.attribute1 := l_est_flight_rec.attribute1;
600 x_prec_flight_schedule.attribute2 := l_est_flight_rec.attribute2;
601 x_prec_flight_schedule.attribute3 := l_est_flight_rec.attribute3;
605 x_prec_flight_schedule.attribute7 := l_est_flight_rec.attribute7;
602 x_prec_flight_schedule.attribute4 := l_est_flight_rec.attribute4;
603 x_prec_flight_schedule.attribute5 := l_est_flight_rec.attribute5;
604 x_prec_flight_schedule.attribute6 := l_est_flight_rec.attribute6;
606 x_prec_flight_schedule.attribute8 := l_est_flight_rec.attribute8;
607 x_prec_flight_schedule.attribute9 := l_est_flight_rec.attribute9;
608 x_prec_flight_schedule.attribute10 := l_est_flight_rec.attribute10;
609 x_prec_flight_schedule.attribute11 := l_est_flight_rec.attribute11;
610 x_prec_flight_schedule.attribute12 := l_est_flight_rec.attribute12;
611 x_prec_flight_schedule.attribute13 := l_est_flight_rec.attribute13;
612 x_prec_flight_schedule.attribute14 := l_est_flight_rec.attribute14;
613 x_prec_flight_schedule.attribute15 := l_est_flight_rec.attribute15;
614
615 -- 8. Fetch another record from cursor, and if the record's arrival time (actual / estimated based on p_use_actuals) = previous record's arrival time, then populate x_is_conflict = FND_API.G_TRUE
616 FETCH get_prec_flight_est INTO l_est_flight_rec;
617 IF (
618 get_prec_flight_est%FOUND
619 AND
620 --Modifying to capture preceding overlapping flights too...
621 --l_est_flight_rec.est_arrival_time = x_prec_flight_schedule.est_arrival_time
622 l_est_flight_rec.est_departure_time = x_prec_flight_schedule.est_departure_time
623 -- If both start times and end time match, then conflict...
624 AND
625 l_est_flight_rec.est_arrival_time = x_prec_flight_schedule.est_arrival_time
626 )
627 THEN
628 x_is_conflict := FND_API.G_TRUE;
629 END IF;
630 END IF;
631 CLOSE get_prec_flight_est;
632 END IF;
633
634
635 -- API body ends here
636
637 -- Log API exit point
638 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
639 THEN
640 fnd_log.string
641 (
642 fnd_log.level_procedure,
643 L_DEBUG_MODULE||'.end',
644 'At the end of PLSQL procedure'
645 );
646 END IF;
647
648 -- Check Error Message stack.
649 x_msg_count := FND_MSG_PUB.count_msg;
650 IF x_msg_count > 0
651 THEN
652 RAISE FND_API.G_EXC_ERROR;
653 END IF;
654
655 -- Standard call to get message count and if count is 1, get message info
656 FND_MSG_PUB.count_and_get
657 (
658 p_count => x_msg_count,
659 p_data => x_msg_data,
660 p_encoded => FND_API.G_FALSE
661 );
662
663 EXCEPTION
664 WHEN FND_API.G_EXC_ERROR THEN
665 x_return_status := FND_API.G_RET_STS_ERROR;
666 FND_MSG_PUB.count_and_get
667 (
668 p_count => x_msg_count,
669 p_data => x_msg_data,
670 p_encoded => FND_API.G_FALSE
671 );
672
673 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
674 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
675 FND_MSG_PUB.count_and_get
676 (
677 p_count => x_msg_count,
678 p_data => x_msg_data,
679 p_encoded => FND_API.G_FALSE
680 );
681
682 WHEN OTHERS THEN
683 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
684 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
685 THEN
686 FND_MSG_PUB.add_exc_msg
687 (
688 p_pkg_name => G_PKG_NAME,
689 p_procedure_name => 'Get_Prec_Flight_Info',
690 p_error_text => SUBSTR(SQLERRM,1,240)
691 );
692 END IF;
693 FND_MSG_PUB.count_and_get
694 (
695 p_count => x_msg_count,
696 p_data => x_msg_data,
697 p_encoded => FND_API.G_FALSE
698 );
699 END Get_Prec_Flight_Info;
700
701 ----------------------------------------
702 -- Spec Procedure Get_Prec_Visit_Info --
703 ----------------------------------------
704 PROCEDURE Get_Prec_Visit_Info
705 (
706 p_api_version IN NUMBER,
707 x_return_status OUT NOCOPY VARCHAR2,
708 x_msg_count OUT NOCOPY NUMBER,
709 x_msg_data OUT NOCOPY VARCHAR2,
710 p_unit_config_id IN NUMBER,
711 p_start_date_time IN DATE,
712 x_prec_visit OUT NOCOPY AHL_VWP_VISITS_PVT.Visit_Rec_Type,
713 x_is_conflict OUT NOCOPY VARCHAR2
714 )
715 IS
716 -- 1. Declare local variables
717 l_api_name CONSTANT VARCHAR2(30) := 'Get_Prec_Visit_Info';
718 l_api_version CONSTANT NUMBER := 1.0;
719 l_return_status VARCHAR2(1);
720 l_msg_count NUMBER;
721 l_msg_data VARCHAR2(2000);
722 L_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
723
724 cursor get_prec_visit_rec
725 (
726 c_unit_config_id number,
727 c_start_time date
728 )
729 is
730 SELECT
731 VB.VISIT_ID,
732 VB.VISIT_NUMBER,
733 VB.OBJECT_VERSION_NUMBER,
734 VB.ORGANIZATION_ID,
735 HROU.NAME ORGANIZATION_NAME,
736 VB.DEPARTMENT_ID,
737 BDPT.DESCRIPTION DEPARTMENT_NAME,
738 VB.SERVICE_REQUEST_ID,
739 VB.START_DATE_TIME,
740 AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(VB.VISIT_ID , FND_API.G_FALSE) CLOSE_DATE_TIME,
741 VB.STATUS_CODE,
742 VB.VISIT_TYPE_CODE,
743 VB.PROJECT_FLAG,
744 VB.PROJECT_ID,
745 VB.PROJECT_TEMPLATE_ID,
746 VB.ATTRIBUTE_CATEGORY,
747 VB.ATTRIBUTE1,
748 VB.ATTRIBUTE2,
749 VB.ATTRIBUTE3,
750 VB.ATTRIBUTE4,
751 VB.ATTRIBUTE5,
752 VB.ATTRIBUTE6,
753 VB.ATTRIBUTE7,
754 VB.ATTRIBUTE8,
755 VB.ATTRIBUTE9,
756 VB.ATTRIBUTE10,
757 VB.ATTRIBUTE11,
758 VB.ATTRIBUTE12,
759 VB.ATTRIBUTE13,
760 VB.ATTRIBUTE14,
761 VB.ATTRIBUTE15,
762 VB.UNIT_SCHEDULE_ID
763 FROM AHL_VISITS_B VB,
764 AHL_UNIT_CONFIG_HEADERS UC,
765 HR_ALL_ORGANIZATION_UNITS HROU,
766 BOM_DEPARTMENTS BDPT
770 VB.ITEM_INSTANCE_ID = UC.CSI_ITEM_INSTANCE_ID AND
767 WHERE
768 VB.ORGANIZATION_ID = HROU.ORGANIZATION_ID(+) AND
769 VB.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+) AND
771 UC.UNIT_CONFIG_HEADER_ID = c_unit_config_id AND
772 VB.STATUS_CODE NOT IN ('CANCELLED', 'DELETED') AND
773 VB.START_DATE_TIME IS NOT NULL AND
774 --Modifying to capture preceding overlapping visits too...
775 --CLOSE_DATE_TIME <= c_start_time
776 --ORDER BY CLOSE_DATE_TIME DESC;
777 START_DATE_TIME < c_start_time
778 ORDER BY START_DATE_TIME DESC, NVL(CLOSE_DATE_TIME, START_DATE_TIME + 1/1440) DESC;
779
780 l_prec_visit get_prec_visit_rec%rowtype;
781
782 BEGIN
783 -- Standard call to check for call compatibility
784 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
785 THEN
786 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
787 END IF;
788
789 -- Initialize API return status to success
790 x_return_status := FND_API.G_RET_STS_SUCCESS;
791
792 -- Log API entry point
793 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
794 THEN
795 fnd_log.string
796 (
797 fnd_log.level_procedure,
798 L_DEBUG_MODULE||'.begin',
799 'At the start of PLSQL procedure'
800 );
801 END IF;
802
803 -- API body starts here
804 -- 4. If (p_unit_config_id is null or p_start_date_time is null), then display error "Unit Configuration Id and Start Time are mandatory parameters"
805 IF (
806 p_unit_config_id IS NULL OR p_unit_config_id = FND_API.G_MISS_NUM OR
807 p_start_date_time IS NULL OR p_start_date_time = FND_API.G_MISS_DATE
808 )
809 THEN
810 FND_MESSAGE.SET_NAME('AHL', 'AHL_COM_INVALID_PROCEDURE_CALL');
811 FND_MESSAGE.SET_TOKEN('PROCEDURE', l_api_name);
812 FND_MSG_PUB.ADD;
813 RAISE FND_API.G_EXC_ERROR;
814 END IF;
815
816 /*
817 -- 5. Validate unit configuration with unit_config_id = p_unit_config_id exists and is complete and active, using cursor check_unit_exists. If no record found, throw error "Unit Configuration does not exist or is not complete"
818 OPEN check_unit_exists (p_unit_config_id);
819 FETCH check_unit_exists INTO l_dummy_varchar;
820 IF (check_unit_exists%NOTFOUND)
821 THEN
822 FND_MESSAGE.SET_NAME('AHL', 'AHL_UA_UNIT_ID_NOT_FOUND');
823 FND_MSG_PUB.ADD;
824 CLOSE check_unit_exists;
825 RAISE FND_API.G_EXC_ERROR;
826 END IF;
827 CLOSE check_unit_exists;
828 */
829
830 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
831 THEN
832 fnd_log.string
833 (
834 fnd_log.level_statement,
835 L_DEBUG_MODULE,
836 'Basic validations done'
837 );
838 END IF;
839
840 x_is_conflict := FND_API.G_FALSE;
841
842 --6. get the greatest and the second greatest CLOSE_DATE and see if they are the same. If same then a conflict occurs.
843 OPEN get_prec_visit_rec (p_unit_config_id ,p_start_date_time);
844 FETCH get_prec_visit_rec INTO l_prec_visit;
845 IF (get_prec_visit_rec%FOUND)
846 THEN
847 -- populate the record to be sent back
848 x_prec_visit.VISIT_ID := l_prec_visit.VISIT_ID;
849 x_prec_visit.VISIT_NUMBER := l_prec_visit.VISIT_NUMBER;
850 x_prec_visit.OBJECT_VERSION_NUMBER := l_prec_visit.OBJECT_VERSION_NUMBER;
851 x_prec_visit.ORGANIZATION_ID := l_prec_visit.ORGANIZATION_ID;
852 x_prec_visit.ORG_NAME := l_prec_visit.ORGANIZATION_NAME;
853 x_prec_visit.DEPARTMENT_ID := l_prec_visit.DEPARTMENT_ID;
854 x_prec_visit.DEPT_NAME := l_prec_visit.DEPARTMENT_NAME;
855 x_prec_visit.START_DATE := l_prec_visit.START_DATE_TIME;
856 x_prec_visit.END_DATE := l_prec_visit.CLOSE_DATE_TIME;
857 x_prec_visit.SERVICE_REQUEST_ID := l_prec_visit.SERVICE_REQUEST_ID;
858 x_prec_visit.STATUS_CODE := l_prec_visit.STATUS_CODE;
859 x_prec_visit.VISIT_TYPE_CODE := l_prec_visit.VISIT_TYPE_CODE;
860 x_prec_visit.PROJECT_FLAG := l_prec_visit.PROJECT_FLAG;
861 x_prec_visit.PROJECT_ID := l_prec_visit.PROJECT_ID;
862 x_prec_visit.ATTRIBUTE_CATEGORY := l_prec_visit.ATTRIBUTE_CATEGORY;
863 x_prec_visit.ATTRIBUTE1 := l_prec_visit.ATTRIBUTE1;
864 x_prec_visit.ATTRIBUTE2 := l_prec_visit.ATTRIBUTE2;
865 x_prec_visit.ATTRIBUTE3 := l_prec_visit.ATTRIBUTE3;
866 x_prec_visit.ATTRIBUTE4 := l_prec_visit.ATTRIBUTE4;
867 x_prec_visit.ATTRIBUTE5 := l_prec_visit.ATTRIBUTE5;
868 x_prec_visit.ATTRIBUTE6 := l_prec_visit.ATTRIBUTE6;
869 x_prec_visit.ATTRIBUTE7 := l_prec_visit.ATTRIBUTE7;
870 x_prec_visit.ATTRIBUTE8 := l_prec_visit.ATTRIBUTE8;
871 x_prec_visit.ATTRIBUTE9 := l_prec_visit.ATTRIBUTE9;
872 x_prec_visit.ATTRIBUTE10 := l_prec_visit.ATTRIBUTE10;
873 x_prec_visit.ATTRIBUTE11 := l_prec_visit.ATTRIBUTE11;
874 x_prec_visit.ATTRIBUTE12 := l_prec_visit.ATTRIBUTE12;
875 x_prec_visit.ATTRIBUTE13 := l_prec_visit.ATTRIBUTE13;
876 x_prec_visit.ATTRIBUTE14 := l_prec_visit.ATTRIBUTE14;
877 x_prec_visit.ATTRIBUTE15 := l_prec_visit.ATTRIBUTE15;
878 x_prec_visit.SERVICE_REQUEST_ID := l_prec_visit.SERVICE_REQUEST_ID;
879 x_prec_visit.UNIT_SCHEDULE_ID := l_prec_visit.UNIT_SCHEDULE_ID;
880
881 FETCH get_prec_visit_rec INTO l_prec_visit;
882 IF (
883 get_prec_visit_rec%FOUND
884 AND
885 --Modifying to capture preceding overlapping visits too...
886 --l_prec_visit.CLOSE_DATE_TIME = x_prec_visit.END_DATE
887 l_prec_visit.START_DATE_TIME = x_prec_visit.START_DATE
888 -- If both start times and end time match, then conflict...
889 AND
890 NVL(l_prec_visit.CLOSE_DATE_TIME, l_prec_visit.START_DATE_TIME + 1/1440) = NVL(x_prec_visit.END_DATE, x_prec_visit.START_DATE + 1/1440)
891 )
892 THEN
893 x_is_conflict := FND_API.G_TRUE; -- events are in conflict
894 END IF;
895 END IF;
896 CLOSE get_prec_visit_rec;
897 -- API body ends here
898
899 -- Log API exit point
903 (
900 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
901 THEN
902 fnd_log.string
904 fnd_log.level_procedure,
905 L_DEBUG_MODULE||'.end',
906 'At the end of PLSQL procedure'
907 );
908 END IF;
909
910 -- Check Error Message stack.
911 x_msg_count := FND_MSG_PUB.count_msg;
912 IF x_msg_count > 0
913 THEN
914 RAISE FND_API.G_EXC_ERROR;
915 END IF;
916
917 -- Standard call to get message count and if count is 1, get message info
918 FND_MSG_PUB.count_and_get
919 (
920 p_count => x_msg_count,
921 p_data => x_msg_data,
922 p_encoded => FND_API.G_FALSE
923 );
924
925 EXCEPTION
926 WHEN FND_API.G_EXC_ERROR THEN
927 x_return_status := FND_API.G_RET_STS_ERROR;
928 FND_MSG_PUB.count_and_get
929 (
930 p_count => x_msg_count,
931 p_data => x_msg_data,
932 p_encoded => FND_API.G_FALSE
933 );
934
935 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
936 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
937 FND_MSG_PUB.count_and_get
938 (
939 p_count => x_msg_count,
940 p_data => x_msg_data,
941 p_encoded => FND_API.G_FALSE
942 );
943
944 WHEN OTHERS THEN
945 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
946 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
947 THEN
948 FND_MSG_PUB.add_exc_msg
949 (
950 p_pkg_name => G_PKG_NAME,
951 p_procedure_name => 'Get_Prec_Visit_Info',
952 p_error_text => SUBSTR(SQLERRM,1,240)
953 );
954 END IF;
955 FND_MSG_PUB.count_and_get
956 (
957 p_count => x_msg_count,
958 p_data => x_msg_data,
959 p_encoded => FND_API.G_FALSE
960 );
961 END Get_Prec_Visit_Info;
962
963 ----------------------------------------
964 -- Spec Procedure Get_Succ_Visit_Info --
965 ----------------------------------------
966 PROCEDURE Get_Succ_Visit_Info
967 (
968 p_api_version IN NUMBER,
969 x_return_status OUT NOCOPY VARCHAR2,
970 x_msg_count OUT NOCOPY NUMBER,
971 x_msg_data OUT NOCOPY VARCHAR2,
972 p_unit_config_id IN NUMBER,
973 p_end_date_time IN DATE,
974 x_succ_visit OUT NOCOPY AHL_VWP_VISITS_PVT.Visit_Rec_Type,
975 x_is_conflict OUT NOCOPY VARCHAR2
976 )
977 IS
978 -- 1. Declare local variables
979 l_api_name CONSTANT VARCHAR2(30) := 'Get_Succ_Visit_Info';
980 l_api_version CONSTANT NUMBER := 1.0;
981 l_return_status VARCHAR2(1);
982 l_msg_count NUMBER;
983 l_msg_data VARCHAR2(2000);
984 L_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
985
986
987 cursor get_succ_visit_rec
988 (
989 c_unit_config_id number,
990 c_end_time date
991 )
992 is
993 SELECT
994 VB.VISIT_ID,
995 VB.VISIT_NUMBER,
996 VB.OBJECT_VERSION_NUMBER,
997 VB.ORGANIZATION_ID,
998 HROU.NAME ORGANIZATION_NAME,
999 VB.DEPARTMENT_ID,
1000 BDPT.DESCRIPTION DEPARTMENT_NAME,
1001 VB.SERVICE_REQUEST_ID,
1002 VB.START_DATE_TIME,
1003 AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(VB.VISIT_ID , FND_API.G_FALSE) CLOSE_DATE_TIME,
1004 VB.STATUS_CODE,
1005 VB.VISIT_TYPE_CODE,
1006 VB.PROJECT_FLAG,
1007 VB.PROJECT_ID,
1008 VB.PROJECT_TEMPLATE_ID,
1009 VB.ATTRIBUTE_CATEGORY,
1010 VB.ATTRIBUTE1,
1011 VB.ATTRIBUTE2,
1012 VB.ATTRIBUTE3,
1013 VB.ATTRIBUTE4,
1014 VB.ATTRIBUTE5,
1015 VB.ATTRIBUTE6,
1016 VB.ATTRIBUTE7,
1017 VB.ATTRIBUTE8,
1018 VB.ATTRIBUTE9,
1019 VB.ATTRIBUTE10,
1020 VB.ATTRIBUTE11,
1021 VB.ATTRIBUTE12,
1022 VB.ATTRIBUTE13,
1023 VB.ATTRIBUTE14,
1024 VB.ATTRIBUTE15,
1025 VB.UNIT_SCHEDULE_ID
1026 FROM AHL_VISITS_B VB,
1027 AHL_UNIT_CONFIG_HEADERS UC,
1028 HR_ALL_ORGANIZATION_UNITS HROU,
1029 BOM_DEPARTMENTS BDPT
1030 WHERE
1031 VB.ORGANIZATION_ID = HROU.ORGANIZATION_ID(+) AND
1032 VB.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+) AND
1033 VB.ITEM_INSTANCE_ID = UC.CSI_ITEM_INSTANCE_ID AND
1034 UC.UNIT_CONFIG_HEADER_ID = c_unit_config_id AND
1035 START_DATE_TIME > c_end_time AND
1036 VB.STATUS_CODE NOT IN ('CANCELLED', 'DELETED') AND
1037 START_DATE_TIME IS NOT NULL
1038 ORDER BY START_DATE_TIME ASC, NVL(CLOSE_DATE_TIME,START_DATE_TIME + 1/1440) ASC;
1039
1040 l_succ_visit get_succ_visit_rec%rowtype;
1041
1042 BEGIN
1043 -- Standard call to check for call compatibility
1044 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1045 THEN
1046 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1047 END IF;
1048
1049 -- Initialize API return status to success
1050 x_return_status := FND_API.G_RET_STS_SUCCESS;
1051
1052 -- Log API entry point
1053 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1054 THEN
1055 fnd_log.string
1056 (
1057 fnd_log.level_procedure,
1058 L_DEBUG_MODULE||'.begin',
1059 'At the start of PLSQL procedure'
1060 );
1061 END IF;
1062
1063 -- API body starts here
1064 -- 4. If (p_unit_config_id is null or p_end_date_time is null), then display error "Unit Configuration Id and Start Time are mandatory parameters"
1065 -- This API is not expected to be called with p_end_date_time = NULL, since the calling API should already be verifying that, hence throwing error if thats the case...
1066 IF (
1067 p_unit_config_id IS NULL OR p_unit_config_id = FND_API.G_MISS_NUM OR
1068 p_end_date_time IS NULL OR p_end_date_time = FND_API.G_MISS_DATE
1069 )
1070 THEN
1071 FND_MESSAGE.SET_NAME('AHL', 'AHL_COM_INVALID_PROCEDURE_CALL');
1075 END IF;
1072 FND_MESSAGE.SET_TOKEN('PROCEDURE', l_api_name);
1073 FND_MSG_PUB.ADD;
1074 RAISE FND_API.G_EXC_ERROR;
1076
1077 /*
1078 -- 5. Validate unit configuration with unit_config_id = p_unit_config_id exists and is complete and active, using cursor check_unit_exists. If no record found, throw error "Unit Configuration does not exist or is not complete"
1079 OPEN check_unit_exists (p_unit_config_id);
1080 FETCH check_unit_exists INTO l_dummy_varchar;
1081 IF (check_unit_exists%NOTFOUND)
1082 THEN
1083 FND_MESSAGE.SET_NAME('AHL', 'AHL_UA_UNIT_ID_NOT_FOUND');
1084 FND_MSG_PUB.ADD;
1085 CLOSE check_unit_exists;
1086 RAISE FND_API.G_EXC_ERROR;
1087 END IF;
1088 CLOSE check_unit_exists;
1089 */
1090
1091 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1092 THEN
1093 fnd_log.string
1094 (
1095 fnd_log.level_statement,
1096 L_DEBUG_MODULE,
1097 'Basic validations done'
1098 );
1099 END IF;
1100
1101 x_is_conflict := FND_API.G_FALSE;
1102
1103 --6. get the greatest and the second greatest CLOSE_DATE and see if they are the same. If same then a conflict occurs.
1104 OPEN get_succ_visit_rec (p_unit_config_id ,p_end_date_time);
1105 FETCH get_succ_visit_rec INTO l_succ_visit;
1106 IF (get_succ_visit_rec%FOUND)
1107 THEN
1108 -- populate the record to be sent back
1109 x_succ_visit.VISIT_ID := l_succ_visit.VISIT_ID;
1110 x_succ_visit.VISIT_NUMBER := l_succ_visit.VISIT_NUMBER;
1111 x_succ_visit.OBJECT_VERSION_NUMBER := l_succ_visit.OBJECT_VERSION_NUMBER;
1112 x_succ_visit.ORGANIZATION_ID := l_succ_visit.ORGANIZATION_ID;
1113 x_succ_visit.ORG_NAME := l_succ_visit.ORGANIZATION_NAME;
1114 x_succ_visit.DEPARTMENT_ID := l_succ_visit.DEPARTMENT_ID;
1115 x_succ_visit.DEPT_NAME := l_succ_visit.DEPARTMENT_NAME;
1116 x_succ_visit.START_DATE := l_succ_visit.START_DATE_TIME;
1117 x_succ_visit.END_DATE := l_succ_visit.CLOSE_DATE_TIME;
1118 x_succ_visit.SERVICE_REQUEST_ID := l_succ_visit.SERVICE_REQUEST_ID;
1119 x_succ_visit.STATUS_CODE := l_succ_visit.STATUS_CODE;
1120 x_succ_visit.VISIT_TYPE_CODE := l_succ_visit.VISIT_TYPE_CODE;
1121 x_succ_visit.PROJECT_FLAG := l_succ_visit.PROJECT_FLAG;
1122 x_succ_visit.PROJECT_ID := l_succ_visit.PROJECT_ID;
1123 x_succ_visit.ATTRIBUTE_CATEGORY := l_succ_visit.ATTRIBUTE_CATEGORY;
1124 x_succ_visit.ATTRIBUTE1 := l_succ_visit.ATTRIBUTE1;
1125 x_succ_visit.ATTRIBUTE2 := l_succ_visit.ATTRIBUTE2;
1126 x_succ_visit.ATTRIBUTE3 := l_succ_visit.ATTRIBUTE3;
1127 x_succ_visit.ATTRIBUTE4 := l_succ_visit.ATTRIBUTE4;
1128 x_succ_visit.ATTRIBUTE5 := l_succ_visit.ATTRIBUTE5;
1129 x_succ_visit.ATTRIBUTE6 := l_succ_visit.ATTRIBUTE6;
1130 x_succ_visit.ATTRIBUTE7 := l_succ_visit.ATTRIBUTE7;
1131 x_succ_visit.ATTRIBUTE8 := l_succ_visit.ATTRIBUTE8;
1132 x_succ_visit.ATTRIBUTE9 := l_succ_visit.ATTRIBUTE9;
1133 x_succ_visit.ATTRIBUTE10 := l_succ_visit.ATTRIBUTE10;
1134 x_succ_visit.ATTRIBUTE11 := l_succ_visit.ATTRIBUTE11;
1135 x_succ_visit.ATTRIBUTE12 := l_succ_visit.ATTRIBUTE12;
1136 x_succ_visit.ATTRIBUTE13 := l_succ_visit.ATTRIBUTE13;
1137 x_succ_visit.ATTRIBUTE14 := l_succ_visit.ATTRIBUTE14;
1138 x_succ_visit.ATTRIBUTE15 := l_succ_visit.ATTRIBUTE15;
1139 x_succ_visit.SERVICE_REQUEST_ID := l_succ_visit.SERVICE_REQUEST_ID;
1140 x_succ_visit.UNIT_SCHEDULE_ID := l_succ_visit.UNIT_SCHEDULE_ID;
1141
1142 FETCH get_succ_visit_rec INTO l_succ_visit;
1143 IF (
1144 get_succ_visit_rec%FOUND
1145 AND
1146 l_succ_visit.START_DATE_TIME = x_succ_visit.START_DATE
1147 -- If both start times and end time match, then conflict...
1148 AND
1149 NVL(l_succ_visit.CLOSE_DATE_TIME, l_succ_visit.START_DATE_TIME + 1/1440) = NVL(x_succ_visit.END_DATE, x_succ_visit.START_DATE + 1/1440)
1150 )
1151 THEN
1152 x_is_conflict := FND_API.G_TRUE; -- events are in conflict
1153 END IF;
1154 END IF;
1155 CLOSE get_succ_visit_rec;
1156 -- API body ends here
1157
1158 -- Log API exit point
1159 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1160 THEN
1161 fnd_log.string
1162 (
1163 fnd_log.level_procedure,
1164 L_DEBUG_MODULE||'.end',
1165 'At the end of PLSQL procedure'
1166 );
1167 END IF;
1168
1169 -- Check Error Message stack.
1170 x_msg_count := FND_MSG_PUB.count_msg;
1171 IF x_msg_count > 0
1172 THEN
1173 RAISE FND_API.G_EXC_ERROR;
1174 END IF;
1175
1176 -- Standard call to get message count and if count is 1, get message info
1177 FND_MSG_PUB.count_and_get
1178 (
1179 p_count => x_msg_count,
1180 p_data => x_msg_data,
1181 p_encoded => FND_API.G_FALSE
1182 );
1183
1184 EXCEPTION
1185 WHEN FND_API.G_EXC_ERROR THEN
1186 x_return_status := FND_API.G_RET_STS_ERROR;
1187 FND_MSG_PUB.count_and_get
1188 (
1189 p_count => x_msg_count,
1190 p_data => x_msg_data,
1191 p_encoded => FND_API.G_FALSE
1192 );
1193
1194 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1195 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1196 FND_MSG_PUB.count_and_get
1197 (
1198 p_count => x_msg_count,
1199 p_data => x_msg_data,
1200 p_encoded => FND_API.G_FALSE
1201 );
1202
1203 WHEN OTHERS THEN
1204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1205 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1206 THEN
1207 FND_MSG_PUB.add_exc_msg
1208 (
1209 p_pkg_name => G_PKG_NAME,
1210 p_procedure_name => 'Get_Succ_Visit_Info',
1211 p_error_text => SUBSTR(SQLERRM,1,240)
1212 );
1213 END IF;
1214 FND_MSG_PUB.count_and_get
1215 (
1216 p_count => x_msg_count,
1217 p_data => x_msg_data,
1218 p_encoded => FND_API.G_FALSE
1219 );
1223 -- Spec Procedure Get_Prec_Event_Info --
1220 END Get_Succ_Visit_Info;
1221
1222 ---------------------------------------------
1224 ---------------------------------------------
1225 PROCEDURE Get_Prec_Event_Info
1226 (
1227 p_api_version IN NUMBER,
1228 x_return_status OUT NOCOPY VARCHAR2,
1229 x_msg_count OUT NOCOPY NUMBER,
1230 x_msg_data OUT NOCOPY VARCHAR2,
1231 p_unit_config_id IN NUMBER,
1232 p_start_date_time IN DATE,
1233 p_use_actuals IN VARCHAR2,
1234 x_prec_visit OUT NOCOPY AHL_VWP_VISITS_PVT.Visit_Rec_Type,
1235 x_prec_flight_schedule OUT NOCOPY AHL_UA_FLIGHT_SCHEDULES_PVT.Flight_Schedule_Rec_Type,
1236 x_is_conflict OUT NOCOPY VARCHAR2,
1237 x_is_org_in_user_ou OUT NOCOPY VARCHAR2
1238 )
1239 IS
1240 -- 1. Declare local variables
1241 l_api_name CONSTANT VARCHAR2(30) := 'Get_Prec_Event_Info';
1242 l_api_version CONSTANT NUMBER := 1.0;
1243 l_return_status VARCHAR2(1);
1244 l_msg_count NUMBER;
1245 l_msg_data VARCHAR2(2000);
1246 L_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1247
1248 l_prec_flight_schedule AHL_UA_FLIGHT_SCHEDULES_PVT.Flight_Schedule_Rec_Type;
1249 l_prec_visit AHL_VWP_VISITS_PVT.Visit_Rec_Type;
1250 l_prec_is_visit VARCHAR2(1);
1251 l_is_flight_conflict VARCHAR2(1);
1252 l_is_visit_conflict VARCHAR2(1);
1253 BEGIN
1254 -- Standard call to check for call compatibility
1255 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1256 THEN
1257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1258 END IF;
1259
1260 -- Initialize API return status to success
1261 x_return_status := FND_API.G_RET_STS_SUCCESS;
1262
1263 -- Log API entry point
1264 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1265 THEN
1266 fnd_log.string
1267 (
1268 fnd_log.level_procedure,
1269 L_DEBUG_MODULE||'.begin',
1270 'At the start of PLSQL procedure'
1271 );
1272 END IF;
1273
1274 -- API body starts here
1275 -- 4. If (p_unit_config_id is null or p_start_date_time is null), then display error "Unit Configuration Id and End Time are mandatory parameters"
1276
1277 IF (
1278 p_unit_config_id IS NULL OR p_unit_config_id = FND_API.G_MISS_NUM OR
1279 p_start_date_time IS NULL OR p_start_date_time = FND_API.G_MISS_DATE
1280 )
1281 THEN
1282 FND_MESSAGE.SET_NAME('AHL', 'AHL_COM_INVALID_PROCEDURE_CALL');
1283 FND_MESSAGE.SET_TOKEN('PROCEDURE', l_api_name);
1284 FND_MSG_PUB.ADD;
1285 RAISE FND_API.G_EXC_ERROR;
1286 END IF;
1287
1288 /*
1289 -- 5. Validate unit configuration with unit_config_id = p_unit_config_id exists and is complete and active, using cursor check_unit_exists. If no record found, throw error "Unit Configuration does not exist or is not complete"
1290 OPEN check_unit_exists (p_unit_config_id);
1291 FETCH check_unit_exists INTO l_dummy_varchar;
1292 IF (check_unit_exists%NOTFOUND)
1293 THEN
1294 FND_MESSAGE.SET_NAME('AHL', 'AHL_UA_UNIT_ID_NOT_FOUND');
1295 FND_MSG_PUB.ADD;
1296 CLOSE check_unit_exists;
1297 RAISE FND_API.G_EXC_ERROR;
1298 END IF;
1299 CLOSE check_unit_exists;
1300 */
1301
1302 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1303 THEN
1304 fnd_log.string
1305 (
1306 fnd_log.level_statement,
1307 L_DEBUG_MODULE,
1308 'Basic validations done'
1309 );
1310 END IF;
1311
1312 x_is_conflict := FND_API.G_FALSE;
1313 x_is_org_in_user_ou := FND_API.G_TRUE;
1314
1315 -- call the Get_Prec_Flight_Info to find out the preceding Flight Schedule or if there is a conflict
1316 -- between preceding Flight Schedules
1317 Get_Prec_Flight_Info
1318 (
1319 p_api_version ,
1320 x_return_status ,
1321 x_msg_count ,
1322 x_msg_data ,
1323 p_unit_config_id,
1324 p_start_date_time,
1325 p_use_actuals ,
1326 l_prec_flight_schedule ,
1327 l_is_flight_conflict
1328 );
1329 -- Check Error Message stack.
1330 x_msg_count := FND_MSG_PUB.count_msg;
1331 IF x_msg_count > 0
1332 THEN
1333 RAISE FND_API.G_EXC_ERROR;
1334 END IF;
1335
1336 -- call the Get_Prec_Visit_Info to find out the preceding Flight Schedule or if there is a conflict
1337 -- between preceding Flight Schedules
1338 Get_Prec_Visit_Info
1339 (
1340 p_api_version ,
1341 x_return_status ,
1342 x_msg_count ,
1343 x_msg_data ,
1344 p_unit_config_id,
1345 p_start_date_time,
1346 l_prec_visit ,
1347 l_is_visit_conflict
1348 );
1349 -- Check Error Message stack.
1350 x_msg_count := FND_MSG_PUB.count_msg;
1351 IF x_msg_count > 0
1352 THEN
1353 RAISE FND_API.G_EXC_ERROR;
1354 END IF;
1355
1356 IF (l_prec_flight_schedule.UNIT_SCHEDULE_ID IS NULL AND l_prec_visit.VISIT_ID IS NULL)
1357 THEN
1358 RETURN;
1359 ELSIF (l_prec_flight_schedule.UNIT_SCHEDULE_ID IS NULL)
1360 THEN
1361 l_prec_is_visit := FND_API.G_TRUE; -- preceding event is a visit
1362 x_is_conflict := l_is_visit_conflict; -- events are in conflict
1363 ELSIF (l_prec_visit.VISIT_ID IS NULL)
1364 THEN
1365 l_prec_is_visit := FND_API.G_FALSE; -- preceding event is not a visit
1366 x_is_conflict := l_is_flight_conflict; -- events are in conflict
1367 ELSE -- implies both records are not NULL
1368 IF (p_use_actuals = FND_API.G_TRUE)
1369 THEN
1370 --Modifying to capture preceding overlapping visits too...
1371 /*IF(nvl(l_prec_flight_schedule.ACTUAL_ARRIVAL_TIME, l_prec_flight_schedule.EST_ARRIVAL_TIME) > l_prec_visit.END_DATE )
1372 THEN
1373 l_prec_is_visit := FND_API.G_FALSE; -- preceding event is not a visit
1377 l_prec_is_visit := FND_API.G_TRUE; -- preceding event is a visit
1374 x_is_conflict := l_is_flight_conflict; -- events are in conflict
1375 ELSIF (nvl(l_prec_flight_schedule.ACTUAL_ARRIVAL_TIME, l_prec_flight_schedule.EST_ARRIVAL_TIME) < l_prec_visit.END_DATE )
1376 THEN
1378 x_is_conflict := l_is_visit_conflict; -- events are in conflict
1379 ELSE
1380 x_is_conflict := FND_API.G_TRUE; -- events are in conflict
1381 END IF;*/
1382 IF(nvl(l_prec_flight_schedule.ACTUAL_DEPARTURE_TIME, l_prec_flight_schedule.EST_DEPARTURE_TIME) > l_prec_visit.START_DATE )
1383 THEN
1384 l_prec_is_visit := FND_API.G_FALSE; -- preceding event is not a visit
1385 x_is_conflict := l_is_flight_conflict; -- events are in conflict
1386 ELSIF (nvl(l_prec_flight_schedule.ACTUAL_DEPARTURE_TIME, l_prec_flight_schedule.EST_DEPARTURE_TIME) < l_prec_visit.START_DATE )
1387 THEN
1388 l_prec_is_visit := FND_API.G_TRUE; -- preceding event is a visit
1389 x_is_conflict := l_is_visit_conflict; -- events are in conflict
1390 ELSE
1391 x_is_conflict := FND_API.G_TRUE; -- events are in conflict
1392 END IF;
1393 ELSE
1394 --Modifying to capture preceding overlapping visits too...
1395 /*IF(l_prec_flight_schedule.EST_ARRIVAL_TIME > l_prec_visit.END_DATE )
1396 THEN
1397 l_prec_is_visit := FND_API.G_FALSE; -- preceding event is not a visit
1398 x_is_conflict := l_is_flight_conflict; -- events are in conflict
1399 ELSIF (l_prec_flight_schedule.EST_ARRIVAL_TIME < l_prec_visit.END_DATE )
1400 THEN
1401 l_prec_is_visit := FND_API.G_TRUE; -- preceding event is a visit
1402 x_is_conflict := l_is_visit_conflict; -- events are in conflict
1403 ELSE
1404 x_is_conflict := FND_API.G_TRUE; -- events are in conflict
1405 END IF;*/
1406 IF(l_prec_flight_schedule.EST_DEPARTURE_TIME > l_prec_visit.START_DATE )
1407 THEN
1408 l_prec_is_visit := FND_API.G_FALSE; -- preceding event is not a visit
1409 x_is_conflict := l_is_flight_conflict; -- events are in conflict
1410 ELSIF (l_prec_flight_schedule.EST_DEPARTURE_TIME < l_prec_visit.START_DATE )
1411 THEN
1412 l_prec_is_visit := FND_API.G_TRUE; -- preceding event is a visit
1413 x_is_conflict := l_is_visit_conflict; -- events are in conflict
1414 ELSE
1415 x_is_conflict := FND_API.G_TRUE; -- events are in conflict
1416 END IF;
1417 END IF;
1418 END IF;
1419
1420 -- if there is no conflict then populate the relevant record (either visit or flight schedule) to be sent back
1421 IF( x_is_conflict = FND_API.G_FALSE)
1422 THEN
1423 IF (l_prec_is_visit = FND_API.G_FALSE)
1424 THEN
1425 x_prec_flight_schedule := l_prec_flight_schedule; -- populate the record to be sent back
1426 ELSE
1427 x_prec_visit := l_prec_visit; -- populate the record to be sent back
1428 x_is_org_in_user_ou := AHL_UTILITY_PVT.is_org_in_user_ou (
1429 l_prec_visit.organization_id,
1430 l_prec_visit.org_name,
1431 l_return_status,
1432 l_msg_data
1433 );
1434 x_msg_count := FND_MSG_PUB.count_msg;
1435 IF x_msg_count > 0
1436 THEN
1437 RAISE FND_API.G_EXC_ERROR;
1438 END IF;
1439 END IF;
1440 END IF;
1441 -- API body ends here
1442
1443 -- Log API exit point
1444 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1445 THEN
1446 fnd_log.string
1447 (
1448 fnd_log.level_procedure,
1449 L_DEBUG_MODULE||'.end',
1450 'At the end of PLSQL procedure'
1451 );
1452 END IF;
1453
1454 -- Check Error Message stack.
1455 x_msg_count := FND_MSG_PUB.count_msg;
1456 IF x_msg_count > 0
1457 THEN
1458 RAISE FND_API.G_EXC_ERROR;
1459 END IF;
1460
1461 -- Standard call to get message count and if count is 1, get message info
1462 FND_MSG_PUB.count_and_get
1463 (
1464 p_count => x_msg_count,
1465 p_data => x_msg_data,
1466 p_encoded => FND_API.G_FALSE
1467 );
1468
1469 EXCEPTION
1470 WHEN FND_API.G_EXC_ERROR THEN
1471 x_return_status := FND_API.G_RET_STS_ERROR;
1472 FND_MSG_PUB.count_and_get
1473 (
1474 p_count => x_msg_count,
1475 p_data => x_msg_data,
1476 p_encoded => FND_API.G_FALSE
1477 );
1478
1479 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1481 FND_MSG_PUB.count_and_get
1482 (
1483 p_count => x_msg_count,
1484 p_data => x_msg_data,
1485 p_encoded => FND_API.G_FALSE
1486 );
1487
1488 WHEN OTHERS THEN
1489 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1490 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1491 THEN
1492 FND_MSG_PUB.add_exc_msg
1493 (
1494 p_pkg_name => G_PKG_NAME,
1495 p_procedure_name => 'Get_Prec_Event_Info',
1496 p_error_text => SUBSTR(SQLERRM,1,240)
1497 );
1498 END IF;
1499 FND_MSG_PUB.count_and_get
1500 (
1501 p_count => x_msg_count,
1502 p_data => x_msg_data,
1503 p_encoded => FND_API.G_FALSE
1504 );
1505 END Get_Prec_Event_Info;
1506
1507 ---------------------------------------------
1508 -- Spec Procedure Get_Succ_Event_Info --
1509 ---------------------------------------------
1510 PROCEDURE Get_Succ_Event_Info
1511 (
1512 p_api_version IN NUMBER,
1513 x_return_status OUT NOCOPY VARCHAR2,
1514 x_msg_count OUT NOCOPY NUMBER,
1515 x_msg_data OUT NOCOPY VARCHAR2,
1516 p_unit_config_id IN NUMBER,
1517 p_end_date_time IN DATE,
1518 p_use_actuals IN VARCHAR2,
1519 x_succ_visit OUT NOCOPY AHL_VWP_VISITS_PVT.Visit_Rec_Type,
1520 x_succ_flight_schedule OUT NOCOPY AHL_UA_FLIGHT_SCHEDULES_PVT.Flight_Schedule_Rec_Type,
1524 IS
1521 x_is_conflict OUT NOCOPY VARCHAR2,
1522 x_is_org_in_user_ou OUT NOCOPY VARCHAR2
1523 )
1525 -- 1. Declare local variables
1526 l_api_name CONSTANT VARCHAR2(30) := 'Get_Succ_Event_Info';
1527 l_api_version CONSTANT NUMBER := 1.0;
1528 l_return_status VARCHAR2(1);
1529 l_msg_count NUMBER;
1530 l_msg_data VARCHAR2(2000);
1531 L_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1532
1533 l_succ_flight_schedule AHL_UA_FLIGHT_SCHEDULES_PVT.Flight_Schedule_Rec_Type;
1534 l_succ_visit AHL_VWP_VISITS_PVT.Visit_Rec_Type;
1535 l_succ_is_visit VARCHAR2(1);
1536 l_is_flight_conflict VARCHAR2(1);
1537 l_is_visit_conflict VARCHAR2(1);
1538
1539 BEGIN
1540 -- Standard call to check for call compatibility
1541 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1542 THEN
1543 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1544 END IF;
1545
1546 -- Initialize API return status to success
1547 x_return_status := FND_API.G_RET_STS_SUCCESS;
1548
1549 -- Log API entry point
1550 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1551 THEN
1552 fnd_log.string
1553 (
1554 fnd_log.level_procedure,
1555 L_DEBUG_MODULE||'.begin',
1556 'At the start of PLSQL procedure'
1557 );
1558 END IF;
1559
1560 -- API body starts here
1561 -- 4. If (p_unit_config_id is null or p_end_date_time is null), then display error "Unit Configuration Id and End Time are mandatory parameters"
1562 -- This API is not expected to be called with p_end_date_time = NULL, since the calling API should already be verifying that, hence throwing error if thats the case...
1563 IF (
1564 p_unit_config_id IS NULL OR p_unit_config_id = FND_API.G_MISS_NUM OR
1565 p_end_date_time IS NULL OR p_end_date_time = FND_API.G_MISS_DATE
1566 )
1567 THEN
1568 FND_MESSAGE.SET_NAME('AHL', 'AHL_COM_INVALID_PROCEDURE_CALL');
1569 FND_MESSAGE.SET_TOKEN('PROCEDURE', l_api_name);
1570 FND_MSG_PUB.ADD;
1571 RAISE FND_API.G_EXC_ERROR;
1572 END IF;
1573
1574 /*
1575 -- 5. Validate unit configuration with unit_config_id = p_unit_config_id exists and is complete and active, using cursor check_unit_exists. If no record found, throw error "Unit Configuration does not exist or is not complete"
1576 OPEN check_unit_exists (p_unit_config_id);
1577 FETCH check_unit_exists INTO l_dummy_varchar;
1578 IF (check_unit_exists%NOTFOUND)
1579 THEN
1580 FND_MESSAGE.SET_NAME('AHL', 'AHL_UA_UNIT_ID_NOT_FOUND');
1581 FND_MSG_PUB.ADD;
1582 CLOSE check_unit_exists;
1583 RAISE FND_API.G_EXC_ERROR;
1584 END IF;
1585 CLOSE check_unit_exists;
1586 */
1587
1588 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1589 THEN
1590 fnd_log.string
1591 (
1592 fnd_log.level_statement,
1593 L_DEBUG_MODULE,
1594 'Basic validations done'
1595 );
1596 END IF;
1597
1598 x_is_conflict := FND_API.G_FALSE;
1599 x_is_org_in_user_ou := FND_API.G_TRUE;
1600
1601 -- call the Get_Succ_Flight_Info to find out the preceding Flight Schedule or if there is a conflict
1602 -- between preceding Flight Schedules
1603 Get_Succ_Flight_Info
1604 (
1605 p_api_version ,
1606 x_return_status ,
1607 x_msg_count ,
1608 x_msg_data ,
1609 p_unit_config_id,
1610 p_end_date_time,
1611 p_use_actuals ,
1612 l_succ_flight_schedule ,
1613 l_is_flight_conflict
1614 );
1615 -- Check Error Message stack.
1616 x_msg_count := FND_MSG_PUB.count_msg;
1617 IF x_msg_count > 0
1618 THEN
1619 RAISE FND_API.G_EXC_ERROR;
1620 END IF;
1621
1622 -- call the Get_Succ_Visit_Info to find out the preceding Flight Schedule or if there is a conflict
1623 -- between preceding Flight Schedules
1624 Get_Succ_Visit_Info
1625 (
1626 p_api_version ,
1627 x_return_status ,
1628 x_msg_count ,
1629 x_msg_data ,
1630 p_unit_config_id,
1631 p_end_date_time,
1632 l_succ_visit ,
1633 l_is_visit_conflict
1634 );
1635 -- Check Error Message stack.
1636 x_msg_count := FND_MSG_PUB.count_msg;
1637 IF x_msg_count > 0
1638 THEN
1639 RAISE FND_API.G_EXC_ERROR;
1640 END IF;
1641
1642 IF (l_succ_flight_schedule.UNIT_SCHEDULE_ID IS NULL AND l_succ_visit.VISIT_ID IS NULL)
1643 THEN
1644 RETURN;
1645 ELSIF (l_succ_flight_schedule.UNIT_SCHEDULE_ID IS NULL)
1646 THEN
1647 l_succ_is_visit := FND_API.G_TRUE; -- preceding event is a visit
1648 x_is_conflict := l_is_visit_conflict; -- events are in conflict
1649 ELSIF (l_succ_visit.VISIT_ID IS NULL)
1650 THEN
1651 l_succ_is_visit := FND_API.G_FALSE; -- preceding event is not a visit
1652 x_is_conflict := l_is_flight_conflict; -- events are in conflict
1653 ELSE -- implies both records are not NULL
1654 IF (p_use_actuals = FND_API.G_TRUE)
1655 THEN
1656 IF(nvl(l_succ_flight_schedule.ACTUAL_DEPARTURE_TIME, l_succ_flight_schedule.EST_DEPARTURE_TIME) < l_succ_visit.START_DATE )
1657 THEN
1658 l_succ_is_visit := FND_API.G_FALSE; -- succeeding event is not a visit
1659 x_is_conflict := l_is_flight_conflict; -- events are in conflict
1660 ELSIF (nvl(l_succ_flight_schedule.ACTUAL_DEPARTURE_TIME, l_succ_flight_schedule.EST_DEPARTURE_TIME) > l_succ_visit.START_DATE )
1661 THEN
1662 l_succ_is_visit := FND_API.G_TRUE; -- succeeding event is a visit
1663 x_is_conflict := l_is_visit_conflict; -- events are in conflict
1664 ELSE
1665 x_is_conflict :=FND_API.G_TRUE; -- events are in conflict
1666 END IF;
1667 ELSE
1668 IF(l_succ_flight_schedule.EST_DEPARTURE_TIME < l_succ_visit.START_DATE )
1669 THEN
1673 THEN
1670 l_succ_is_visit := FND_API.G_FALSE; -- succeeding event is not a visit
1671 x_is_conflict := l_is_flight_conflict; -- events are in conflict
1672 ELSIF (l_succ_flight_schedule.EST_DEPARTURE_TIME > l_succ_visit.START_DATE )
1674 l_succ_is_visit := FND_API.G_TRUE; -- succeeding event is a visit
1675 x_is_conflict := l_is_visit_conflict; -- events are in conflict
1676 ELSE
1677 x_is_conflict :=FND_API.G_TRUE; -- events are in conflict
1678 END IF;
1679 END IF;
1680 END IF;
1681
1682 -- if there is no conflict then populate the relevant record (either visit or flight schedule) to be sent back
1683 IF( x_is_conflict = FND_API.G_FALSE)
1684 THEN
1685 IF (l_succ_is_visit = FND_API.G_FALSE)
1686 THEN
1687 x_succ_flight_schedule := l_succ_flight_schedule; -- populate the record to be sent back
1688 ELSE
1689 x_succ_visit := l_succ_visit; -- populate the record to be sent back
1690 x_is_org_in_user_ou := AHL_UTILITY_PVT.is_org_in_user_ou (
1691 l_succ_visit.organization_id,
1692 l_succ_visit.org_name,
1693 l_return_status,
1694 l_msg_data
1695 );
1696
1697 x_msg_count := FND_MSG_PUB.count_msg;
1698 IF x_msg_count > 0
1699 THEN
1700 RAISE FND_API.G_EXC_ERROR;
1701 END IF;
1702 END IF;
1703 END IF;
1704 -- API body ends here
1705
1706 -- Log API exit point
1707 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1708 THEN
1709 fnd_log.string
1710 (
1711 fnd_log.level_procedure,
1712 L_DEBUG_MODULE||'.end',
1713 'At the end of PLSQL procedure'
1714 );
1715 END IF;
1716
1717 -- Check Error Message stack.
1718 x_msg_count := FND_MSG_PUB.count_msg;
1719 IF x_msg_count > 0
1720 THEN
1721 RAISE FND_API.G_EXC_ERROR;
1722 END IF;
1723
1724 -- Standard call to get message count and if count is 1, get message info
1725 FND_MSG_PUB.count_and_get
1726 (
1727 p_count => x_msg_count,
1728 p_data => x_msg_data,
1729 p_encoded => FND_API.G_FALSE
1730 );
1731
1732 EXCEPTION
1733 WHEN FND_API.G_EXC_ERROR THEN
1734 x_return_status := FND_API.G_RET_STS_ERROR;
1735 FND_MSG_PUB.count_and_get
1736 (
1737 p_count => x_msg_count,
1738 p_data => x_msg_data,
1739 p_encoded => FND_API.G_FALSE
1740 );
1741
1742 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1743 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1744 FND_MSG_PUB.count_and_get
1745 (
1746 p_count => x_msg_count,
1747 p_data => x_msg_data,
1748 p_encoded => FND_API.G_FALSE
1749 );
1750
1751 WHEN OTHERS THEN
1752 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1753 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1754 THEN
1755 FND_MSG_PUB.add_exc_msg
1756 (
1757 p_pkg_name => G_PKG_NAME,
1758 p_procedure_name => 'Get_Succ_Event_Info',
1759 p_error_text => SUBSTR(SQLERRM,1,240)
1760 );
1761 END IF;
1762 FND_MSG_PUB.count_and_get
1763 (
1764 p_count => x_msg_count,
1765 p_data => x_msg_data,
1766 p_encoded => FND_API.G_FALSE
1767 );
1768 END Get_Succ_Event_Info;
1769
1770 ---------------------------------------------
1771 -- Non-spec Procedure Get_Succ_Flight_Info --
1772 ---------------------------------------------
1773 PROCEDURE Get_Succ_Flight_Info
1774 (
1775 p_api_version IN NUMBER,
1776 x_return_status OUT NOCOPY VARCHAR2,
1777 x_msg_count OUT NOCOPY NUMBER,
1778 x_msg_data OUT NOCOPY VARCHAR2,
1779 p_unit_config_id IN NUMBER,
1780 p_end_date_time IN DATE,
1781 p_use_actuals IN VARCHAR2,
1782 x_succ_flight_schedule OUT NOCOPY AHL_UA_FLIGHT_SCHEDULES_PVT.Flight_Schedule_Rec_Type,
1783 x_is_conflict OUT NOCOPY VARCHAR2
1784 )
1785 IS
1786 -- 1. Declare local variables
1787 l_api_name CONSTANT VARCHAR2(30) := 'Get_Succ_Flight_Info';
1788 l_api_version CONSTANT NUMBER := 1.0;
1789 l_return_status VARCHAR2(1);
1790 l_msg_count NUMBER;
1791 l_msg_data VARCHAR2(2000);
1792 L_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1793
1794 -- 2. Define cursor get_succ_flight_act to retrieve all flights for which the departure times are after or equal to end time (using actual times if available)
1795 cursor get_succ_flight_act
1796 (
1797 p_unit_config_id number,
1798 p_end_time date
1799 )
1800 is
1801 select *
1802 from ahl_unit_schedules_v
1803 where unit_config_header_id = p_unit_config_id
1804 and nvl(actual_departure_time, est_departure_time) > p_end_time
1805 order by nvl(actual_departure_time, est_departure_time) asc, nvl(actual_arrival_time, est_arrival_time) asc;
1806
1807 -- 3. Define cursor get_succ_flight_est to retrieve all flights for which the departure times are after or equal to end time (without using actual times)
1808 cursor get_succ_flight_est
1809 (
1810 p_unit_config_id number,
1811 p_end_time date
1812 )
1813 is
1814 select *
1815 from ahl_unit_schedules_v
1816 where unit_config_header_id = p_unit_config_id
1817 and est_departure_time > p_end_time
1818 order by est_departure_time asc, est_arrival_time asc;
1819
1820 l_act_flight_rec get_succ_flight_act%rowtype;
1821 l_est_flight_rec get_succ_flight_est%rowtype;
1822
1823 BEGIN
1824 -- Standard call to check for call compatibility
1825 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1826 THEN
1827 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1828 END IF;
1829
1830 -- Initialize API return status to success
1834 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1831 x_return_status := FND_API.G_RET_STS_SUCCESS;
1832
1833 -- Log API entry point
1835 THEN
1836 fnd_log.string
1837 (
1838 fnd_log.level_procedure,
1839 L_DEBUG_MODULE||'.begin',
1840 'At the start of PLSQL procedure'
1841 );
1842 END IF;
1843
1844 -- API body starts here
1845 -- 4. If (p_unit_config_id is null or p_end_date_time is null), then display error "Unit Configuration Id and End Time are mandatory parameters"
1846 -- This API is not expected to be called with p_end_date_time = NULL, since the calling API should already be verifying that, hence throwing error if thats the case...
1847 IF (
1848 p_unit_config_id IS NULL OR p_unit_config_id = FND_API.G_MISS_NUM OR
1849 p_end_date_time IS NULL OR p_end_date_time = FND_API.G_MISS_DATE
1850 )
1851 THEN
1852 FND_MESSAGE.SET_NAME('AHL', 'AHL_COM_INVALID_PROCEDURE_CALL');
1853 FND_MESSAGE.SET_TOKEN('PROCEDURE', l_api_name);
1854 FND_MSG_PUB.ADD;
1855 RAISE FND_API.G_EXC_ERROR;
1856 END IF;
1857
1858 /*
1859 -- 5. Validate unit configuration with unit_config_id = p_unit_config_id exists and is complete and active, using cursor check_unit_exists. If no record found, throw error "Unit Configuration does not exist or is not complete"
1860 OPEN check_unit_exists (p_unit_config_id);
1861 FETCH check_unit_exists INTO l_dummy_varchar;
1862 IF (check_unit_exists%NOTFOUND)
1863 THEN
1864 FND_MESSAGE.SET_NAME('AHL', 'AHL_UA_UNIT_ID_NOT_FOUND');
1865 FND_MSG_PUB.ADD;
1866 CLOSE check_unit_exists;
1867 RAISE FND_API.G_EXC_ERROR;
1868 END IF;
1869 CLOSE check_unit_exists;
1870 */
1871
1872 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1873 THEN
1874 fnd_log.string
1875 (
1876 fnd_log.level_statement,
1877 L_DEBUG_MODULE,
1878 'Basic validations done'
1879 );
1880 END IF;
1881
1882 x_is_conflict := FND_API.G_FALSE;
1883
1884 -- 6. If (p_use_actuals = FND_API.G_TRUE), open cursor get_succ_flight_act else open cursor get_succ_flight_est
1885 IF (p_use_actuals IS NOT NULL AND p_use_actuals = FND_API.G_TRUE)
1886 THEN
1887 -- 7. Fetch one record from cursor into and populate x_Flight_Schedule_Rec_Type with values from this record
1888 OPEN get_succ_flight_act (p_unit_config_id, p_end_date_time);
1889 FETCH get_succ_flight_act INTO l_act_flight_rec;
1890 IF (get_succ_flight_act%FOUND)
1891 THEN
1892 x_succ_flight_schedule.unit_schedule_id := l_act_flight_rec.unit_schedule_id;
1893 x_succ_flight_schedule.flight_number := l_act_flight_rec.flight_number;
1894 x_succ_flight_schedule.segment := l_act_flight_rec.segment;
1895 x_succ_flight_schedule.est_departure_time := l_act_flight_rec.est_departure_time;
1896 x_succ_flight_schedule.actual_departure_time := l_act_flight_rec.actual_departure_time;
1897 x_succ_flight_schedule.departure_dept_id := l_act_flight_rec.departure_dept_id;
1898 x_succ_flight_schedule.departure_dept_code := l_act_flight_rec.departure_dept_code;
1899 x_succ_flight_schedule.departure_org_id := l_act_flight_rec.departure_org_id;
1900 x_succ_flight_schedule.departure_org_code := l_act_flight_rec.departure_org_code;
1901 x_succ_flight_schedule.est_arrival_time := l_act_flight_rec.est_arrival_time;
1902 x_succ_flight_schedule.actual_arrival_time := l_act_flight_rec.actual_arrival_time;
1903 x_succ_flight_schedule.arrival_dept_id := l_act_flight_rec.arrival_dept_id;
1904 x_succ_flight_schedule.arrival_dept_code := l_act_flight_rec.arrival_dept_code;
1905 x_succ_flight_schedule.arrival_org_id := l_act_flight_rec.arrival_org_id;
1906 x_succ_flight_schedule.arrival_org_code := l_act_flight_rec.arrival_org_code;
1907 x_succ_flight_schedule.preceding_us_id := l_act_flight_rec.preceding_us_id;
1908 x_succ_flight_schedule.unit_config_header_id := l_act_flight_rec.unit_config_header_id;
1909 x_succ_flight_schedule.unit_config_name := l_act_flight_rec.unit_config_name;
1910 x_succ_flight_schedule.csi_instance_id := l_act_flight_rec.csi_item_instance_id;
1911 x_succ_flight_schedule.instance_number := l_act_flight_rec.instance_number;
1912 x_succ_flight_schedule.item_number := l_act_flight_rec.item_number;
1913 x_succ_flight_schedule.serial_number := l_act_flight_rec.serial_number;
1914 x_succ_flight_schedule.visit_reschedule_mode := l_act_flight_rec.visit_reschedule_mode;
1915 x_succ_flight_schedule.visit_reschedule_meaning := l_act_flight_rec.visit_reschedule_meaning;
1916 x_succ_flight_schedule.object_version_number := l_act_flight_rec.object_version_number;
1917 x_succ_flight_schedule.attribute_category := l_act_flight_rec.attribute_category;
1918 x_succ_flight_schedule.attribute1 := l_act_flight_rec.attribute1;
1919 x_succ_flight_schedule.attribute2 := l_act_flight_rec.attribute2;
1920 x_succ_flight_schedule.attribute3 := l_act_flight_rec.attribute3;
1921 x_succ_flight_schedule.attribute4 := l_act_flight_rec.attribute4;
1922 x_succ_flight_schedule.attribute5 := l_act_flight_rec.attribute5;
1923 x_succ_flight_schedule.attribute6 := l_act_flight_rec.attribute6;
1924 x_succ_flight_schedule.attribute7 := l_act_flight_rec.attribute7;
1925 x_succ_flight_schedule.attribute8 := l_act_flight_rec.attribute8;
1926 x_succ_flight_schedule.attribute9 := l_act_flight_rec.attribute9;
1927 x_succ_flight_schedule.attribute10 := l_act_flight_rec.attribute10;
1928 x_succ_flight_schedule.attribute11 := l_act_flight_rec.attribute11;
1929 x_succ_flight_schedule.attribute12 := l_act_flight_rec.attribute12;
1930 x_succ_flight_schedule.attribute13 := l_act_flight_rec.attribute13;
1931 x_succ_flight_schedule.attribute14 := l_act_flight_rec.attribute14;
1932 x_succ_flight_schedule.attribute15 := l_act_flight_rec.attribute15;
1933
1934 -- 8. Fetch another record from cursor, and if the record's arrival time (actual / estimated based on p_use_actuals) = previous record's arrival time, then populate x_is_conflict = FND_API.G_TRUE
1935 FETCH get_succ_flight_act INTO l_act_flight_rec;
1936 IF (
1937 get_succ_flight_act%FOUND
1938 AND
1942 AND
1939 nvl(l_act_flight_rec.actual_departure_time, l_act_flight_rec.est_departure_time) =
1940 nvl(x_succ_flight_schedule.actual_departure_time, x_succ_flight_schedule.est_departure_time)
1941 -- If both start times and end time match, then conflict...
1943 nvl(l_act_flight_rec.actual_arrival_time, l_act_flight_rec.est_arrival_time) =
1944 nvl(x_succ_flight_schedule.actual_arrival_time, x_succ_flight_schedule.est_arrival_time)
1945 )
1946 THEN
1947 x_is_conflict := FND_API.G_TRUE;
1948 END IF;
1949 END IF;
1950 CLOSE get_succ_flight_act;
1951 ELSE
1952 -- 7. Fetch one record from cursor into and populate x_Flight_Schedule_Rec_Type with values from this record
1953 OPEN get_succ_flight_est (p_unit_config_id, p_end_date_time);
1954 FETCH get_succ_flight_est INTO l_est_flight_rec;
1955 IF (get_succ_flight_est%FOUND)
1956 THEN
1957 x_succ_flight_schedule.unit_schedule_id := l_est_flight_rec.unit_schedule_id;
1958 x_succ_flight_schedule.flight_number := l_est_flight_rec.flight_number;
1959 x_succ_flight_schedule.segment := l_est_flight_rec.segment;
1960 x_succ_flight_schedule.est_departure_time := l_est_flight_rec.est_departure_time;
1961 x_succ_flight_schedule.actual_departure_time := l_est_flight_rec.actual_departure_time;
1962 x_succ_flight_schedule.departure_dept_id := l_est_flight_rec.departure_dept_id;
1963 x_succ_flight_schedule.departure_dept_code := l_est_flight_rec.departure_dept_code;
1964 x_succ_flight_schedule.departure_org_id := l_est_flight_rec.departure_org_id;
1965 x_succ_flight_schedule.departure_org_code := l_est_flight_rec.departure_org_code;
1966 x_succ_flight_schedule.est_arrival_time := l_est_flight_rec.est_arrival_time;
1967 x_succ_flight_schedule.actual_arrival_time := l_est_flight_rec.actual_arrival_time;
1968 x_succ_flight_schedule.arrival_dept_id := l_est_flight_rec.arrival_dept_id;
1969 x_succ_flight_schedule.arrival_dept_code := l_est_flight_rec.arrival_dept_code;
1970 x_succ_flight_schedule.arrival_org_id := l_est_flight_rec.arrival_org_id;
1971 x_succ_flight_schedule.arrival_org_code := l_est_flight_rec.arrival_org_code;
1972 x_succ_flight_schedule.preceding_us_id := l_est_flight_rec.preceding_us_id;
1973 x_succ_flight_schedule.unit_config_header_id := l_est_flight_rec.unit_config_header_id;
1974 x_succ_flight_schedule.unit_config_name := l_est_flight_rec.unit_config_name;
1975 x_succ_flight_schedule.csi_instance_id := l_est_flight_rec.csi_item_instance_id;
1976 x_succ_flight_schedule.instance_number := l_est_flight_rec.instance_number;
1977 x_succ_flight_schedule.item_number := l_est_flight_rec.item_number;
1978 x_succ_flight_schedule.serial_number := l_est_flight_rec.serial_number;
1979 x_succ_flight_schedule.visit_reschedule_mode := l_est_flight_rec.visit_reschedule_mode;
1980 x_succ_flight_schedule.visit_reschedule_meaning := l_est_flight_rec.visit_reschedule_meaning;
1981 x_succ_flight_schedule.object_version_number := l_est_flight_rec.object_version_number;
1982 x_succ_flight_schedule.attribute_category := l_est_flight_rec.attribute_category;
1983 x_succ_flight_schedule.attribute1 := l_est_flight_rec.attribute1;
1984 x_succ_flight_schedule.attribute2 := l_est_flight_rec.attribute2;
1985 x_succ_flight_schedule.attribute3 := l_est_flight_rec.attribute3;
1986 x_succ_flight_schedule.attribute4 := l_est_flight_rec.attribute4;
1987 x_succ_flight_schedule.attribute5 := l_est_flight_rec.attribute5;
1988 x_succ_flight_schedule.attribute6 := l_est_flight_rec.attribute6;
1989 x_succ_flight_schedule.attribute7 := l_est_flight_rec.attribute7;
1990 x_succ_flight_schedule.attribute8 := l_est_flight_rec.attribute8;
1991 x_succ_flight_schedule.attribute9 := l_est_flight_rec.attribute9;
1992 x_succ_flight_schedule.attribute10 := l_est_flight_rec.attribute10;
1993 x_succ_flight_schedule.attribute11 := l_est_flight_rec.attribute11;
1994 x_succ_flight_schedule.attribute12 := l_est_flight_rec.attribute12;
1995 x_succ_flight_schedule.attribute13 := l_est_flight_rec.attribute13;
1996 x_succ_flight_schedule.attribute14 := l_est_flight_rec.attribute14;
1997 x_succ_flight_schedule.attribute15 := l_est_flight_rec.attribute15;
1998
1999 -- 8. Fetch another record from cursor, and if the record's arrival time (actual / estimated based on p_use_actuals) = previous record's arrival time, then populate x_is_conflict = FND_API.G_TRUE
2000 FETCH get_succ_flight_est INTO l_est_flight_rec;
2001 IF (
2002 get_succ_flight_est%FOUND
2003 AND
2004 l_est_flight_rec.est_departure_time = x_succ_flight_schedule.est_departure_time
2005 -- If both start times and end time match, then conflict...
2006 AND
2007 l_est_flight_rec.est_arrival_time = x_succ_flight_schedule.est_arrival_time
2008 )
2009 THEN
2010 x_is_conflict := FND_API.G_TRUE;
2011 END IF;
2012 END IF;
2013 CLOSE get_succ_flight_est;
2014 END IF;
2015
2016 -- API body ends here
2017
2018 -- Log API exit point
2019 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2020 THEN
2021 fnd_log.string
2022 (
2023 fnd_log.level_procedure,
2024 L_DEBUG_MODULE||'.end',
2025 'At the end of PLSQL procedure'
2026 );
2027 END IF;
2028
2029 -- Check Error Message stack.
2030 x_msg_count := FND_MSG_PUB.count_msg;
2031 IF x_msg_count > 0
2032 THEN
2033 RAISE FND_API.G_EXC_ERROR;
2034 END IF;
2035
2036 -- Standard call to get message count and if count is 1, get message info
2037 FND_MSG_PUB.count_and_get
2038 (
2039 p_count => x_msg_count,
2040 p_data => x_msg_data,
2041 p_encoded => FND_API.G_FALSE
2042 );
2043
2044 EXCEPTION
2045 WHEN FND_API.G_EXC_ERROR THEN
2046 x_return_status := FND_API.G_RET_STS_ERROR;
2047 FND_MSG_PUB.count_and_get
2048 (
2049 p_count => x_msg_count,
2050 p_data => x_msg_data,
2051 p_encoded => FND_API.G_FALSE
2052 );
2053
2054 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2055 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2056 FND_MSG_PUB.count_and_get
2057 (
2058 p_count => x_msg_count,
2059 p_data => x_msg_data,
2060 p_encoded => FND_API.G_FALSE
2061 );
2062
2063 WHEN OTHERS THEN
2064 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2065 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2066 THEN
2067 FND_MSG_PUB.add_exc_msg
2068 (
2069 p_pkg_name => G_PKG_NAME,
2070 p_procedure_name => 'Get_Succ_Flight_Info',
2071 p_error_text => SUBSTR(SQLERRM,1,240)
2072 );
2073 END IF;
2074 FND_MSG_PUB.count_and_get
2075 (
2076 p_count => x_msg_count,
2077 p_data => x_msg_data,
2078 p_encoded => FND_API.G_FALSE
2079 );
2080 END Get_Succ_Flight_Info;
2081
2082 End AHL_UA_COMMON_PVT;
2083