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