[Home] [Help]
PACKAGE BODY: APPS.AHL_UA_FLIGHT_SCHEDULES_PVT
Source
1 PACKAGE BODY AHL_UA_FLIGHT_SCHEDULES_PVT AS
2 /* $Header: AHLVUFSB.pls 120.4 2006/09/15 23:17:34 sikumar noship $ */
3
4 G_USER_ID CONSTANT NUMBER := TO_NUMBER(FND_GLOBAL.USER_ID);
5 G_LOGIN_ID CONSTANT NUMBER := TO_NUMBER(FND_GLOBAL.LOGIN_ID);
6 G_SYSDATE CONSTANT DATE := SYSDATE;
7 --Flag for determining wether to use Actual dates or Estimated dates.
8 G_USE_ACTUALS CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
9
10 -- Internal record structure used to pass visit reschedule related details to
11 -- Visit sync procedure.
12 TYPE visit_sync_rec_type IS RECORD
13 (
14 UNIT_SCHEDULE_ID NUMBER,
15 CHANGED_ARRIVAL_TIME NUMBER,
16 CHANGED_ORG_ID NUMBER,
17 CHANGED_DEPT_ID NUMBER,
18 VISIT_RESCHEDULE_MODE VARCHAR2(30)
19 );
20
21 -- Record and table structure for storing Unit Schedule Records for finding out Preceeding US ID.
22 TYPE pre_us_rec_type IS RECORD
23 (
24 unit_schedule_id NUMBER,
25 preceding_us_id NUMBER,
26 arrival_time DATE
27 );
28 TYPE pre_us_tbl_type IS TABLE OF pre_us_rec_type INDEX BY BINARY_INTEGER;
29
30 G_PKG_NAME VARCHAR2(30):='AHL_UA_FLIGHT_SCHEDULES_PVT';
31
32 l_visit_tbl AHL_VWP_VISITS_PVT.Visit_Tbl_Type;
33 l_visit_count NUMBER;
34
35 CURSOR get_flight_visit
36 (
37 p_unit_schedule_id number
38 )
39 IS
40 SELECT *
41 FROM AHL_VISITS_B
42 WHERE unit_schedule_id = p_unit_schedule_id
43 AND STATUS_CODE NOT IN ('DELETED', 'CANCELLED' , 'CLOSED');
44
45
46 l_flight_visit get_flight_visit%ROWTYPE;
47
48
49
50
51 -----------------------------------------------------------------------------------------------------
52 -- Function for constructing record identifier for error messages.
53 -----------------------------------------------------------------------------------------------------
54
55 FUNCTION get_record_identifier(
56 p_flight_schedule_rec IN FLIGHT_SCHEDULE_REC_TYPE
57 )
58 RETURN VARCHAR2
59 IS
60 l_record_identifier VARCHAR2(200);
61 BEGIN
62 l_record_identifier := '';
63
64 IF p_flight_schedule_rec.UNIT_CONFIG_NAME IS NOT NULL
65 THEN
66 l_record_identifier := l_record_identifier||p_flight_schedule_rec.UNIT_CONFIG_NAME;
67 END IF;
68
69 IF p_flight_schedule_rec.FLIGHT_NUMBER IS NOT NULL
70 THEN
71 l_record_identifier := l_record_identifier||','||p_flight_schedule_rec.FLIGHT_NUMBER;
72 END IF;
73
74 IF p_flight_schedule_rec.SEGMENT IS NOT NULL
75 THEN
76 l_record_identifier := l_record_identifier||','||p_flight_schedule_rec.SEGMENT;
77 END IF;
78
79 RETURN l_record_identifier;
80
81 END get_record_identifier;
82
83 -----------------------------------------------------------------------------------------------------
84 -- Perform item level validation on the attributes of Unit Flight Schedule.
85 -----------------------------------------------------------------------------------------------------
86 PROCEDURE validate_attributes
87 (
88 p_flight_schedule_rec IN FLIGHT_SCHEDULE_REC_TYPE,
89 x_return_status OUT NOCOPY VARCHAR2
90 )
91 IS
92
93 -- Cursor for checking for the validity of the flight schedule
94 CURSOR get_cur_us_csr(p_unit_schedule_id IN NUMBER, p_object_version_number IN NUMBER)
95 IS
96 SELECT 'X'
97 FROM AHL_UNIT_SCHEDULES
98 WHERE unit_schedule_id = p_unit_schedule_id
99 AND object_version_number = p_object_version_number;
100
101 -- Define all local variables here.
102 l_api_name CONSTANT VARCHAR2(30) := 'validate_attributes';
103 l_dummy VARCHAR2(1);
104 l_record_identifier VARCHAR2(300);
105
106
107 BEGIN
108
109 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
110 fnd_log.string
111 (
112 fnd_log.level_procedure,
113 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
114 'At the start of '||l_api_name
115 );
116 END IF;
117
118 -- initialize return status to success at the begining
119 x_return_status := FND_API.G_RET_STS_SUCCESS;
120
121
122 l_record_identifier := get_record_identifier(
123 p_flight_schedule_rec => p_flight_schedule_rec
124 );
125
126 --Validate DML flag
127 IF (
128 p_flight_schedule_rec.DML_OPERATION <> 'D' AND
129 p_flight_schedule_rec.DML_OPERATION <> 'U' AND
130 p_flight_schedule_rec.DML_OPERATION <> 'C'
131 )
132 THEN
133 FND_MESSAGE.set_name( 'AHL','AHL_COM_INVALID_DML' );
134 FND_MESSAGE.set_token( 'FIELD', p_flight_schedule_rec.DML_OPERATION);
135 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
136 FND_MSG_PUB.add;
137 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
138 fnd_log.string
139 (
140 fnd_log.level_error,
141 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
142 'DML Operation specified is invalid for '
143 ||p_flight_schedule_rec.unit_schedule_id
144 );
145 END IF;
146 x_return_status := FND_API.G_RET_STS_ERROR;
147 RAISE FND_API.G_EXC_ERROR;
148 END IF;
149
150
151 --Obj version number and Unit Schedule id check in case of update or delete.
152 IF (
153 p_flight_schedule_rec.DML_OPERATION = 'D' OR
154 p_flight_schedule_rec.DML_OPERATION = 'U'
155 )
156 THEN
157 --Unit Schedule id cannot be null
158 IF p_flight_schedule_rec.UNIT_SCHEDULE_ID IS NULL OR
159 p_flight_schedule_rec.UNIT_SCHEDULE_ID = FND_API.G_MISS_NUM
160 THEN
161 FND_MESSAGE.set_name( 'AHL','AHL_UA_US_NOT_FOUND' );
162 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
163 FND_MSG_PUB.add;
164 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
165 fnd_log.string
166 (
167 fnd_log.level_error,
168 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
169 'Unit Schedule ID is null for '
170 ||p_flight_schedule_rec.unit_schedule_id
171 );
172 END IF;
173 x_return_status := FND_API.G_RET_STS_ERROR;
174 RAISE FND_API.G_EXC_ERROR;
175 END IF;
176
177 -- Check for Object Version number.
178 IF ( p_flight_schedule_rec.OBJECT_VERSION_NUMBER IS NULL OR
179 p_flight_schedule_rec.OBJECT_VERSION_NUMBER = FND_API.G_MISS_NUM )
180 THEN
181 FND_MESSAGE.set_name( 'AHL','AHL_UA_OBJ_VERNO_NULL' );
182 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
183 FND_MSG_PUB.add;
184 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
185 fnd_log.string
186 (
187 fnd_log.level_error,
188 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
189 'Object Version Number is null for '
190 ||p_flight_schedule_rec.unit_schedule_id
191 );
192 END IF;
193 x_return_status := FND_API.G_RET_STS_ERROR;
194 RAISE FND_API.G_EXC_ERROR;
195 END IF;
196
197 -- Check if unit schedule rec is valid.
198 OPEN get_cur_us_csr(p_flight_schedule_rec.unit_schedule_id, p_flight_schedule_rec.object_version_number);
199 FETCH get_cur_us_csr INTO l_dummy;
200 IF get_cur_us_csr%NOTFOUND
201 THEN
202 FND_MESSAGE.set_name( 'AHL','AHL_UA_REC_CHANGED' );
203 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
204 FND_MSG_PUB.add;
205 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
206 fnd_log.string
207 (
208 fnd_log.level_error,
209 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
210 'Unit Schedule record is not valid ->'
211 ||p_flight_schedule_rec.unit_schedule_id
212 );
213 END IF;
214 x_return_status := FND_API.G_RET_STS_ERROR;
215 RAISE FND_API.G_EXC_ERROR;
216 END IF;
217 END IF;
218
219 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
220 fnd_log.string
221 (
222 fnd_log.level_procedure,
223 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.end',
224 'At the end of '||l_api_name
225 );
226 END IF;
227
228 END validate_attributes;
229
230 -----------------------------------------------------------------------------------------------------
231 -- Procedure for converting values to ids
232 -----------------------------------------------------------------------------------------------------
233 PROCEDURE convert_values_to_ids
234 (
235 p_x_flight_schedule_rec IN OUT NOCOPY FLIGHT_SCHEDULE_REC_TYPE,
236 x_return_status OUT NOCOPY VARCHAR2
237 )
238 IS
239
240 -- Cursor for getting unit config id from unit config name
241 CURSOR uc_name_to_id_csr(p_uc_name IN VARCHAR2)
242 IS
243 SELECT unit_config_header_id
244 FROM AHL_UNIT_CONFIG_HEADERS
245 WHERE name = p_uc_name
246 --priyan Bug # 5303188
247 -- AND ahl_util_uc_pkg.get_uc_status_code (unit_config_header_id) IN ('COMPLETE', 'INCOMPLETE');
248 --AND unit_config_status_code in ('COMPLETE','INCOMPLETE');
249 --AND TRUNC(NVL(active_end_date,sysdate+1)) > TRUNC(sysdate);
250 -- fix for bug number 5528416
251 AND ahl_util_uc_pkg.get_uc_status_code (unit_config_header_id) NOT IN ('DRAFT', 'EXPIRED');
252
253
254 -- Cursor for getting org id from org name
255 CURSOR org_name_to_id_csr(p_org_code IN VARCHAR2)
256 IS
257 SELECT mtlp.organization_id
258 FROM MTL_PARAMETERS mtlp
259 WHERE mtlp.eam_enabled_flag = 'Y'
260 AND mtlp.organization_code = p_org_code;
261
262 -- Cursor for getting org id from org name
263 CURSOR dept_name_to_id_csr(p_dept_code IN VARCHAR2, p_org_id IN NUMBER)
264 IS
265 SELECT DEPARTMENT_ID
266 FROM BOM_DEPARTMENTS BD
267 WHERE BD.DEPARTMENT_CODE = p_dept_code AND
268 BD.ORGANIZATION_ID = p_org_id;
269
270 -- Define all local variables here.
271 l_api_name CONSTANT VARCHAR2(30) := 'convert_values_to_ids';
272 l_record_identifier VARCHAR2(150);
273 l_msg_data VARCHAR2(30);
274
275 BEGIN
276
277 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
278 fnd_log.string
279 (
280 fnd_log.level_procedure,
281 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
282 'At the start of '||l_api_name
283 );
284 END IF;
285
286 -- Initialize return status to success at the begining
287 x_return_status := FND_API.G_RET_STS_SUCCESS;
288
289 l_record_identifier := get_record_identifier(
290 p_flight_schedule_rec => p_x_flight_schedule_rec
291 );
292
293 -- convert unit name(UC Name) to UC header id
294 IF /*(
295 p_x_flight_schedule_rec.unit_config_header_id IS NULL OR
296 p_x_flight_schedule_rec.unit_config_header_id = FND_API.G_MISS_NUM
297 )
298 AND*/
299 (
300 p_x_flight_schedule_rec.unit_config_name IS NOT NULL AND
301 p_x_flight_schedule_rec.unit_config_name <> FND_API.G_MISS_CHAR
302 )
303 THEN
304
305 OPEN uc_name_to_id_csr(p_x_flight_schedule_rec.unit_config_name);
306 FETCH uc_name_to_id_csr INTO p_x_flight_schedule_rec.unit_config_header_id;
307 IF uc_name_to_id_csr%NOTFOUND THEN
308 FND_MESSAGE.SET_NAME('AHL','AHL_UA_INV_UC_NAME');
309 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
310 FND_MSG_PUB.ADD;
311 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
312 fnd_log.string
313 (
314 fnd_log.level_error,
315 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
316 'Unit Config name specified for '||p_x_flight_schedule_rec.unit_schedule_id
317 ||' is invalid'
318 );
319 END IF;
320 x_return_status := FND_API.G_RET_STS_ERROR;
321 END IF;
322 CLOSE uc_name_to_id_csr;
323
324 END IF;
325
326 -- convert arrival org
327 IF/*(
328 p_x_flight_schedule_rec.arrival_org_id IS NULL OR
329 p_x_flight_schedule_rec.arrival_org_id = FND_API.G_MISS_NUM
330 )
331 AND*/
332 (
333 p_x_flight_schedule_rec.ARRIVAL_ORG_CODE IS NOT NULL AND
334 p_x_flight_schedule_rec.ARRIVAL_ORG_CODE <> FND_API.G_MISS_CHAR
335 )
336 THEN
337 OPEN org_name_to_id_csr(p_x_flight_schedule_rec.ARRIVAL_ORG_CODE);
338 FETCH org_name_to_id_csr INTO p_x_flight_schedule_rec.arrival_org_id;
339 IF org_name_to_id_csr%NOTFOUND THEN
340 FND_MESSAGE.SET_NAME('AHL','AHL_UA_INV_ARR_ORG');
341 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
342 FND_MSG_PUB.ADD;
343 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
344 fnd_log.string
345 (
346 fnd_log.level_error,
347 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
348 'Arrival Org code specified for '||p_x_flight_schedule_rec.unit_schedule_id
349 ||' is invalid'
350 );
351 END IF;
352 x_return_status := FND_API.G_RET_STS_ERROR;
353 END IF;
354 CLOSE org_name_to_id_csr;
355 END IF;
356
357 -- convert arrival department
358 IF /*(
359 p_x_flight_schedule_rec.arrival_dept_id IS NULL OR
360 p_x_flight_schedule_rec.arrival_dept_id = FND_API.G_MISS_NUM
361 )
362 AND*/
363 (
364 p_x_flight_schedule_rec.arrival_dept_code IS NOT NULL AND
365 p_x_flight_schedule_rec.arrival_dept_code <> FND_API.G_MISS_CHAR
366 )
367 AND
368 (
369 p_x_flight_schedule_rec.arrival_org_id IS NOT NULL AND
370 p_x_flight_schedule_rec.arrival_org_id <> FND_API.G_MISS_NUM
371 )
372 THEN
373 OPEN dept_name_to_id_csr(p_x_flight_schedule_rec.arrival_dept_code,
374 p_x_flight_schedule_rec.ARRIVAL_ORG_ID);
375 FETCH dept_name_to_id_csr INTO p_x_flight_schedule_rec.arrival_dept_id;
376 IF dept_name_to_id_csr%NOTFOUND THEN
377 FND_MESSAGE.SET_NAME('AHL','AHL_UA_INV_ARR_DEPT');
378 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
379 FND_MSG_PUB.ADD;
380 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
381 fnd_log.string
382 (
383 fnd_log.level_error,
384 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
385 'Arrival Department code specified for '||p_x_flight_schedule_rec.unit_schedule_id
386 ||' is invalid'
387 );
388 END IF;
389 x_return_status := FND_API.G_RET_STS_ERROR;
390 END IF;
391 CLOSE dept_name_to_id_csr;
392 END IF;
393
394
395 -- convert departure org
396 IF /*(
397 p_x_flight_schedule_rec.departure_org_id IS NULL OR
398 p_x_flight_schedule_rec.departure_org_id = FND_API.G_MISS_NUM
399 )
400 AND*/
401 (
402 p_x_flight_schedule_rec.departure_org_code IS NOT NULL AND
403 p_x_flight_schedule_rec.departure_org_code <> FND_API.G_MISS_CHAR
404 )
405 THEN
406 OPEN org_name_to_id_csr(p_x_flight_schedule_rec.departure_org_code);
407 FETCH org_name_to_id_csr INTO p_x_flight_schedule_rec.departure_org_id;
408 IF org_name_to_id_csr%NOTFOUND THEN
409 FND_MESSAGE.SET_NAME('AHL','AHL_UA_INV_DEP_ORG');
410 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
411 FND_MSG_PUB.ADD;
412 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
413 fnd_log.string
414 (
415 fnd_log.level_error,
416 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
417 'Departure org code specified for '||p_x_flight_schedule_rec.unit_schedule_id
418 ||' is invalid'
419 );
420 END IF;
421 x_return_status := FND_API.G_RET_STS_ERROR;
422 END IF;
423 CLOSE org_name_to_id_csr;
424 END IF;
425
426 -- convert departure department
427 IF /*(
428 p_x_flight_schedule_rec.departure_dept_id IS NULL OR
429 p_x_flight_schedule_rec.departure_dept_id = FND_API.G_MISS_NUM
430 )
431 AND*/
432 (
433 p_x_flight_schedule_rec.departure_dept_code IS NOT NULL AND
434 p_x_flight_schedule_rec.departure_dept_code <> FND_API.G_MISS_CHAR
435 )
436 AND
437 (
438 p_x_flight_schedule_rec.departure_org_id IS NOT NULL AND
439 p_x_flight_schedule_rec.departure_org_id <> FND_API.G_MISS_NUM
440 )
441 THEN
442 OPEN dept_name_to_id_csr(p_x_flight_schedule_rec.departure_dept_code,
443 p_x_flight_schedule_rec.departure_org_id);
444 FETCH dept_name_to_id_csr INTO p_x_flight_schedule_rec.departure_dept_id;
445 IF dept_name_to_id_csr%NOTFOUND THEN
446 FND_MESSAGE.SET_NAME('AHL','AHL_UA_INV_DEP_DEPT');
447 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
448 FND_MSG_PUB.ADD;
449 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
450 fnd_log.string
451 (
452 fnd_log.level_error,
453 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
454 'Departure department code specified for '||p_x_flight_schedule_rec.unit_schedule_id
455 ||' is invalid'
456 );
457 END IF;
458 x_return_status := FND_API.G_RET_STS_ERROR;
459 END IF;
460 CLOSE dept_name_to_id_csr;
461 END IF;
462
463 -- validate visit synchronization rule lookup type.
464 IF /*(
465 p_x_flight_schedule_rec.VISIT_RESCHEDULE_MODE IS NOT NULL AND
466 p_x_flight_schedule_rec.VISIT_RESCHEDULE_MODE <> FND_API.G_MISS_CHAR
467 )
468 OR*/
469 (
470 p_x_flight_schedule_rec.VISIT_RESCHEDULE_MEANING IS NOT NULL AND
471 p_x_flight_schedule_rec.VISIT_RESCHEDULE_MEANING <> FND_API.G_MISS_CHAR
472 )
473
474 THEN
475 AHL_RM_ROUTE_UTIL.validate_lookup(
476 x_return_status => x_return_status,
477 x_msg_data => l_msg_data,
478 p_lookup_type => 'AHL_UA_VISIT_SYNC_RULE',
479 p_lookup_meaning => p_x_flight_schedule_rec.VISIT_RESCHEDULE_MEANING,
480 p_x_lookup_code => p_x_flight_schedule_rec.VISIT_RESCHEDULE_MODE
481 );
482
483 -- If any severe error occurs, then, abort API.
484 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
485 FND_MESSAGE.SET_NAME('AHL','AHL_UA_INV_RESCH_MODE');
486 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
487 FND_MSG_PUB.ADD;
488 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
489 fnd_log.string
490 (
491 fnd_log.level_error,
492 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
493 'visit reschedule mode specified for '||p_x_flight_schedule_rec.unit_schedule_id
494 ||' is invalid'
495 );
496 END IF;
497 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
498 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
499 END IF;
500 END IF;
501
502 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
503 fnd_log.string
504 (
505 fnd_log.level_procedure,
506 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.end',
507 'At the end of '||l_api_name
508 );
509 END IF;
510
511
512 END convert_values_to_ids;
513
514 PROCEDURE default_unchanged_attributes
515 (
516 p_x_flight_schedule_rec IN OUT NOCOPY FLIGHT_SCHEDULE_REC_TYPE
517 )
518 IS
519
520 -- Cursor for getting a Unit Schedule record.
521 CURSOR get_current_us_csr(p_unit_shcedule_id IN NUMBER) IS
522 SELECT *
523 FROM AHL_UNIT_SCHEDULES
524 WHERE unit_schedule_id = p_unit_shcedule_id;
525
526 l_current_us_rec get_current_us_csr%ROWTYPE;
527
528 -- Define all local variables here.
529 l_api_name CONSTANT VARCHAR2(30) := 'default_unchanged_attributes';
530 l_record_identifier VARCHAR2(150);
531
532 BEGIN
533
534 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
535 fnd_log.string
536 (
537 fnd_log.level_procedure,
538 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
539 'At the start of '||l_api_name
540 );
541 END IF;
542
543 --get current unit_schedule record
544 OPEN get_current_us_csr(p_x_flight_schedule_rec.unit_schedule_id);
545 FETCH get_current_us_csr INTO l_current_us_rec;
546
547 CLOSE get_current_us_csr;
548
549
550 -- Default Unit Config Header id.
551 IF (p_x_flight_schedule_rec.unit_config_header_id IS NULL )
552 THEN
553 p_x_flight_schedule_rec.unit_config_header_id := l_current_us_rec.unit_config_header_id;
554 ELSIF p_x_flight_schedule_rec.unit_config_header_id = FND_API.G_MISS_NUM THEN
555 p_x_flight_schedule_rec.unit_config_header_id := NULL;
556 END IF;
557
558 -- Default Flight Number
559 IF ( p_x_flight_schedule_rec.flight_number IS NULL ) THEN
560 p_x_flight_schedule_rec.flight_number := l_current_us_rec.flight_number;
561 ELSIF p_x_flight_schedule_rec.flight_number = FND_API.G_MISS_CHAR THEN
562 p_x_flight_schedule_rec.flight_number := NULL;
563 END IF;
564
565 -- Default segment
566 IF ( p_x_flight_schedule_rec.segment IS NULL ) THEN
567 p_x_flight_schedule_rec.segment := l_current_us_rec.segment;
568 ELSIF p_x_flight_schedule_rec.segment = FND_API.G_MISS_CHAR THEN
569 p_x_flight_schedule_rec.segment := NULL;
570 END IF;
571
572 -- Default departure dept id.
573 IF ( p_x_flight_schedule_rec.departure_dept_id IS NULL ) THEN
574 p_x_flight_schedule_rec.departure_dept_id := l_current_us_rec.departure_dept_id;
575 ELSIF p_x_flight_schedule_rec.departure_dept_id = FND_API.G_MISS_NUM THEN
576 p_x_flight_schedule_rec.departure_dept_id := NULL;
577 END IF;
578
579 -- Default departure org id.
580 IF ( p_x_flight_schedule_rec.departure_org_id IS NULL ) THEN
581 p_x_flight_schedule_rec.departure_org_id := l_current_us_rec.departure_org_id;
582 ELSIF ( p_x_flight_schedule_rec.departure_org_id = FND_API.G_MISS_NUM ) THEN
583 p_x_flight_schedule_rec.departure_org_id := NULL;
584 END IF;
585
586 -- Default arrival dept id.
587 IF ( p_x_flight_schedule_rec.arrival_dept_id IS NULL ) THEN
588 p_x_flight_schedule_rec.arrival_dept_id := l_current_us_rec.arrival_dept_id;
589 ELSIF p_x_flight_schedule_rec.arrival_dept_id = FND_API.G_MISS_NUM THEN
590 p_x_flight_schedule_rec.arrival_dept_id := NULL;
591 END IF;
592
593 -- Default arrival org is updated
594 IF ( p_x_flight_schedule_rec.arrival_org_id IS NULL ) THEN
595 p_x_flight_schedule_rec.arrival_org_id := l_current_us_rec.arrival_org_id;
596 ELSIF p_x_flight_schedule_rec.arrival_org_id = FND_API.G_MISS_NUM THEN
597 p_x_flight_schedule_rec.arrival_org_id := NULL;
598 END IF;
599
600 -- Default estimated departure time.
601 IF ( p_x_flight_schedule_rec.est_departure_time IS NULL ) THEN
602 p_x_flight_schedule_rec.est_departure_time := l_current_us_rec.est_departure_time;
603 ELSIF p_x_flight_schedule_rec.est_departure_time = FND_API.G_MISS_DATE THEN
604 p_x_flight_schedule_rec.est_departure_time := NULL;
605 END IF;
606
607 -- Default estimated Arrival time.
608 IF ( p_x_flight_schedule_rec.est_arrival_time IS NULL ) THEN
609 p_x_flight_schedule_rec.est_arrival_time := l_current_us_rec.est_arrival_time;
610 ELSIF p_x_flight_schedule_rec.est_arrival_time = FND_API.G_MISS_DATE THEN
611 p_x_flight_schedule_rec.est_arrival_time := NULL;
612 END IF;
613
614 -- default actual_departure_time
615 IF ( p_x_flight_schedule_rec.actual_departure_time IS NULL ) THEN
616 p_x_flight_schedule_rec.actual_departure_time := l_current_us_rec.actual_departure_time;
617 ELSIF p_x_flight_schedule_rec.actual_departure_time = FND_API.G_MISS_DATE
618 THEN
619 p_x_flight_schedule_rec.actual_departure_time := NULL;
620 END IF;
621
622 -- default actual_arrival_time
623 IF ( p_x_flight_schedule_rec.actual_arrival_time IS NULL ) THEN
624 p_x_flight_schedule_rec.actual_arrival_time := l_current_us_rec.actual_arrival_time;
625 ELSIF p_x_flight_schedule_rec.actual_arrival_time = FND_API.G_MISS_DATE
626 THEN
627 p_x_flight_schedule_rec.actual_arrival_time := NULL;
628 END IF;
629
630 -- default preceding_us_id
631 IF ( p_x_flight_schedule_rec.preceding_us_id IS NULL ) THEN
632 p_x_flight_schedule_rec.preceding_us_id := l_current_us_rec.preceding_us_id;
633 ELSIF p_x_flight_schedule_rec.preceding_us_id = FND_API.G_MISS_NUM
634 THEN
635 p_x_flight_schedule_rec.preceding_us_id := NULL;
636 END IF;
637
638 -- default visit_reschedule_mode
639 IF ( p_x_flight_schedule_rec.visit_reschedule_mode IS NULL ) THEN
640 p_x_flight_schedule_rec.visit_reschedule_mode := l_current_us_rec.visit_reschedule_mode;
641 ELSIF p_x_flight_schedule_rec.visit_reschedule_mode = FND_API.G_MISS_CHAR
642 THEN
643 p_x_flight_schedule_rec.visit_reschedule_mode := NULL;
644 END IF;
645
646 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
647 fnd_log.string
648 (
649 fnd_log.level_procedure,
650 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.end',
651 'At the end of '||l_api_name
652 );
653 END IF;
654
655 END default_unchanged_attributes;
656
657 ---------------------------------------------------------------------------------------------------------
658 -- Procedure which validates all mandatory input fields.
659 -- This needs to be done before any further validation.
660 -- Note: This cannot be done at the start of the procedure, since we are allowing defaulting of attributes
661 -- in case of update. So this can be performed only after default_missing_attributes.
662 ----------------------------------------------------------------------------------------------------------
663 PROCEDURE validate_mandatory_fields
664 (
665 p_flight_schedule_rec IN FLIGHT_SCHEDULE_REC_TYPE,
666 x_return_status OUT NOCOPY VARCHAR2
667 )
668 IS
669
670 -- Define all local variables here.
671 l_api_name CONSTANT VARCHAR2(30) := 'validate_mandatory_fields';
672 l_record_identifier VARCHAR2(150);
673
674 BEGIN
675
676 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
677 fnd_log.string
678 (
679 fnd_log.level_procedure,
680 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
681 'At the start of '||l_api_name
682 );
683 END IF;
684
685 -- Initialize return status to success at the begining
686 x_return_status := FND_API.G_RET_STS_SUCCESS;
687
688 l_record_identifier := get_record_identifier(
689 p_flight_schedule_rec => p_flight_schedule_rec
690 );
691
692 -- Unit Config Header is Mandatory input Field and cannot be null
693 IF (
694 p_flight_schedule_rec.unit_config_header_id IS NULL OR
695 p_flight_schedule_rec.unit_config_header_id = FND_API.G_MISS_NUM
696 )
697 THEN
698 FND_MESSAGE.set_name( 'AHL','AHL_UA_INV_UC_NAME' );
699 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
700 FND_MSG_PUB.add;
701 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
702 fnd_log.string
703 (
704 fnd_log.level_error,
705 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
706 'Unit Config Header id is a mandatory input field and cannot be null for '
707 ||p_flight_schedule_rec.unit_schedule_id
708 );
709 END IF;
710 x_return_status := FND_API.G_RET_STS_ERROR;
711 END IF;
712
713 -- Flight Number is a mandatory input field and cannot be null
714 IF (
715 p_flight_schedule_rec.flight_number IS NULL OR
716 p_flight_schedule_rec.flight_number = FND_API.G_MISS_CHAR
717 )
718 THEN
719 FND_MESSAGE.set_name( 'AHL', 'AHL_UA_FLG_NUMBER_NULL' );
720 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
721 FND_MSG_PUB.add;
722 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
723 fnd_log.string
724 (
725 fnd_log.level_error,
726 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
727 'Flight Number cannot be null for '
728 ||p_flight_schedule_rec.unit_schedule_id
729 );
730 END IF;
731 x_return_status := FND_API.G_RET_STS_ERROR;
732 --RAISE FND_API.G_EXC_ERROR;
733 END IF;
734
735 -- Segment is a mandatory input field and cannot be null
736 IF (
737 p_flight_schedule_rec.segment IS NULL OR
738 p_flight_schedule_rec.segment = FND_API.G_MISS_CHAR
739 )
740 THEN
741 FND_MESSAGE.set_name( 'AHL', 'AHL_UA_SEGMENT_NULL' );
742 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
743 FND_MSG_PUB.add;
744 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
745 fnd_log.string
746 (
747 fnd_log.level_error,
748 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
749 'Segment cannot be null for '
750 ||p_flight_schedule_rec.unit_schedule_id
751 );
752 END IF;
753 x_return_status := FND_API.G_RET_STS_ERROR;
754 --RAISE FND_API.G_EXC_ERROR;
755 END IF;
756
757 -- Departure department is a mandatory input field and cannot be null
758 IF (
759 p_flight_schedule_rec.departure_dept_id IS NULL OR
760 p_flight_schedule_rec.departure_dept_id = FND_API.G_MISS_NUM
761 )
762 THEN
763 FND_MESSAGE.set_name( 'AHL','AHL_UA_DEP_DEPT_NULL' );
764 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
765 FND_MSG_PUB.add;
766 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
767 fnd_log.string
768 (
769 fnd_log.level_error,
770 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
771 'Departure_Dept_Id is a mandatory input field and cannot be null for '
772 ||p_flight_schedule_rec.unit_schedule_id
773 );
774 END IF;
775 x_return_status := FND_API.G_RET_STS_ERROR;
776 END IF;
777
778 -- Departure Organization is a mandatory input field and cannot be null
779 IF (
780 p_flight_schedule_rec.departure_org_id IS NULL OR
781 p_flight_schedule_rec.departure_org_id = FND_API.G_MISS_NUM
782 )
783 THEN
784 FND_MESSAGE.set_name( 'AHL','AHL_UA_DEP_ORG_NULL' );
785 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
786 FND_MSG_PUB.add;
787 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
788 fnd_log.string
789 (
790 fnd_log.level_error,
791 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
792 'Departure_Org_Id is a mandatory input field and cannot be null for '
793 ||p_flight_schedule_rec.unit_schedule_id
794 );
795 END IF;
796 x_return_status := FND_API.G_RET_STS_ERROR;
797 END IF;
798
799 -- Arrival Department is a mandatory input field and cannot be null
800 IF (
801 p_flight_schedule_rec.arrival_dept_id IS NULL OR
802 p_flight_schedule_rec.arrival_dept_id = FND_API.G_MISS_NUM
803 )
804 THEN
805 FND_MESSAGE.set_name( 'AHL','AHL_UA_ARR_DEPT_NULL' );
806 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
807 FND_MSG_PUB.add;
808 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
809 fnd_log.string
810 (
811 fnd_log.level_error,
812 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
813 'Arrival_Dept_Id is a mandatory input field and cannot be null for '
814 ||p_flight_schedule_rec.unit_schedule_id
815 );
816 END IF;
817 x_return_status := FND_API.G_RET_STS_ERROR;
818 END IF;
819
820 -- Arrival Organization is a mandatory input field and cannot be null
821 IF (
822 p_flight_schedule_rec.arrival_org_id IS NULL OR
823 p_flight_schedule_rec.arrival_org_id = FND_API.G_MISS_NUM
824 )
825 THEN
826 FND_MESSAGE.set_name( 'AHL','AHL_UA_ARR_ORG_NULL' );
827 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
828 FND_MSG_PUB.add;
829 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
830 fnd_log.string
831 (
832 fnd_log.level_error,
833 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
834 'Arrival_Org_Id is a mandatory input field and cannot be null for '
835 ||p_flight_schedule_rec.unit_schedule_id
836 );
837 END IF;
838 x_return_status := FND_API.G_RET_STS_ERROR;
839 END IF;
840
841 -- Estimated Departure Time is a mandatory input field and cannot be null
842 IF (
843 p_flight_schedule_rec.est_departure_time IS NULL OR
844 p_flight_schedule_rec.est_departure_time = FND_API.G_MISS_DATE
845 )
846 THEN
847 FND_MESSAGE.set_name( 'AHL','AHL_UA_EST_DEP_TIME_NULL' );
848 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
849 FND_MSG_PUB.add;
850 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
851 fnd_log.string
852 (
853 fnd_log.level_error,
854 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
855 'Est_Departure_Time is a mandatory input field and cannot be null for '
856 ||p_flight_schedule_rec.unit_schedule_id
857 );
858 END IF;
859 x_return_status := FND_API.G_RET_STS_ERROR;
860 END IF;
861
862 -- Estimated Arrival Time is a mandatory input field and cannot be null
863 IF (
864 p_flight_schedule_rec.est_arrival_time IS NULL OR
865 p_flight_schedule_rec.est_arrival_time = FND_API.G_MISS_DATE
866 )
867 THEN
868 FND_MESSAGE.set_name( 'AHL','AHL_UA_EST_ARR_TIME_NULL' );
869 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
870 FND_MSG_PUB.add;
871 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
872 fnd_log.string
873 (
874 fnd_log.level_error,
875 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
876 'Est_Arrival_Time is a mandatory input field and cannot be null for '
877 ||p_flight_schedule_rec.unit_schedule_id
878 );
879 END IF;
880 x_return_status := FND_API.G_RET_STS_ERROR;
881 END IF;
882
883 -- Visit Reschedule Mode is a mandatory input field and cannot be null
884 IF (
885 p_flight_schedule_rec.visit_reschedule_mode IS NULL OR
886 p_flight_schedule_rec.visit_reschedule_mode = FND_API.G_MISS_CHAR
887 )
888 THEN
889 FND_MESSAGE.set_name( 'AHL','AHL_UA_VST_RES_MODE_NULL' );
890 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
891 FND_MSG_PUB.add;
892 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
893 fnd_log.string
894 (
895 fnd_log.level_error,
896 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
897 'Visit_Reschedule_Mode is a mandatory input field and cannot be null for '
898 ||p_flight_schedule_rec.unit_schedule_id
899 );
900 END IF;
901 x_return_status := FND_API.G_RET_STS_ERROR;
902 --RAISE FND_API.G_EXC_ERROR;
903 END IF;
904
905 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
906 fnd_log.string
907 (
908 fnd_log.level_procedure,
909 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.end',
910 'At the end of '||l_api_name
911 );
912 END IF;
913
914 END validate_mandatory_fields;
915
916 -----------------------------------------------------------------------------------------------------
917 -- Procedure which checks if update is allowed in Flight Schedule fields
918 -----------------------------------------------------------------------------------------------------
919 PROCEDURE validate_update(
920 p_x_flight_schedule_rec IN OUT NOCOPY FLIGHT_SCHEDULE_REC_TYPE,
921 x_return_status OUT NOCOPY VARCHAR2
922 )
923 IS
924 -- Cursor for getting a Unit Schedule record.
925 CURSOR get_current_us_csr(p_unit_shcedule_id IN NUMBER) IS
926 SELECT *
927 FROM AHL_UNIT_SCHEDULES
928 WHERE unit_schedule_id = p_unit_shcedule_id;
929
930 -- Define all local variables here.
931 l_api_name CONSTANT VARCHAR2(30) := 'validate_update';
932 l_current_us_rec get_current_us_csr%ROWTYPE;
933 l_update_allowed VARCHAR2(1);
934 l_record_identifier VARCHAR2(150);
935
936 BEGIN
937
938 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
939 fnd_log.string
940 (
941 fnd_log.level_procedure,
942 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.end',
943 'At the end of '||l_api_name
944 );
945 END IF;
946
947 --Initialize x_return_status to success at the start of the procedure
948 x_return_status := FND_API.G_RET_STS_SUCCESS;
949
950 --Check if update is allowed for the record
951 l_update_allowed := is_update_allowed(
952 p_x_flight_schedule_rec.unit_schedule_id,
953 is_super_user
954 );
955
956 -- returns a string with Unit Name, Flight Number and Segment of current record.
957 -- This is used as a token in error messages
958 l_record_identifier := get_record_identifier(
959 p_flight_schedule_rec => p_x_flight_schedule_rec
960 );
961
962 --get current unit_schedule record
963 OPEN get_current_us_csr(p_x_flight_schedule_rec.unit_schedule_id);
964 FETCH get_current_us_csr INTO l_current_us_rec;
965 IF get_current_us_csr%NOTFOUND
966 THEN
967 FND_MESSAGE.set_name( 'AHL', 'AHL_UA_US_NOT_FOUND' );
968 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
969 FND_MSG_PUB.add;
970 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
971 fnd_log.string
972 (
973 fnd_log.level_error,
974 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
975 'Flight schedule record not found for id '
976 ||p_x_flight_schedule_rec.unit_schedule_id
977 );
978 END IF;
979 CLOSE get_current_us_csr;
980 x_return_status := FND_API.G_RET_STS_ERROR;
981 --RAISE FND_API.G_EXC_ERROR;
982 END IF;
983
984 CLOSE get_current_us_csr;
985
986 -- Unit cannot be updated irrespective of the whether the user is super user or not
987 IF p_x_flight_schedule_rec.unit_config_header_id <> l_current_us_rec.unit_config_header_id
988 THEN
989 FND_MESSAGE.set_name( 'AHL','AHL_UA_UNIT_NO_UPDATE' );
990 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
991 FND_MSG_PUB.add;
992 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
993 fnd_log.string
994 (
995 fnd_log.level_error,
996 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
997 'Unit config header id cannot be updated for '
998 ||p_x_flight_schedule_rec.unit_schedule_id
999 );
1000 END IF;
1001 x_return_status := FND_API.G_RET_STS_ERROR;
1002 --RAISE FND_API.G_EXC_ERROR;
1003 END IF;
1004
1005 ----------------------------------------------------------------------------------------------------
1006 /* --Un comment the code if this is required
1007 -- Flight Number cannot be updated unless user is super user.
1008 IF p_x_flight_schedule_rec.flight_number <> l_current_us_rec.flight_number
1009 THEN
1010 FND_MESSAGE.set_name( 'AHL','AHL_UA_FLIGHT_NO_UPDATE' );
1011 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
1012 FND_MSG_PUB.add;
1013 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1014 fnd_log.string
1015 (
1016 fnd_log.level_error,
1017 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1018 'Flight Number cannot be updated for '
1019 ||p_x_flight_schedule_rec.unit_schedule_id
1020 );
1021 END IF;
1022 x_return_status := FND_API.G_RET_STS_ERROR;
1023 --RAISE FND_API.G_EXC_ERROR;
1024 END IF;
1025
1026 -- Flight Segment cannot be updated unless user is super user.
1027 IF p_x_flight_schedule_rec.segment <> l_current_us_rec.segment
1028 THEN
1029 FND_MESSAGE.set_name( 'AHL','AHL_UA_SEGMENT_NO_UPDATE' );
1030 FND_MESSAGE.set_token( 'RECORD', l_record_identifier);
1031 FND_MSG_PUB.add;
1032 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1033 fnd_log.string
1034 (
1035 fnd_log.level_error,
1036 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1037 'Flight Segment cannot be updated for '
1038 ||p_x_flight_schedule_rec.unit_schedule_id
1039 );
1040 END IF;
1041 x_return_status := FND_API.G_RET_STS_ERROR;
1042 --RAISE FND_API.G_EXC_ERROR;
1043 END IF;
1044 ------------------------------------------------------------------------------------------------------
1045 --Un comment the code if this is required */
1046
1047 -- Departure department cannot be updated unless user is super user.
1048 IF p_x_flight_schedule_rec.departure_dept_id <> l_current_us_rec.departure_dept_id AND
1049 l_update_allowed = FND_API.G_FALSE
1050 THEN
1051 FND_MESSAGE.set_name( 'AHL', 'AHL_UA_DEP_DEPT_NO_UPDATE' );
1052 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
1053 FND_MSG_PUB.add;
1054 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1055 fnd_log.string
1056 (
1057 fnd_log.level_error,
1058 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1059 'Departure deparment cannot be updated for '
1060 ||p_x_flight_schedule_rec.unit_schedule_id
1061 );
1062 END IF;
1063 x_return_status := FND_API.G_RET_STS_ERROR;
1064 --RAISE FND_API.G_EXC_ERROR;
1065 END IF;
1066
1067 -- Departure Organization cannot be updated unless user is super user.
1068 IF p_x_flight_schedule_rec.departure_org_id <> l_current_us_rec.departure_org_id AND
1069 l_update_allowed = FND_API.G_FALSE
1070 THEN
1071 FND_MESSAGE.set_name( 'AHL', 'AHL_UA_DEP_ORG_NO_UPDATE' );
1072 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
1073 FND_MSG_PUB.add;
1074 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1075 fnd_log.string
1076 (
1077 fnd_log.level_error,
1078 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1079 'Departure org cannot be updated for '
1080 ||p_x_flight_schedule_rec.unit_schedule_id
1081 );
1082 END IF;
1083 x_return_status := FND_API.G_RET_STS_ERROR;
1084 --RAISE FND_API.G_EXC_ERROR;
1085 END IF;
1086
1087 -- Arrival Department cannot be updated unless user is super user.
1088 IF p_x_flight_schedule_rec.arrival_dept_id <> l_current_us_rec.arrival_dept_id AND
1089 l_update_allowed = FND_API.G_FALSE
1090 THEN
1091 FND_MESSAGE.set_name( 'AHL', 'AHL_UA_ARR_DEP_NO_UPDATE' );
1092 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
1093 FND_MSG_PUB.add;
1094 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1095 fnd_log.string
1096 (
1097 fnd_log.level_error,
1098 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1099 'Arrival Department cannot be updated for'
1100 ||p_x_flight_schedule_rec.unit_schedule_id
1101 );
1102 END IF;
1103 x_return_status := FND_API.G_RET_STS_ERROR;
1104 --RAISE FND_API.G_EXC_ERROR;
1105 END IF;
1106
1107 -- Arrival Organization cannot be updated unless user is super user.
1108 IF p_x_flight_schedule_rec.arrival_org_id <> l_current_us_rec.arrival_org_id AND
1109 l_update_allowed = FND_API.G_FALSE
1110 THEN
1111 FND_MESSAGE.set_name( 'AHL', 'AHL_UA_ARR_ORG_NO_UPDATE' );
1112 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
1113 FND_MSG_PUB.add;
1114 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1115 fnd_log.string
1116 (
1117 fnd_log.level_error,
1118 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1119 'Arrival org cannot be updated for '
1120 ||p_x_flight_schedule_rec.unit_schedule_id
1121 );
1122 END IF;
1123 x_return_status := FND_API.G_RET_STS_ERROR;
1124 --RAISE FND_API.G_EXC_ERROR;
1125 END IF;
1126
1127 -- Estimated Departure Time cannot be updated unless user is super user.
1128 IF p_x_flight_schedule_rec.est_departure_time <> l_current_us_rec.est_departure_time AND
1129 l_update_allowed = FND_API.G_FALSE
1130 THEN
1131 FND_MESSAGE.set_name( 'AHL', 'AHL_UA_EST_DEP_NO_UPDATE' );
1132 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
1133 FND_MSG_PUB.add;
1134 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1135 fnd_log.string
1136 (
1137 fnd_log.level_error,
1138 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1139 'Estimated Departure time cannot be updated for '
1140 ||p_x_flight_schedule_rec.unit_schedule_id
1141 );
1142 END IF;
1143 x_return_status := FND_API.G_RET_STS_ERROR;
1144 --RAISE FND_API.G_EXC_ERROR;
1145 END IF;
1146
1147 -- Estimated Arrival Time cannot be updated unless user is super user.
1148 IF p_x_flight_schedule_rec.est_arrival_time <> l_current_us_rec.est_arrival_time AND
1149 l_update_allowed = FND_API.G_FALSE
1150 THEN
1151 FND_MESSAGE.set_name( 'AHL', 'AHL_UA_EST_ARR_NO_UPDATE' );
1152 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
1153 FND_MSG_PUB.add;
1154 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1155 fnd_log.string
1156 (
1157 fnd_log.level_error,
1158 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1159 'Estimated Arrival time cannot be updated for '
1160 ||p_x_flight_schedule_rec.unit_schedule_id
1161 );
1162 END IF;
1163 x_return_status := FND_API.G_RET_STS_ERROR;
1164 --RAISE FND_API.G_EXC_ERROR;
1165 END IF;
1166
1167 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1168 fnd_log.string
1169 (
1170 fnd_log.level_procedure,
1171 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.end',
1172 'At the end of '||l_api_name
1173 );
1174 END IF;
1175 END validate_update;
1176
1177 -----------------------------------------------------------------------------------------------------
1178 -- Procedure to Perform cross attribute validation and missing attribute checks and duplicate checks
1179 -----------------------------------------------------------------------------------------------------
1180 PROCEDURE validate_record
1181 (
1182 p_x_flight_schedule_rec IN OUT NOCOPY FLIGHT_SCHEDULE_REC_TYPE,
1183 x_return_status OUT NOCOPY VARCHAR2
1184 )
1185 IS
1186
1187 --Cursor for getting the preceeding event of an Unit Schedule.
1188 CURSOR get_preceeding_us_csr(p_unit_schedule_id IN NUMBER) IS
1189 /*SELECT unit_schedule_id, actual_departure_time, actual_arrival_time
1190 FROM AHL_UNIT_SCHEDULES_V
1191 WHERE unit_schedule_id = (
1192 SELECT preceding_us_id
1193 FROM AHL_UNIT_SCHEDULES_V
1194 WHERE unit_schedule_id = p_unit_schedule_id
1195 );*/
1196 --Priyan changed the query due to performance issues
1197 --Refer Bug # 4916339
1198 SELECT
1199 UNIT_SCHEDULE_ID,
1200 ACTUAL_DEPARTURE_TIME,
1201 ACTUAL_ARRIVAL_TIME
1202 FROM
1203 AHL_UNIT_SCHEDULES
1204 WHERE
1205 UNIT_SCHEDULE_ID = (
1206 SELECT PRECEDING_US_ID
1207 FROM AHL_UNIT_SCHEDULES
1208 WHERE UNIT_SCHEDULE_ID = p_unit_schedule_id
1209 );
1210
1211 l_preceeding_us_rec get_preceeding_us_csr%ROWTYPE;
1212
1213 --Cursor for getting the preceeding event of an Unit Schedule in create mode.
1214 CURSOR get_pre_us_uc_csr(p_uc_header_id IN NUMBER, p_est_arrival_date IN DATE) IS
1215 SELECT unit_schedule_id, actual_departure_time, actual_arrival_time
1216 FROM AHL_UNIT_SCHEDULES
1217 WHERE unit_config_header_id = p_uc_header_id AND
1218 Est_arrival_time < p_est_arrival_date;
1219
1220 CURSOR get_curr_act_dates_csr(p_unit_schedule_id IN NUMBER) IS
1221 SELECT actual_departure_time, actual_arrival_time
1222 FROM AHL_UNIT_SCHEDULES
1223 WHERE unit_schedule_id = p_unit_schedule_id;
1224 l_curr_act_dates_rec get_curr_act_dates_csr%ROWTYPE;
1225
1226 -- Cursor for finding overlaps in flight schedules.If this is used then above cursor is not required
1227 -- Using AHL_UNIT_SCHEDULES_V instead of AHL_UNIT_SCHEDULES because of restriction in access.
1228 CURSOR get_cur_us_det_csr(p_unit_schedule_id IN NUMBER)
1229 IS
1230 SELECT unit_config_header_id,
1231 EST_DEPARTURE_TIME,
1232 EST_ARRIVAL_TIME,
1233 ACTUAL_DEPARTURE_TIME,
1234 ACTUAL_ARRIVAL_TIME
1235 FROM AHL_UNIT_SCHEDULES
1236 WHERE unit_schedule_id = p_unit_schedule_id;
1237
1238
1239 -- Define all local variables here.
1240 l_api_name CONSTANT VARCHAR2(30) := 'validate_record';
1241 l_cur_us_rec get_cur_us_det_csr%ROWTYPE;
1242 l_us_dup_count NUMBER;
1243 l_record_identifier VARCHAR2(150);
1244 l_est_violation_count NUMBER;
1245
1246 BEGIN
1247
1248 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1249 fnd_log.string
1250 (
1251 fnd_log.level_procedure,
1252 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
1253 'At the start of '||l_api_name
1254 );
1255 END IF;
1256
1257 -- Set return status to success.
1258
1259 x_return_status := FND_API.G_RET_STS_SUCCESS;
1260
1261 l_record_identifier := get_record_identifier(
1262 p_flight_schedule_rec => p_x_flight_schedule_rec
1263 );
1264
1265 --Check on delete if actual dates are not recorded.
1266 IF p_x_flight_schedule_rec.dml_operation = 'D'
1267 THEN
1268 OPEN get_curr_act_dates_csr(p_x_flight_schedule_rec.unit_schedule_id);
1269 FETCH get_curr_act_dates_csr
1270 INTO
1271 l_curr_act_dates_rec.actual_departure_time,
1272 l_curr_act_dates_rec.actual_arrival_time;
1273
1274 CLOSE get_curr_act_dates_csr;
1275 IF ( l_curr_act_dates_rec.actual_departure_time IS NOT NULL OR
1276 l_curr_act_dates_rec.actual_arrival_time IS NOT NULL )
1277 AND is_delete_allowed(p_unit_schedule_id => p_x_flight_schedule_rec.unit_schedule_id,
1278 p_is_super_user => is_super_user
1279 ) = FND_API.G_FALSE
1280 THEN
1281 FND_MESSAGE.set_name( 'AHL', 'AHL_UA_ACT_NO_DEL' );
1282 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
1283 FND_MSG_PUB.add;
1284 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1285 fnd_log.string
1286 (
1287 fnd_log.level_error,
1288 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1289 'Actual times are entered for '||p_x_flight_schedule_rec.unit_schedule_id
1290 ||'so delete not allowed'
1291 );
1292 END IF;
1293 x_return_status := FND_API.G_RET_STS_ERROR;
1294 END IF;
1295
1296 ELSE
1297 --Check for duplicate records.
1298 SELECT count(unit_schedule_id) INTO l_us_dup_count
1299 FROM AHL_UNIT_SCHEDULES
1300 WHERE UNIT_CONFIG_HEADER_ID = p_x_flight_schedule_rec.UNIT_CONFIG_HEADER_ID
1301 AND FLIGHT_NUMBER = p_x_flight_schedule_rec.FLIGHT_NUMBER
1302 AND SEGMENT = p_x_flight_schedule_rec.SEGMENT
1303 AND EST_ARRIVAL_TIME = p_x_flight_schedule_rec.EST_ARRIVAL_TIME
1304 AND EST_DEPARTURE_TIME = p_x_flight_schedule_rec.EST_DEPARTURE_TIME
1305 AND unit_schedule_id <> nvl(p_x_flight_schedule_rec.unit_schedule_id,-100);
1306
1307 IF l_us_dup_count > 0 THEN
1308 -- Duplicate found, so throw error.
1309 FND_MESSAGE.set_name( 'AHL', 'AHL_UA_DUP_FLG_SCH' );
1310 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
1311 FND_MSG_PUB.add;
1312 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1313 fnd_log.string
1314 (
1315 fnd_log.level_error,
1316 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1317 'Duplicates flight schedules found for ,'
1318 ||p_x_flight_schedule_rec.unit_schedule_id
1319 );
1320 END IF;
1321 x_return_status := FND_API.G_RET_STS_ERROR;
1322 RAISE FND_API.G_EXC_ERROR;
1323 END IF;
1324
1325 --Check if Actual Arrival is entered without departure.
1326 IF (
1327 p_x_flight_schedule_rec.ACTUAL_ARRIVAL_TIME IS NOT NULL AND
1328 p_x_flight_schedule_rec.ACTUAL_ARRIVAL_TIME <> FND_API.G_MISS_DATE
1329 )
1330 AND
1331 (
1332 p_x_flight_schedule_rec.ACTUAL_DEPARTURE_TIME IS NULL OR
1333 p_x_flight_schedule_rec.ACTUAL_DEPARTURE_TIME = FND_API.G_MISS_DATE
1334 )
1335 THEN
1336 FND_MESSAGE.set_name( 'AHL', 'AHL_UA_ARR_WO_DEP' );
1337 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
1338 FND_MSG_PUB.add;
1339 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1340 fnd_log.string
1341 (
1342 fnd_log.level_error,
1343 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1344 'Actuals Arrival time cannot be entered until departure time is entered '||p_x_flight_schedule_rec.unit_schedule_id
1345 );
1346 END IF;
1347 x_return_status := FND_API.G_RET_STS_ERROR;
1348 END IF;
1349
1350 --Check if actuals are greater than sysdate.
1351 IF (
1352 p_x_flight_schedule_rec.ACTUAL_DEPARTURE_TIME IS NOT NULL AND
1353 p_x_flight_schedule_rec.ACTUAL_DEPARTURE_TIME <> FND_API.G_MISS_DATE AND
1354 p_x_flight_schedule_rec.ACTUAL_DEPARTURE_TIME > SYSDATE
1355 )
1356 OR
1357 (
1358 p_x_flight_schedule_rec.ACTUAL_ARRIVAL_TIME IS NOT NULL AND
1359 p_x_flight_schedule_rec.ACTUAL_ARRIVAL_TIME <> FND_API.G_MISS_DATE AND
1360 p_x_flight_schedule_rec.ACTUAL_ARRIVAL_TIME > SYSDATE
1361 )
1362 THEN
1363 FND_MESSAGE.set_name( 'AHL', 'AHL_UA_ACT_GT_SYSDATE' );
1364 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
1365 FND_MSG_PUB.add;
1366 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1367 fnd_log.string
1368 (
1369 fnd_log.level_error,
1370 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1371 'Actuals cannot be greater than sysdate for '||p_x_flight_schedule_rec.unit_schedule_id
1372 );
1373 END IF;
1374 x_return_status := FND_API.G_RET_STS_ERROR;
1375 END IF;
1376
1377 --Validate if arrival dates are greater than departure dates.
1378 IF (
1379 p_x_flight_schedule_rec.EST_DEPARTURE_TIME IS NOT NULL AND
1380 p_x_flight_schedule_rec.EST_DEPARTURE_TIME <> FND_API.G_MISS_DATE
1381 )
1382 AND
1383 p_x_flight_schedule_rec.EST_DEPARTURE_TIME >= p_x_flight_schedule_rec.EST_ARRIVAL_TIME
1384 THEN
1385 FND_MESSAGE.set_name( 'AHL', 'AHL_UA_ARR_LT_DEP' );
1386 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
1387 FND_MSG_PUB.add;
1388 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1389 fnd_log.string
1390 (
1391 fnd_log.level_error,
1392 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1393 'Estimated Departure cannot be greater than arrival for '
1394 ||p_x_flight_schedule_rec.unit_schedule_id
1395 );
1396 END IF;
1397 x_return_status := FND_API.G_RET_STS_ERROR;
1398 END IF;
1399
1400 --Validate if arrival dates are greater than departure dates.
1401 IF (
1402 p_x_flight_schedule_rec.ACTUAL_DEPARTURE_TIME IS NOT NULL AND
1403 p_x_flight_schedule_rec.ACTUAL_DEPARTURE_TIME <> FND_API.G_MISS_DATE
1404 )
1405 AND
1406 p_x_flight_schedule_rec.ACTUAL_DEPARTURE_TIME >= p_x_flight_schedule_rec.ACTUAL_ARRIVAL_TIME
1407 THEN
1408 FND_MESSAGE.set_name( 'AHL', 'AHL_UA_ARR_LT_DEP' );
1409 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
1410 FND_MSG_PUB.add;
1411 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1412 fnd_log.string
1413 (
1414 fnd_log.level_error,
1415 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1416 'Actual Departure cannot be greater than arrival for '
1417 ||p_x_flight_schedule_rec.unit_schedule_id
1418 );
1419 END IF;
1420 x_return_status := FND_API.G_RET_STS_ERROR;
1421 END IF;
1422
1423
1424 IF (
1425 p_x_flight_schedule_rec.actual_departure_time IS NOT NULL AND
1426 p_x_flight_schedule_rec.actual_departure_time <> FND_API.G_MISS_DATE
1427 )
1428 OR
1429 (
1430 p_x_flight_schedule_rec.actual_arrival_time IS NOT NULL AND
1431 p_x_flight_schedule_rec.actual_arrival_time <> FND_API.G_MISS_DATE
1432 )
1433 THEN
1434 --If create then use unit_config_header_id to fetch actual times
1435 -- Added for bug 4071579
1436 IF p_x_flight_schedule_rec.unit_schedule_id IS NULL
1437 THEN
1438 OPEN get_pre_us_uc_csr(
1439 p_x_flight_schedule_rec.unit_config_header_id,
1440 p_x_flight_schedule_rec.EST_ARRIVAL_TIME
1441 );
1442 FETCH get_pre_us_uc_csr
1443 INTO
1444 l_preceeding_us_rec.unit_schedule_id,
1445 l_preceeding_us_rec.actual_departure_time,
1446 l_preceeding_us_rec.actual_arrival_time;
1447 CLOSE get_pre_us_uc_csr;
1448 ELSE
1449 --Check if prior actuals are recorded.
1450 OPEN get_preceeding_us_csr(p_x_flight_schedule_rec.unit_schedule_id);
1451 FETCH get_preceeding_us_csr
1452 INTO
1453 l_preceeding_us_rec.unit_schedule_id,
1454 l_preceeding_us_rec.actual_departure_time,
1455 l_preceeding_us_rec.actual_arrival_time;
1456 CLOSE get_preceeding_us_csr;
1457 END IF;
1458
1459 IF l_preceeding_us_rec.unit_schedule_id IS NOT NULL AND
1460 (l_preceeding_us_rec.actual_departure_time IS NULL OR
1461 l_preceeding_us_rec.actual_arrival_time IS NULL )
1462 THEN
1463 FND_MESSAGE.set_name( 'AHL', 'AHL_UA_PRE_ACT_NOT_REC' );
1464 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
1465 FND_MSG_PUB.add;
1466 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1467 fnd_log.string
1468 (
1469 fnd_log.level_error,
1470 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1471 'previous actuals are not entered for '||p_x_flight_schedule_rec.unit_schedule_id
1472 );
1473 END IF;
1474 x_return_status := FND_API.G_RET_STS_ERROR;
1475 END IF;
1476 END IF;
1477
1478 -- code to verify if estimated times are updated to a value less than
1479 -- some other flight's estimated arrival time of the same unit whose Actuals are already entered.
1480 SELECT count(unit_schedule_id) into l_est_violation_count
1481 FROM AHL_UNIT_SCHEDULES
1482 WHERE est_arrival_time > p_x_flight_schedule_rec.EST_ARRIVAL_TIME AND
1483 unit_config_header_id = p_x_flight_schedule_rec.unit_config_header_id
1484 AND (
1485 actual_departure_time is not null OR actual_arrival_time is not null
1486 )
1487 AND unit_schedule_id <> nvl(p_x_flight_schedule_rec.unit_schedule_id,-110);
1488
1489 IF l_est_violation_count > 0 THEN
1490 FND_MESSAGE.set_name( 'AHL', 'AHL_UA_EST_ARR_VIO' );
1491 FND_MESSAGE.set_token( 'RECORD', l_record_identifier );
1492 FND_MSG_PUB.add;
1493 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1494 fnd_log.string
1495 (
1496 fnd_log.level_error,
1497 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1498 'estimated arrival time violates another flight'||
1499 'schedules estimated arrival time for '||p_x_flight_schedule_rec.unit_schedule_id
1500 );
1501 END IF;
1502 x_return_status := FND_API.G_RET_STS_ERROR;
1503 END IF;
1504
1505 END IF;
1506 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1507 fnd_log.string
1508 (
1509 fnd_log.level_procedure,
1510 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.end',
1511 'At the end of '||l_api_name
1512 );
1513 END IF;
1514
1515 END validate_record;
1516
1517 -----------------------------------------------------------------------------------------------------
1518 -- Procedure to calculate preceeding flight schedules information based on current record info.
1519 -----------------------------------------------------------------------------------------------------
1520 PROCEDURE Sequence_Flight_Schedules
1521 (
1522 p_x_flight_schedules_tbl IN OUT NOCOPY FLIGHT_SCHEDULES_TBL_TYPE
1523 )
1524 IS
1525
1526 --Cursor for finding previous unit schedule id.
1527 CURSOR get_pre_us_id_act(p_unit_config_header_id IN NUMBER)
1528 IS
1529 SELECT unit_schedule_id, nvl(actual_arrival_time, est_arrival_time) "ARRIVAL_TIME"
1530 FROM AHL_UNIT_SCHEDULES
1531 WHERE unit_config_header_id = p_unit_config_header_id
1532 ORDER BY nvl(actual_arrival_time, est_arrival_time) DESC;
1533
1534 --Cursor for finding previous unit schedule id.
1535 CURSOR get_pre_us_id_est(p_unit_config_header_id IN NUMBER)
1536 IS
1537 SELECT unit_schedule_id, est_arrival_time "ARRIVAL_TIME"
1538 FROM AHL_UNIT_SCHEDULES
1539 WHERE unit_config_header_id = p_unit_config_header_id
1540 ORDER BY est_arrival_time DESC;
1541
1542 -- Define all local variables here.
1543 l_api_name CONSTANT VARCHAR2(30) := 'Sequence_Flight_Schedules';
1544 l_us_count NUMBER;
1545 l_pre_us_tbl pre_us_tbl_type;
1546 l_equal_arr_count NUMBER;
1547 l_count NUMBER;
1548 j NUMBER;
1549
1550 BEGIN
1551
1552 FOR i IN p_x_flight_schedules_tbl.FIRST..p_x_flight_schedules_tbl.LAST
1553 LOOP
1554 --Get preceeding us id.
1555 l_equal_arr_count := 0;
1556 l_count := 1;
1557 IF G_USE_ACTUALS = FND_API.G_TRUE THEN
1558 OPEN get_pre_us_id_act(p_x_flight_schedules_tbl(i).unit_config_header_id);
1559 LOOP
1560 FETCH get_pre_us_id_act INTO l_pre_us_tbl(l_count).unit_schedule_id,
1561 l_pre_us_tbl(l_count).arrival_time;
1562 EXIT WHEN get_pre_us_id_act%NOTFOUND;
1563 IF l_count > 1 THEN
1564 IF l_pre_us_tbl(l_count-1).arrival_time = l_pre_us_tbl(l_count).arrival_time
1565 THEN
1566 l_equal_arr_count := l_equal_arr_count + 1;
1567 ELSIF l_equal_arr_count > 0 THEN
1568 LOOP
1569 l_pre_us_tbl(l_count-l_equal_arr_count-1).preceding_us_id := l_pre_us_tbl(l_count).unit_schedule_id;
1570 EXIT WHEN l_equal_arr_count = 0;
1571 l_equal_arr_count := l_equal_arr_count -1;
1572 END LOOP;
1573 ELSE
1574 l_pre_us_tbl(l_count-1).preceding_us_id := l_pre_us_tbl(l_count).unit_schedule_id;
1575 END IF;
1576 END IF;
1577 l_count := l_count + 1;
1578 END LOOP;
1579 CLOSE get_pre_us_id_act;
1580 ELSE
1581 OPEN get_pre_us_id_est(p_x_flight_schedules_tbl(i).unit_config_header_id);
1582 LOOP
1583 FETCH get_pre_us_id_est INTO l_pre_us_tbl(l_count).unit_schedule_id,
1584 l_pre_us_tbl(l_count).arrival_time;
1585 EXIT WHEN get_pre_us_id_est%NOTFOUND;
1586 IF l_count > 1 THEN
1587 IF l_pre_us_tbl(l_count-1).arrival_time = l_pre_us_tbl(l_count).arrival_time
1588 THEN
1589 l_equal_arr_count := l_equal_arr_count + 1;
1590 ELSIF l_equal_arr_count > 0 THEN
1591 LOOP
1592 l_pre_us_tbl(l_count-l_equal_arr_count-1).preceding_us_id := l_pre_us_tbl(l_count).unit_schedule_id;
1593 EXIT WHEN l_equal_arr_count = 0;
1594 l_equal_arr_count := l_equal_arr_count -1;
1595 END LOOP;
1596 ELSE
1597 l_pre_us_tbl(l_count-1).preceding_us_id := l_pre_us_tbl(l_count).unit_schedule_id;
1598 END IF;
1599 END IF;
1600 l_count := l_count + 1;
1601 END LOOP;
1602 CLOSE get_pre_us_id_est;
1603 END IF;
1604
1605 --Update the table.
1606 FOR j IN 1..l_pre_us_tbl.COUNT
1607 LOOP
1608 UPDATE AHL_UNIT_SCHEDULES
1609 SET preceding_us_id = l_pre_us_tbl(j).preceding_us_id
1610 WHERE unit_schedule_id = l_pre_us_tbl(j).unit_schedule_id;
1611 END LOOP;
1612
1613 END LOOP;
1614
1615 END Sequence_Flight_Schedules;
1616
1617 -----------------------------------------------------------------------------------------------------
1618 -- Procedure for synchronising a Transit Visit with a Flight Schedule if the Flight shcedule Estimated
1619 -- dates are changed.
1620 -----------------------------------------------------------------------------------------------------
1621
1622 PROCEDURE Synchronize_Visit_Details (
1623 x_return_status OUT NOCOPY VARCHAR2,
1624 p_visit_sync_rec IN OUT NOCOPY VISIT_SYNC_REC_TYPE
1625 )
1626 IS
1627
1628 l_api_name CONSTANT VARCHAR2(30) := 'Synchronize_Visit_Details';
1629 l_msg_count NUMBER;
1630 l_msg_data VARCHAR2(150);
1631 l_time_diff NUMBER;
1632
1633
1634 BEGIN
1635 -- Initialize the return status to success.
1636
1637 x_return_status := FND_API.G_RET_STS_SUCCESS;
1638
1639 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1640 fnd_log.string
1641 (
1642 fnd_log.level_procedure,
1643 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
1644 'At the start of '||l_api_name
1645 );
1646 END IF;
1647
1648 -- If the time difference is null then take the time difference as 0
1649 l_time_diff := NVL(p_visit_sync_rec.CHANGED_ARRIVAL_TIME,0);
1650
1651 IF( p_visit_sync_rec.VISIT_RESCHEDULE_MODE <> 'NEVER_RESCHEDULE')
1652 THEN
1653
1654 OPEN get_flight_visit( p_visit_sync_rec.UNIT_SCHEDULE_ID );
1655
1656 LOOP
1657
1658 FETCH get_flight_visit into l_flight_visit;
1659
1660 EXIT WHEN get_flight_visit%NOTFOUND;
1661
1662 l_visit_count := get_flight_visit%ROWCOUNT;
1663
1664 l_visit_tbl(l_visit_count).visit_id := l_flight_visit.visit_id;
1665 l_visit_tbl(l_visit_count).object_version_number := l_flight_visit.object_version_number;
1666 l_visit_tbl(l_visit_count).start_date := l_flight_visit.start_date_time + l_time_diff;
1667
1668 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1669 fnd_log.string
1670 (
1671 fnd_log.level_statement,
1672 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
1673 ' changed arrival time ' || p_visit_sync_rec.CHANGED_ARRIVAL_TIME ||
1674 ' changed org id ' || TO_CHAR(p_visit_sync_rec.CHANGED_ORG_ID) ||
1675 ' changed dept id ' || TO_CHAR(p_visit_sync_rec.CHANGED_DEPT_ID )
1676 );
1677 END IF;
1678
1679 IF( l_flight_visit.close_date_time IS NOT NULL)
1680 THEN
1681 l_visit_tbl(l_visit_count).PLAN_END_DATE := l_flight_visit.close_date_time + l_time_diff;
1682 ELSE
1683 -- calculate the close date time.
1684 l_visit_tbl(l_visit_count).PLAN_END_DATE := AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(l_flight_visit.visit_id , FND_API.G_FALSE) + l_time_diff;
1685 END IF;
1686
1687 l_visit_tbl(l_visit_count).item_instance_id := l_flight_visit.item_instance_id;
1688 l_visit_tbl(l_visit_count).unit_schedule_id := l_flight_visit.unit_schedule_id;
1689
1690 IF( p_visit_sync_rec.CHANGED_ORG_ID IS NOT NULL AND p_visit_sync_rec.CHANGED_ORG_ID <> l_flight_visit.ORGANIZATION_ID )
1691 THEN
1692 l_visit_tbl(l_visit_count).ORGANIZATION_ID := p_visit_sync_rec.CHANGED_ORG_ID;
1693 ELSE
1694 l_visit_tbl(l_visit_count).ORGANIZATION_ID := l_flight_visit.ORGANIZATION_ID;
1695 END IF;
1696
1697 IF( p_visit_sync_rec.CHANGED_DEPT_ID IS NOT NULL AND p_visit_sync_rec.CHANGED_DEPT_ID <> l_flight_visit.DEPARTMENT_ID )
1698 THEN
1699 l_visit_tbl(l_visit_count).DEPARTMENT_ID := p_visit_sync_rec.CHANGED_DEPT_ID;
1700 ELSE
1701 l_visit_tbl(l_visit_count).DEPARTMENT_ID := l_flight_visit.DEPARTMENT_ID ;
1702 END IF;
1703
1704 IF( p_visit_sync_rec.VISIT_RESCHEDULE_MODE = 'ALWAYS_RESCHEDULE')
1705 THEN
1706 l_visit_tbl(l_visit_count).operation_flag := 'S';
1707 ELSIF ( p_visit_sync_rec.VISIT_RESCHEDULE_MODE = 'DELETE')
1708
1709 THEN
1710 l_visit_tbl(l_visit_count).operation_flag := 'X';
1711 END IF;
1712
1713 END LOOP;
1714
1715 CLOSE get_flight_visit;
1716
1717 AHL_VWP_VISITS_PVT.Process_Visit (
1718 p_api_version => 1.0,
1719 p_init_msg_list => FND_API.g_false,
1720 p_commit => FND_API.g_false,
1721 p_validation_level => FND_API.g_valid_level_full,
1722 p_module_type => 'JSP',
1723 p_x_Visit_tbl => l_visit_tbl,
1724 x_return_status => x_return_status,
1725 x_msg_count => l_msg_count,
1726 x_msg_data => l_msg_data
1727 );
1728
1729
1730 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS )
1731 THEN
1732 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1733 fnd_log.string
1734 (
1735 fnd_log.level_statement,
1736 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
1737 ' AHL_VWP_VISITS_PVT.Process_Visit returned errors'
1738 );
1739 END IF;
1740 ELSE
1741 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1742 fnd_log.string
1743 (
1744 fnd_log.level_statement,
1745 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1746 ' AHL_VWP_VISITS_PVT.Process_Visit executed successfully'
1747 );
1748 END IF;
1749 END IF;
1750
1751 END IF;
1752
1753 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1754 fnd_log.string
1755 (
1756 fnd_log.level_procedure,
1757 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.end',
1758 'At the end of '||l_api_name
1759 );
1760 END IF;
1761 EXCEPTION
1762 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1763 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1764 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1765 p_count => l_msg_count,
1766 p_data => l_msg_data);
1767
1768 WHEN FND_API.G_EXC_ERROR THEN
1769 x_return_status := FND_API.G_RET_STS_ERROR;
1770 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1771 p_count => l_msg_count,
1772 p_data => l_msg_data);
1773
1774 WHEN OTHERS THEN
1775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1776 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1777 THEN
1778 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1779 p_procedure_name => l_api_name,
1780 p_error_text => SUBSTR(SQLERRM,1,240));
1781
1782 END IF;
1783 FND_MSG_PUB.count_and_get
1784 (
1785 p_count => l_msg_count,
1786 p_data => l_msg_data,
1787 p_encoded => FND_API.G_FALSE
1788 );
1789
1790 END Synchronize_Visit_Details;
1791
1792 PROCEDURE Create_Flight_Schedules(
1793 p_module_type IN VARCHAR2,
1794 x_return_status OUT NOCOPY VARCHAR2,
1795 p_x_flight_schedules_tbl IN OUT NOCOPY FLIGHT_SCHEDULES_TBL_TYPE
1796 )
1797 IS
1798
1799 l_api_name CONSTANT VARCHAR2(30) := 'Create_Flight_Schedules';
1800 l_api_version CONSTANT NUMBER := 1;
1801 l_msg_count NUMBER;
1802 l_overlap_us_count NUMBER;
1803
1804 BEGIN
1805 -- Initialize return status to success initially
1806 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1807
1808 FOR i IN p_x_flight_schedules_tbl.FIRST..p_x_flight_schedules_tbl.LAST
1809 LOOP
1810 IF p_x_flight_schedules_tbl(i).DML_OPERATION = 'C'
1811 THEN
1812 -- Validate all attributes (Item level validation)
1813 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1814 fnd_log.string
1815 (
1816 fnd_log.level_statement,
1817 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1818 'Validate attributes before creating flight schedules...'
1819 );
1820 END IF;
1821
1822 validate_attributes
1823 (
1824 p_x_flight_schedules_tbl(i),
1825 x_return_status
1826 );
1827
1828 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1829 fnd_log.string
1830 (
1831 fnd_log.level_statement,
1832 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1833 'p_x_flight_schedules_tbl('||i||').DML_OPERATION : ' || p_x_flight_schedules_tbl(i).DML_OPERATION
1834 );
1835 END IF;
1836
1837 -- Convert Values to Ids.
1838 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1839 fnd_log.string
1840 (
1841 fnd_log.level_statement,
1842 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1843 'Calling convert_values_to_ids..'
1844 );
1845 END IF;
1846
1847 IF ( p_module_type = 'OAF' OR p_module_type = 'JSP' )
1848 THEN
1849 convert_values_to_ids
1850 (
1851 p_x_flight_schedules_tbl(i),
1852 x_return_status
1853 );
1854
1855 -- If any severe error occurs, then, abort API.
1856 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1857 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
1858 fnd_log.string
1859 (
1860 fnd_log.level_exception,
1861 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
1862 'convert_values_to_ids returned with expected error..'
1863 );
1864 END IF;
1865 RAISE FND_API.G_EXC_ERROR;
1866 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1867 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
1868 fnd_log.string
1869 (
1870 fnd_log.level_exception,
1871 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
1872 'convert_values_to_ids returned with un-expected error..'
1873 );
1874 END IF;
1875 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1876 END IF;-- return status
1877 END IF;-- module type
1878
1879
1880 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1881 fnd_log.string
1882 (
1883 fnd_log.level_statement,
1884 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1885 'call validate_mandatory_inputs to check all missing mandatory fields'
1886 );
1887 END IF;
1888
1889 validate_mandatory_fields
1890 (
1891 p_x_flight_schedules_tbl(i),
1892 x_return_status
1893 );
1894
1895 -- If any severe error occurs, then, abort API.
1896 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1897 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
1898 fnd_log.string
1899 (
1900 fnd_log.level_exception,
1901 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
1902 'validate_mandatory_fields returned with expected error..'
1903 );
1904 END IF;
1905 RAISE FND_API.G_EXC_ERROR;
1906 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1907 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
1908 fnd_log.string
1909 (
1910 fnd_log.level_exception,
1911 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
1912 'validate_mandatory_fields returned with un-expected error..'
1913 );
1914 END IF;
1915 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1916 END IF;
1917
1918 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1919 fnd_log.string
1920 (
1921 fnd_log.level_statement,
1922 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1923 'Validate all records before DML '
1924 );
1925 END IF;
1926
1927 validate_record
1928 (
1929 p_x_flight_schedules_tbl(i),
1930 x_return_status
1931 );
1932
1933 -- If any severe error occurs, then, abort API.
1934 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1935 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
1936 fnd_log.string
1937 (
1938 fnd_log.level_exception,
1939 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
1940 'validate_record returned with expected error..'
1941 );
1942 END IF;
1943 RAISE FND_API.G_EXC_ERROR;
1944 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1945 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
1946 fnd_log.string
1947 (
1948 fnd_log.level_exception,
1949 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
1950 'validate_record returned with un-expected error..'
1951 );
1952 END IF;
1953 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1954 END IF;
1955
1956 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1957 fnd_log.string
1958 (
1959 fnd_log.level_statement,
1960 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1961 'Creating new Flight scehdule ..'
1962 );
1963 END IF;
1964
1965 -- Insert the record
1966 INSERT INTO AHL_UNIT_SCHEDULES
1967 (
1968 UNIT_SCHEDULE_ID,
1969 FLIGHT_NUMBER,
1970 SEGMENT,
1971 DEPARTURE_DEPT_ID,
1972 DEPARTURE_ORG_ID,
1973 ARRIVAL_DEPT_ID,
1974 ARRIVAL_ORG_ID,
1975 EST_DEPARTURE_TIME,
1976 EST_ARRIVAL_TIME,
1977 ACTUAL_DEPARTURE_TIME,
1978 ACTUAL_ARRIVAL_TIME,
1979 PRECEDING_US_ID,
1980 UNIT_CONFIG_HEADER_ID,
1981 VISIT_RESCHEDULE_MODE,
1982 LAST_UPDATE_DATE,
1983 LAST_UPDATED_BY,
1984 CREATION_DATE,
1985 CREATED_BY,
1986 LAST_UPDATE_LOGIN,
1987 OBJECT_VERSION_NUMBER,
1988 ATTRIBUTE_CATEGORY,
1989 ATTRIBUTE1,
1990 ATTRIBUTE2,
1991 ATTRIBUTE3,
1992 ATTRIBUTE4,
1993 ATTRIBUTE5,
1994 ATTRIBUTE6,
1995 ATTRIBUTE7,
1996 ATTRIBUTE8,
1997 ATTRIBUTE9,
1998 ATTRIBUTE10,
1999 ATTRIBUTE11,
2000 ATTRIBUTE12,
2001 ATTRIBUTE13,
2002 ATTRIBUTE14,
2003 ATTRIBUTE15
2004 ) VALUES
2005 (
2006 AHL_UNIT_SCHEDULES_S.NEXTVAL,
2007 p_x_flight_schedules_tbl(i).FLIGHT_NUMBER,
2008 p_x_flight_schedules_tbl(i).SEGMENT,
2009 p_x_flight_schedules_tbl(i).DEPARTURE_DEPT_ID,
2010 p_x_flight_schedules_tbl(i).DEPARTURE_ORG_ID,
2011 p_x_flight_schedules_tbl(i).ARRIVAL_DEPT_ID,
2012 p_x_flight_schedules_tbl(i).ARRIVAL_ORG_ID,
2013 p_x_flight_schedules_tbl(i).EST_DEPARTURE_TIME,
2014 p_x_flight_schedules_tbl(i).EST_ARRIVAL_TIME,
2015 p_x_flight_schedules_tbl(i).ACTUAL_DEPARTURE_TIME,
2016 p_x_flight_schedules_tbl(i).ACTUAL_ARRIVAL_TIME,
2017 p_x_flight_schedules_tbl(i).PRECEDING_US_ID,
2018 p_x_flight_schedules_tbl(i).UNIT_CONFIG_HEADER_ID,
2019 p_x_flight_schedules_tbl(i).VISIT_RESCHEDULE_MODE,
2020 G_SYSDATE,
2021 G_USER_ID,
2022 G_SYSDATE,
2023 G_USER_ID,
2024 G_LOGIN_ID,
2025 1,
2026 p_x_flight_schedules_tbl(i).ATTRIBUTE_CATEGORY,
2027 p_x_flight_schedules_tbl(i).ATTRIBUTE1,
2028 p_x_flight_schedules_tbl(i).ATTRIBUTE2,
2029 p_x_flight_schedules_tbl(i).ATTRIBUTE3,
2030 p_x_flight_schedules_tbl(i).ATTRIBUTE4,
2031 p_x_flight_schedules_tbl(i).ATTRIBUTE5,
2032 p_x_flight_schedules_tbl(i).ATTRIBUTE6,
2033 p_x_flight_schedules_tbl(i).ATTRIBUTE7,
2034 p_x_flight_schedules_tbl(i).ATTRIBUTE8,
2035 p_x_flight_schedules_tbl(i).ATTRIBUTE9,
2036 p_x_flight_schedules_tbl(i).ATTRIBUTE10,
2037 p_x_flight_schedules_tbl(i).ATTRIBUTE11,
2038 p_x_flight_schedules_tbl(i).ATTRIBUTE12,
2039 p_x_flight_schedules_tbl(i).ATTRIBUTE13,
2040 p_x_flight_schedules_tbl(i).ATTRIBUTE14,
2041 p_x_flight_schedules_tbl(i).ATTRIBUTE15
2042 );
2043 SELECT AHL_UNIT_SCHEDULES_S.CURRVAL INTO p_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID FROM DUAL ;
2044 END IF;
2045
2046 -- Check if overlaps are occurring. If there is any overlap, show warning.
2047 l_overlap_us_count := 0;
2048 IF p_x_flight_schedules_tbl(i).actual_departure_time IS NOT NULL AND p_x_flight_schedules_tbl(i).actual_arrival_time IS NOT NULL
2049 THEN
2050 SELECT count(unit_schedule_id) INTO l_overlap_us_count
2051 FROM AHL_UNIT_SCHEDULES
2052 WHERE unit_config_header_id = p_x_flight_schedules_tbl(i).unit_config_header_id
2053 AND (
2054 (
2055 (p_x_flight_schedules_tbl(i).actual_departure_time between ACTUAL_DEPARTURE_TIME and ACTUAL_ARRIVAL_TIME)
2056 AND
2057 ( p_x_flight_schedules_tbl(i).actual_arrival_time between ACTUAL_DEPARTURE_TIME and ACTUAL_ARRIVAL_TIME)
2058 )
2059 OR (ACTUAL_DEPARTURE_TIME between p_x_flight_schedules_tbl(i).actual_departure_time and p_x_flight_schedules_tbl(i).actual_arrival_time)
2060 OR (ACTUAL_ARRIVAL_TIME between p_x_flight_schedules_tbl(i).actual_departure_time and p_x_flight_schedules_tbl(i).actual_arrival_time)
2061 )
2062 AND unit_schedule_id <> p_x_flight_schedules_tbl(i).unit_schedule_id;
2063 ELSIF p_x_flight_schedules_tbl(i).est_departure_time IS NOT NULL AND p_x_flight_schedules_tbl(i).est_arrival_time IS NOT NULL
2064 THEN
2065 SELECT count(unit_schedule_id) INTO l_overlap_us_count
2066 FROM AHL_UNIT_SCHEDULES
2067 WHERE unit_config_header_id = p_x_flight_schedules_tbl(i).unit_config_header_id
2068 /*AND (
2069 (
2070 (p_x_flight_schedules_tbl(i).est_departure_time between EST_DEPARTURE_TIME and EST_ARRIVAL_TIME)
2071 AND
2072 ( p_x_flight_schedules_tbl(i).est_arrival_time between EST_DEPARTURE_TIME and EST_ARRIVAL_TIME)
2073 )
2074 OR (EST_DEPARTURE_TIME between p_x_flight_schedules_tbl(i).est_departure_time and p_x_flight_schedules_tbl(i).est_arrival_time)
2075 OR (EST_ARRIVAL_TIME between p_x_flight_schedules_tbl(i).est_departure_time and p_x_flight_schedules_tbl(i).est_arrival_time)
2076 )*/
2077 AND (
2078 (
2079 ((p_x_flight_schedules_tbl(i).est_departure_time>EST_DEPARTURE_TIME AND p_x_flight_schedules_tbl(i).est_departure_time<EST_ARRIVAL_TIME))
2080 AND
2081 ((p_x_flight_schedules_tbl(i).est_arrival_time>EST_DEPARTURE_TIME and p_x_flight_schedules_tbl(i).est_arrival_time<EST_ARRIVAL_TIME))
2082 )
2083 OR ((EST_DEPARTURE_TIME>p_x_flight_schedules_tbl(i).est_departure_time and EST_DEPARTURE_TIME<p_x_flight_schedules_tbl(i).est_arrival_time))
2084 OR ((EST_ARRIVAL_TIME>p_x_flight_schedules_tbl(i).est_departure_time and EST_ARRIVAL_TIME<p_x_flight_schedules_tbl(i).est_arrival_time))
2085 )
2086 AND unit_schedule_id <> p_x_flight_schedules_tbl(i).unit_schedule_id;
2087 END IF;
2088
2089 IF l_overlap_us_count > 0 THEN
2090 -- There is an overlap in times.
2091 FND_MESSAGE.set_name('AHL', 'AHL_UA_US_TIME_OVERLAP');
2092 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(
2093 p_flight_schedule_rec => p_x_flight_schedules_tbl(i)
2094 ) );
2095
2096 p_x_flight_schedules_tbl(i).conflict_message := FND_MESSAGE.GET;
2097 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2098 fnd_log.string
2099 (
2100 fnd_log.level_error,
2101 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2102 'Overlap in flight times specified,'||
2103 'this is a warning and not a error for '||p_x_flight_schedules_tbl(i).unit_schedule_id
2104 );
2105 END IF;
2106 END IF;
2107 END LOOP;
2108 END Create_Flight_Schedules;
2109
2110
2111 PROCEDURE Update_Flight_Schedules(
2112 p_module_type IN VARCHAR2,
2113 x_return_status OUT NOCOPY VARCHAR2,
2114 p_x_flight_schedules_tbl IN OUT NOCOPY FLIGHT_SCHEDULES_TBL_TYPE
2115 )
2116 IS
2117
2118 l_api_name CONSTANT VARCHAR2(30) := 'Update_Flight_Schedules';
2119 l_api_version CONSTANT NUMBER := 1;
2120 l_msg_count NUMBER;
2121 l_msg_data VARCHAR2(150);
2122 l_dummy VARCHAR2(1);
2123
2124 -- Cursor for getting old flight schedule details.
2125 CURSOR old_flight_rec_csr(p_unit_schedule_id IN NUMBER)
2126 IS
2127 SELECT est_arrival_time, arrival_org_id, arrival_dept_id
2128 FROM AHL_UNIT_SCHEDULES
2129 WHERE unit_schedule_id = p_unit_schedule_id;
2130
2131 l_old_flight_rec old_flight_rec_csr%ROWTYPE;
2132 l_visit_sync_rec visit_sync_rec_type;
2133
2134 l_is_sync_needed VARCHAR2(1);
2135 l_overlap_us_count NUMBER;
2136
2137 BEGIN
2138 -- Initialize return status to success initially
2139 x_return_status:=FND_API.G_RET_STS_SUCCESS;
2140
2141 -- Validate all inputs to the API
2142 FOR i IN p_x_flight_schedules_tbl.FIRST..p_x_flight_schedules_tbl.LAST
2143 LOOP
2144 IF ( p_x_flight_schedules_tbl(i).DML_OPERATION = 'U' )
2145 THEN
2146 -- Validate all attributes (Item level validation)
2147 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2148 fnd_log.string
2149 (
2150 fnd_log.level_statement,
2151 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2152 'Validate attributes before creating flight schedules...'
2153 );
2154 END IF;
2155
2156 validate_attributes
2157 (
2158 p_x_flight_schedules_tbl(i),
2159 x_return_status
2160 );
2161
2162 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2163 fnd_log.string
2164 (
2165 fnd_log.level_statement,
2166 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2167 'p_x_flight_schedules_tbl('||i||').DML_OPERATION : ' || p_x_flight_schedules_tbl(i).DML_OPERATION
2168 );
2169 END IF;
2170
2171 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2172 fnd_log.string
2173 (
2174 fnd_log.level_statement,
2175 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2176 'Calling convert_values_to_ids..'
2177 );
2178 END IF;
2179
2180 IF ( p_module_type = 'OAF' OR p_module_type = 'JSP' )
2181 THEN
2182 convert_values_to_ids
2183 (
2184 p_x_flight_schedules_tbl(i),
2185 x_return_status
2186 );
2187
2188 -- If any severe error occurs, then, abort API.
2189 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2190 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
2191 fnd_log.string
2192 (
2193 fnd_log.level_exception,
2194 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
2195 'convert_values_to_ids returned with expected error..'
2196 );
2197 END IF;
2198 RAISE FND_API.G_EXC_ERROR;
2199 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2200 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
2201 fnd_log.string
2202 (
2203 fnd_log.level_exception,
2204 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
2205 'convert_values_to_ids returned with un-expected error..'
2206 );
2207 END IF;
2208 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2209 END IF;-- return status
2210 END IF;-- module type
2211
2212 -- Default missing and unchanged attributes.
2213 IF ( p_module_type <> 'OAF' )
2214 THEN
2215 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2216 fnd_log.string
2217 (
2218 fnd_log.level_statement,
2219 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2220 'default_unchanged_attributes for update operation. Module type is '||p_module_type
2221 );
2222 END IF;
2223
2224 default_unchanged_attributes
2225 (
2226 p_x_flight_schedule_rec => p_x_flight_schedules_tbl(i)
2227 );
2228
2229 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2230 fnd_log.string
2231 (
2232 fnd_log.level_statement,
2233 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2234 'call validate_mandatory_inputs to check all missing mandatory fields'
2235 );
2236 END IF;
2237 END IF;
2238
2239 validate_mandatory_fields
2240 (
2241 p_x_flight_schedules_tbl(i),
2242 x_return_status
2243 );
2244
2245 -- If any severe error occurs, then, abort API.
2246 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2247 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
2248 fnd_log.string
2249 (
2250 fnd_log.level_exception,
2251 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
2252 'validate_mandatory_fields returned with expected error..'
2253 );
2254 END IF;
2255 RAISE FND_API.G_EXC_ERROR;
2256 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2257 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
2258 fnd_log.string
2259 (
2260 fnd_log.level_exception,
2261 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
2262 'validate_mandatory_fields returned with un-expected error..'
2263 );
2264 END IF;
2265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2266 END IF; -- return status
2267
2268
2269 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2270 THEN
2271 fnd_log.string
2272 (
2273 fnd_log.level_statement,
2274 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2275 'Validate all records before DML '
2276 );
2277 END IF;
2278
2279 validate_update
2280 (
2281 p_x_flight_schedules_tbl(i),
2282 x_return_status
2283 );
2284
2285 -- If any severe error occurs, then, abort API.
2286 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2287 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
2288 fnd_log.string
2289 (
2290 fnd_log.level_exception,
2291 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
2292 'validate_update returned with expected error..'
2293 );
2294 END IF;
2295 RAISE FND_API.G_EXC_ERROR;
2296 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2297 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
2298 fnd_log.string
2299 (
2300 fnd_log.level_exception,
2301 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
2302 'validate_update returned with un-expected error..'
2303 );
2304 END IF;
2305 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2306 END IF;
2307
2308 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2309 fnd_log.string
2310 (
2311 fnd_log.level_statement,
2312 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2313 'Validate all records before DML '
2314 );
2315 END IF;
2316
2317 validate_record
2318 (
2319 p_x_flight_schedules_tbl(i),
2320 x_return_status
2321 );
2322
2323 -- If any severe error occurs, then, abort API.
2324 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2325 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
2326 fnd_log.string
2327 (
2328 fnd_log.level_exception,
2329 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
2330 'validate_record returned with expected error..'
2331 );
2332 END IF;
2333 RAISE FND_API.G_EXC_ERROR;
2334 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2335 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
2336 fnd_log.string
2337 (
2338 fnd_log.level_exception,
2339 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
2340 'validate_record returned with un-expected error..'
2341 );
2342 END IF;
2343 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2344 END IF;
2345
2346 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2347 fnd_log.string
2348 (
2349 fnd_log.level_statement,
2350 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2351 'Updating Flight scehdule ->'||p_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID
2352 );
2353 END IF;
2354
2355 OPEN old_flight_rec_csr(p_x_flight_schedules_tbl(i).unit_schedule_id);
2356 FETCH old_flight_rec_csr INTO l_old_flight_rec;
2357
2358 -- Update the record
2359 UPDATE AHL_UNIT_SCHEDULES SET
2360 UNIT_SCHEDULE_ID = p_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID,
2361 FLIGHT_NUMBER = p_x_flight_schedules_tbl(i).FLIGHT_NUMBER,
2362 SEGMENT = p_x_flight_schedules_tbl(i).SEGMENT,
2363 DEPARTURE_DEPT_ID = p_x_flight_schedules_tbl(i).DEPARTURE_DEPT_ID,
2364 DEPARTURE_ORG_ID = p_x_flight_schedules_tbl(i).DEPARTURE_ORG_ID,
2365 ARRIVAL_DEPT_ID = p_x_flight_schedules_tbl(i).ARRIVAL_DEPT_ID,
2366 ARRIVAL_ORG_ID = p_x_flight_schedules_tbl(i).ARRIVAL_ORG_ID,
2367 EST_DEPARTURE_TIME = p_x_flight_schedules_tbl(i).EST_DEPARTURE_TIME,
2368 EST_ARRIVAL_TIME = p_x_flight_schedules_tbl(i).EST_ARRIVAL_TIME,
2369 ACTUAL_DEPARTURE_TIME = p_x_flight_schedules_tbl(i).ACTUAL_DEPARTURE_TIME,
2370 ACTUAL_ARRIVAL_TIME = p_x_flight_schedules_tbl(i).ACTUAL_ARRIVAL_TIME,
2371 PRECEDING_US_ID = p_x_flight_schedules_tbl(i).PRECEDING_US_ID,
2372 UNIT_CONFIG_HEADER_ID = p_x_flight_schedules_tbl(i).UNIT_CONFIG_HEADER_ID,
2373 VISIT_RESCHEDULE_MODE = p_x_flight_schedules_tbl(i).VISIT_RESCHEDULE_MODE,
2374 LAST_UPDATE_DATE = G_SYSDATE,
2375 LAST_UPDATED_BY = G_USER_ID,
2376 CREATION_DATE = G_SYSDATE,
2377 CREATED_BY = G_USER_ID,
2378 LAST_UPDATE_LOGIN = G_LOGIN_ID,
2379 OBJECT_VERSION_NUMBER = p_x_flight_schedules_tbl(i).OBJECT_VERSION_NUMBER + 1,
2380 ATTRIBUTE_CATEGORY = p_x_flight_schedules_tbl(i).ATTRIBUTE_CATEGORY,
2381 ATTRIBUTE1 = p_x_flight_schedules_tbl(i).ATTRIBUTE1,
2382 ATTRIBUTE2 = p_x_flight_schedules_tbl(i).ATTRIBUTE2,
2383 ATTRIBUTE3 = p_x_flight_schedules_tbl(i).ATTRIBUTE3,
2384 ATTRIBUTE4 = p_x_flight_schedules_tbl(i).ATTRIBUTE4,
2385 ATTRIBUTE5 = p_x_flight_schedules_tbl(i).ATTRIBUTE5,
2386 ATTRIBUTE6 = p_x_flight_schedules_tbl(i).ATTRIBUTE6,
2387 ATTRIBUTE7 = p_x_flight_schedules_tbl(i).ATTRIBUTE7,
2388 ATTRIBUTE8 = p_x_flight_schedules_tbl(i).ATTRIBUTE8,
2389 ATTRIBUTE9 = p_x_flight_schedules_tbl(i).ATTRIBUTE9,
2390 ATTRIBUTE10 = p_x_flight_schedules_tbl(i).ATTRIBUTE10,
2391 ATTRIBUTE11 = p_x_flight_schedules_tbl(i).ATTRIBUTE11,
2392 ATTRIBUTE12 = p_x_flight_schedules_tbl(i).ATTRIBUTE12,
2393 ATTRIBUTE13 = p_x_flight_schedules_tbl(i).ATTRIBUTE13,
2394 ATTRIBUTE14 = p_x_flight_schedules_tbl(i).ATTRIBUTE14,
2395 ATTRIBUTE15 = p_x_flight_schedules_tbl(i).ATTRIBUTE15
2396 WHERE
2397 unit_schedule_id = p_x_flight_schedules_tbl(i).unit_schedule_id
2398 AND object_version_number= p_x_flight_schedules_tbl(i).object_version_number;
2399
2400 -- If the record does not exist, then, abort API.
2401 IF ( SQL%ROWCOUNT = 0 ) THEN
2402 FND_MESSAGE.set_name('AHL','AHL_COM_RECORD_CHANGED');
2403 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(
2404 p_flight_schedule_rec => p_x_flight_schedules_tbl(i)
2405 ) );
2406 FND_MSG_PUB.add;
2407 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2408 fnd_log.string
2409 (
2410 fnd_log.level_error,
2411 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2412 'SQL Error, Update failed..'
2413 );
2414 END IF;
2415 END IF;
2416
2417 -- Set OUT values
2418 p_x_flight_schedules_tbl(i).object_version_number := p_x_flight_schedules_tbl(i).object_version_number + 1;
2419 --p_x_flight_schedule_rec.superuser_role := is_super_user;
2420
2421 -- Calculate visit synchronization details.
2422 l_is_sync_needed := 'N';
2423
2424
2425 IF old_flight_rec_csr%FOUND THEN
2426 -- Check if Estimated arrival time has changed.
2427 IF l_old_flight_rec.EST_ARRIVAL_TIME <> p_x_flight_schedules_tbl(i).EST_ARRIVAL_TIME
2428 THEN
2429 l_visit_sync_rec.CHANGED_ARRIVAL_TIME := p_x_flight_schedules_tbl(i).EST_ARRIVAL_TIME - l_old_flight_rec.EST_ARRIVAL_TIME;
2430 l_is_sync_needed := 'Y';
2431 END IF;
2432
2433 -- Check if Estimated arrival time has changed.
2434 IF l_old_flight_rec.ARRIVAL_ORG_ID <> p_x_flight_schedules_tbl(i).ARRIVAL_ORG_ID
2435 THEN
2436 -- Estimated arrival time has changed, so reschedule visit
2437 l_visit_sync_rec.CHANGED_ORG_ID := p_x_flight_schedules_tbl(i).ARRIVAL_ORG_ID;
2438 l_is_sync_needed := 'Y';
2439 END IF;
2440
2441 -- Check if Estimated arrival time has changed.
2442 IF l_old_flight_rec.ARRIVAL_DEPT_ID <> p_x_flight_schedules_tbl(i).ARRIVAL_DEPT_ID
2443 THEN
2444 -- Estimated arrival time has changed, so reschedule visit
2445 l_visit_sync_rec.CHANGED_DEPT_ID := p_x_flight_schedules_tbl(i).ARRIVAL_DEPT_ID;
2446 l_is_sync_needed := 'Y';
2447 END IF;
2448 END IF;
2449
2450 IF l_is_sync_needed = 'Y' THEN
2451
2452 --populate visit reschedule mode.
2453 l_visit_sync_rec.visit_reschedule_mode := p_x_flight_schedules_tbl(i).visit_reschedule_mode;
2454 l_visit_sync_rec.unit_schedule_id := p_x_flight_schedules_tbl(i).unit_schedule_id;
2455
2456 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2457 fnd_log.string
2458 (
2459 fnd_log.level_statement,
2460 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2461 'Synchronize visits affected if any for flight -> '||p_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID
2462 );
2463 END IF;
2464
2465 --Call visit synchronization function to synchronize visit with updated flight schedule
2466
2467 Synchronize_Visit_Details (
2468 x_return_status => x_return_status,
2469 p_visit_sync_rec => l_visit_sync_rec
2470 );
2471
2472 -- If any severe error occurs, then, abort API.
2473 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2474 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
2475 fnd_log.string
2476 (
2477 fnd_log.level_exception,
2478 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
2479 'Synchronize_Visit_Details returned with expected error..'
2480 );
2481 END IF;
2482 RAISE FND_API.G_EXC_ERROR;
2483 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2484 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
2485 fnd_log.string
2486 (
2487 fnd_log.level_exception,
2488 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
2489 'Synchronize_Visit_Details returned with un-expected error..'
2490 );
2491 END IF;
2492 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2493 END IF; -- return status check
2494 END IF; -- l_sync_flag
2495 END IF; -- DML Operation check
2496
2497 -- Check if overlaps are occurring. If there is any overlap, show warning.
2498 l_overlap_us_count := 0;
2499 IF p_x_flight_schedules_tbl(i).actual_departure_time IS NOT NULL AND p_x_flight_schedules_tbl(i).actual_arrival_time IS NOT NULL
2500 THEN
2501 SELECT count(unit_schedule_id) INTO l_overlap_us_count
2502 FROM AHL_UNIT_SCHEDULES
2503 WHERE unit_config_header_id = p_x_flight_schedules_tbl(i).unit_config_header_id
2504 AND (
2505 (
2506 (p_x_flight_schedules_tbl(i).actual_departure_time between ACTUAL_DEPARTURE_TIME and ACTUAL_ARRIVAL_TIME)
2507 AND
2508 ( p_x_flight_schedules_tbl(i).actual_arrival_time between ACTUAL_DEPARTURE_TIME and ACTUAL_ARRIVAL_TIME)
2509 )
2510 OR (ACTUAL_DEPARTURE_TIME between p_x_flight_schedules_tbl(i).actual_departure_time and p_x_flight_schedules_tbl(i).actual_arrival_time)
2511 OR (ACTUAL_ARRIVAL_TIME between p_x_flight_schedules_tbl(i).actual_departure_time and p_x_flight_schedules_tbl(i).actual_arrival_time)
2512 )
2513 AND unit_schedule_id <> p_x_flight_schedules_tbl(i).unit_schedule_id;
2514 ELSIF p_x_flight_schedules_tbl(i).est_departure_time IS NOT NULL AND p_x_flight_schedules_tbl(i).est_arrival_time IS NOT NULL
2515 THEN
2516 SELECT count(unit_schedule_id) INTO l_overlap_us_count
2517 FROM AHL_UNIT_SCHEDULES
2518 WHERE unit_config_header_id = p_x_flight_schedules_tbl(i).unit_config_header_id
2519 /*AND (
2520 (
2521 (p_x_flight_schedules_tbl(i).est_departure_time between EST_DEPARTURE_TIME and EST_ARRIVAL_TIME)
2522 AND
2523 ( p_x_flight_schedules_tbl(i).est_arrival_time between EST_DEPARTURE_TIME and EST_ARRIVAL_TIME)
2524 )
2525 OR (EST_DEPARTURE_TIME between p_x_flight_schedules_tbl(i).est_departure_time and p_x_flight_schedules_tbl(i).est_arrival_time)
2526 OR (EST_ARRIVAL_TIME between p_x_flight_schedules_tbl(i).est_departure_time and p_x_flight_schedules_tbl(i).est_arrival_time)
2527 )*/
2528 AND (
2529 (
2530 ((p_x_flight_schedules_tbl(i).est_departure_time>EST_DEPARTURE_TIME AND p_x_flight_schedules_tbl(i).est_departure_time<EST_ARRIVAL_TIME))
2531 AND
2532 ((p_x_flight_schedules_tbl(i).est_arrival_time>EST_DEPARTURE_TIME and p_x_flight_schedules_tbl(i).est_arrival_time<EST_ARRIVAL_TIME))
2533 )
2534 OR ((EST_DEPARTURE_TIME>p_x_flight_schedules_tbl(i).est_departure_time and EST_DEPARTURE_TIME<p_x_flight_schedules_tbl(i).est_arrival_time))
2535 OR ((EST_ARRIVAL_TIME>p_x_flight_schedules_tbl(i).est_departure_time and EST_ARRIVAL_TIME<p_x_flight_schedules_tbl(i).est_arrival_time))
2536 )
2537 AND unit_schedule_id <> p_x_flight_schedules_tbl(i).unit_schedule_id;
2538 END IF;
2539
2540 IF l_overlap_us_count > 0 THEN
2541 -- There is an overlap in times.
2542 FND_MESSAGE.set_name('AHL', 'AHL_UA_US_TIME_OVERLAP');
2543 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(
2544 p_flight_schedule_rec => p_x_flight_schedules_tbl(i)
2545 ) );
2546
2547 p_x_flight_schedules_tbl(i).conflict_message := FND_MESSAGE.GET;
2548 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2549 fnd_log.string
2550 (
2551 fnd_log.level_error,
2552 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2553 'Overlap in flight times specified,'||
2554 'this is a warning and not a error for '||p_x_flight_schedules_tbl(i).unit_schedule_id
2555 );
2556 END IF;
2557 END IF;
2558 END LOOP;
2559
2560 END Update_Flight_Schedules;
2561
2562
2563 PROCEDURE Delete_Flight_Schedules(
2564 x_return_status OUT NOCOPY VARCHAR2,
2565 x_msg_count OUT NOCOPY NUMBER,
2566 x_msg_data OUT NOCOPY VARCHAR2,
2567 p_x_flight_schedules_tbl IN OUT NOCOPY FLIGHT_SCHEDULES_TBL_TYPE
2568 )
2569 IS
2570
2571 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Flight_Schedules';
2572 l_api_version CONSTANT NUMBER := 1.0;
2573 l_msg_count NUMBER;
2574 l_msg_data VARCHAR2(150);
2575
2576 BEGIN
2577
2578 -- Initialize return status to success initially
2579 x_return_status:=FND_API.G_RET_STS_SUCCESS;
2580
2581 -- Validate all inputs to the API
2582 FOR i IN p_x_flight_schedules_tbl.FIRST..p_x_flight_schedules_tbl.LAST
2583 LOOP
2584
2585 IF ( p_x_flight_schedules_tbl(i).DML_OPERATION = 'D' )
2586 THEN
2587 -- Validate all attributes (Item level validation)
2588 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2589 fnd_log.string
2590 (
2591 fnd_log.level_statement,
2592 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2593 'Validate attributes before creating flight schedules...'
2594 );
2595 END IF;
2596
2597 validate_attributes
2598 (
2599 p_x_flight_schedules_tbl(i),
2600 x_return_status
2601 );
2602
2603 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2604 fnd_log.string
2605 (
2606 fnd_log.level_statement,
2607 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2608 'Validate all records before DML '
2609 );
2610 END IF;
2611
2612 validate_record
2613 (
2614 p_x_flight_schedules_tbl(i),
2615 x_return_status
2616 );
2617
2618 -- If any severe error occurs, then, abort API.
2619 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2620 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
2621 fnd_log.string
2622 (
2623 fnd_log.level_exception,
2624 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
2625 'validate_record returned with expected error..'
2626 );
2627 END IF;
2628 RAISE FND_API.G_EXC_ERROR;
2629 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2630 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
2631 fnd_log.string
2632 (
2633 fnd_log.level_exception,
2634 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
2635 'validate_record returned with un-expected error..'
2636 );
2637 END IF;
2638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2639 END IF;
2640
2641 -- Delete the record
2642 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2643 fnd_log.string
2644 (
2645 fnd_log.level_statement,
2646 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2647 'Deleting Flight scehdule -> '||p_x_flight_schedules_tbl(i).unit_schedule_id
2648 );
2649 END IF;
2650
2651 -- find all transit visits for the particular flight schedule
2652 OPEN get_flight_visit( p_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID );
2653 LOOP
2654 FETCH get_flight_visit into l_flight_visit;
2655 EXIT WHEN get_flight_visit%NOTFOUND;
2656 l_visit_count := get_flight_visit%ROWCOUNT;
2657 l_visit_tbl(l_visit_count).visit_id := l_flight_visit.visit_id;
2658 l_visit_tbl(l_visit_count).object_version_number := l_flight_visit.object_version_number;
2659 l_visit_tbl(l_visit_count).operation_flag := 'X';
2660 END LOOP;
2661 CLOSE get_flight_visit;
2662
2663 -- delete all associations of the flight schedule to its own transit visits
2664 AHL_VWP_VISITS_PVT.DELETE_FLIGHT_ASSOC(
2665 p_x_flight_schedules_tbl(i).unit_schedule_id,
2666 x_return_status
2667 );
2668
2669 -- If any severe error occurs, then, abort API.
2670 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2671 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
2672 fnd_log.string
2673 (
2674 fnd_log.level_exception,
2675 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
2676 'AHL_VWP_VISITS_PVT.Process_Visit returned with expected error..'
2677 );
2678 END IF;
2679 RAISE FND_API.G_EXC_ERROR;
2680 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2681 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
2682 fnd_log.string
2683 (
2684 fnd_log.level_exception,
2685 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
2686 'validate_record returned with un-expected error..'
2687 );
2688 END IF;
2689 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2690 END IF;
2691
2692 -- delete the transit visits found
2693 AHL_VWP_VISITS_PVT.Process_Visit (
2694 p_api_version => 1.0,
2695 p_init_msg_list => FND_API.g_false,
2696 p_commit => FND_API.g_false,
2697 p_validation_level => FND_API.g_valid_level_full,
2698 p_module_type => 'JSP',
2699 p_x_Visit_tbl => l_visit_tbl,
2700 x_return_status => x_return_status,
2701 x_msg_count => l_msg_count,
2702 x_msg_data => l_msg_data
2703 );
2704
2705
2706 -- If any severe error occurs, then, abort API.
2707 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2708 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
2709 fnd_log.string
2710 (
2711 fnd_log.level_exception,
2712 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
2713 'AHL_VWP_VISITS_PVT.Process_Visit returned with expected error..'
2714 );
2715 END IF;
2716 RAISE FND_API.G_EXC_ERROR;
2717 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2718 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
2719 fnd_log.string
2720 (
2721 fnd_log.level_exception,
2722 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
2723 'validate_record returned with un-expected error..'
2724 );
2725 END IF;
2726 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2727 END IF;
2728 DELETE FROM AHL_UNIT_SCHEDULES
2729 WHERE unit_schedule_id = p_x_flight_schedules_tbl(i).unit_schedule_id
2730 AND object_version_number= p_x_flight_schedules_tbl(i).object_version_number;
2731
2732 -- If the record does not exist, then, abort API.
2733 IF ( SQL%ROWCOUNT = 0 ) THEN
2734 FND_MESSAGE.set_name('AHL','AHL_COM_RECORD_CHANGED');
2735 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(
2736 p_flight_schedule_rec => p_x_flight_schedules_tbl(i)
2737 ) );
2738 FND_MSG_PUB.add;
2739 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2740 fnd_log.string
2741 (
2742 fnd_log.level_error,
2743 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2744 'SQL Error, Delete failed..'
2745 );
2746 END IF;
2747 END IF; -- error handling
2748 END IF; -- dml operation check
2749 END LOOP;
2750 END Delete_Flight_Schedules;
2751
2752
2753 -----------------------------------------------------------------------------------------------------
2754 -- Procedure for creating/updating/deleting Flight Schedules.
2755 -----------------------------------------------------------------------------------------------------
2756 PROCEDURE Process_Flight_Schedules(
2757 p_api_version IN NUMBER :=1.0,
2758 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
2759 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
2760 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
2761 p_default IN VARCHAR2 :=FND_API.G_FALSE,
2762 p_module_type IN VARCHAR2 :=NULL,
2763 x_return_status OUT NOCOPY VARCHAR2,
2764 x_msg_count OUT NOCOPY NUMBER,
2765 x_msg_data OUT NOCOPY VARCHAR2,
2766 p_x_flight_schedules_tbl IN OUT NOCOPY FLIGHT_SCHEDULES_TBL_TYPE
2767 )
2768 IS
2769 l_api_name CONSTANT VARCHAR2(30) := 'Process_Flight_Schedules';
2770 l_api_version CONSTANT NUMBER := 1.0;
2771
2772 BEGIN
2773 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2774 fnd_log.string
2775 (
2776 fnd_log.level_procedure,
2777 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
2778 'At the start of Process_Flight_Schedules'
2779 );
2780 END IF;
2781
2782 SAVEPOINT process_flight_schedules_pvt;
2783
2784 -- Initialize return status to success initially
2785 x_return_status:=FND_API.G_RET_STS_SUCCESS;
2786
2787 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2788 p_api_version,
2789 l_api_name,G_PKG_NAME)
2790 THEN
2791 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2792 END IF;
2793
2794 -- Initialize message list if p_init_msg_list is set to TRUE.
2795 IF FND_API.to_boolean(p_init_msg_list) THEN
2796 FND_MSG_PUB.initialize;
2797 END IF;
2798
2799 --Local procedure for Deleting Flight Schedules
2800 Delete_Flight_Schedules(
2801 x_return_status => x_return_status,
2802 x_msg_count => x_msg_count,
2803 x_msg_data => x_msg_data,
2804 p_x_flight_schedules_tbl => p_x_flight_schedules_tbl
2805 );
2806
2807 --Local procedure for Updating Flight Schedules
2808 Update_Flight_Schedules(
2809 p_module_type => p_module_type,
2810 x_return_status => x_return_status,
2811 p_x_flight_schedules_tbl => p_x_flight_schedules_tbl
2812 );
2813
2814 --Local procedure for Creating Flight Schedules
2815 Create_Flight_Schedules(
2816 p_module_type => p_module_type,
2817 x_return_status => x_return_status,
2818 p_x_flight_schedules_tbl => p_x_flight_schedules_tbl
2819 );
2820
2821
2822 -- Update Preceding Unit Flight Schedule Id after all DMLs.
2823 Sequence_Flight_Schedules(
2824 p_x_flight_schedules_tbl => p_x_flight_schedules_tbl
2825 );
2826
2827
2828 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2829 fnd_log.string
2830 (
2831 fnd_log.level_statement,
2832 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2833 'Done DML , committing the work'
2834 );
2835 END IF;
2836
2837 IF FND_API.TO_BOOLEAN(p_commit) THEN
2838 COMMIT;
2839 END IF;
2840
2841 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2842 fnd_log.string
2843 (
2844 fnd_log.level_procedure,
2845 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.end',
2846 'At the end of Process_Flight_Schedules'
2847 );
2848 END IF;
2849 EXCEPTION
2850 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2851 ROLLBACK TO process_flight_schedules_pvt;
2852 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2853 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2854 p_count => x_msg_count,
2855 p_data => x_msg_data);
2856
2857 WHEN FND_API.G_EXC_ERROR THEN
2858 ROLLBACK TO process_flight_schedules_pvt;
2859 x_return_status := FND_API.G_RET_STS_ERROR;
2860 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2861 p_count => x_msg_count,
2862 p_data => X_msg_data);
2863
2864 WHEN OTHERS THEN
2865 ROLLBACK TO process_flight_schedules_pvt;
2866 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2867 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2868 THEN
2869 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2870 p_procedure_name => l_api_name,
2871 p_error_text => SUBSTR(SQLERRM,1,240));
2872
2873 END IF;
2874 FND_MSG_PUB.count_and_get
2875 (
2876 p_count => x_msg_count,
2877 p_data => x_msg_data,
2878 p_encoded => FND_API.G_FALSE
2879 );
2880 END Process_Flight_Schedules;
2881
2882 -----------------------------------------------------------------------------------------------------
2883 -- Procedure for Validating a Flight Schedule.
2884 -----------------------------------------------------------------------------------------------------
2885 PROCEDURE Validate_Flight_Schedule(
2886 p_api_version IN NUMBER :=1.0,
2887 x_return_status OUT NOCOPY VARCHAR2,
2888 x_msg_count OUT NOCOPY NUMBER,
2889 x_msg_data OUT NOCOPY VARCHAR2,
2890 p_unit_config_id IN NUMBER,
2891 p_unit_schedule_id IN NUMBER
2892 )
2893 IS
2894
2895 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Flight_Schedule';
2896 l_api_version CONSTANT NUMBER := 1;
2897 l_dummy VARCHAR2(1);
2898
2899 CURSOR check_flight_exists_csr
2900 (
2901 p_unit_schedule_id number,
2902 p_unit_config_id number
2903 )
2904 IS
2905 SELECT 'X'
2906 FROM AHL_UNIT_SCHEDULES
2907 WHERE unit_schedule_id = p_unit_schedule_id
2908 AND unit_config_header_id = nvl(p_unit_config_id, unit_config_header_id);
2909
2910 BEGIN
2911 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2912 fnd_log.string
2913 (
2914 fnd_log.level_procedure,
2915 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
2916 'At the start of '||l_api_name
2917 );
2918 END IF;
2919
2920 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2921 fnd_log.string
2922 (
2923 fnd_log.level_statement,
2924 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2925 'p_unit_schedule_id -> '||p_unit_schedule_id
2926 ||', p_unit_config_id -> '||p_unit_config_id
2927 );
2928 END IF;
2929
2930 -- Initialize return status to success initially
2931 x_return_status:=FND_API.G_RET_STS_SUCCESS;
2932
2933 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2934 p_api_version,
2935 l_api_name,G_PKG_NAME)
2936 THEN
2937 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2938 END IF;
2939
2940 -- Throw error if p_unit_schedule_id is null
2941
2942 IF p_unit_schedule_id IS NULL THEN
2943 FND_MESSAGE.set_name('AHL','AHL_UA_US_NOT_FOUND');
2944 FND_MSG_PUB.add;
2945 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2946 fnd_log.string
2947 (
2948 fnd_log.level_error,
2949 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2950 'unit schedule id is null..'
2951 );
2952 END IF;
2953 x_return_status := FND_API.G_RET_STS_ERROR;
2954 RAISE FND_API.G_EXC_ERROR;
2955 ELSE
2956 OPEN check_flight_exists_csr(p_unit_schedule_id, p_unit_config_id);
2957 FETCH check_flight_exists_csr INTO l_dummy;
2958 IF check_flight_exists_csr%NOTFOUND THEN
2959 FND_MESSAGE.set_name('AHL','AHL_UA_US_NOT_FOUND');
2960 FND_MSG_PUB.add;
2961 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2962 fnd_log.string
2963 (
2964 fnd_log.level_error,
2965 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2966 'unit schedule Record is invalid..'
2967 );
2968 END IF;
2969 x_return_status := FND_API.G_RET_STS_ERROR;
2970 CLOSE check_flight_exists_csr;
2971 RAISE FND_API.G_EXC_ERROR;
2972 END IF;
2973 CLOSE check_flight_exists_csr;
2974 END IF;
2975
2976 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2977 fnd_log.string
2978 (
2979 fnd_log.level_procedure,
2980 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.end',
2981 'At the start of '||l_api_name
2982 );
2983 END IF;
2984 EXCEPTION
2985 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2986 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2987 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2988 p_count => x_msg_count,
2989 p_data => x_msg_data);
2990
2991 WHEN FND_API.G_EXC_ERROR THEN
2992 x_return_status := FND_API.G_RET_STS_ERROR;
2993 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2994 p_count => x_msg_count,
2995 p_data => X_msg_data);
2996
2997 WHEN OTHERS THEN
2998 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2999 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3000 THEN
3001 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3002 p_procedure_name => l_api_name,
3003 p_error_text => SUBSTR(SQLERRM,1,240));
3004
3005 END IF;
3006 FND_MSG_PUB.count_and_get
3007 (
3008 p_count => x_msg_count,
3009 p_data => x_msg_data,
3010 p_encoded => FND_API.G_FALSE
3011 );
3012
3013 END Validate_Flight_Schedule;
3014
3015
3016 ------------------------------------------------------------------------------------------------
3017 -- Function which checks if current user is super user or not.
3018 ------------------------------------------------------------------------------------------------
3019 FUNCTION is_super_user
3020
3021 RETURN VARCHAR2
3022 IS
3023 BEGIN
3024
3025 IF (FND_FUNCTION.TEST('AHL_UA_SUPER_USER'))
3026 THEN
3027 RETURN FND_API.G_TRUE;
3028 --RETURN 'Y';
3029 ELSE
3030 RETURN FND_API.G_FALSE;
3031 --RETURN 'N';
3032 END IF;
3033
3034 END is_super_user;
3035
3036 ------------------------------------------------------------------------------------------------
3037 -- Function to check if delete is allowed for an unit schedule record
3038 ------------------------------------------------------------------------------------------------
3039 FUNCTION is_delete_allowed
3040 (
3041 p_unit_schedule_id IN NUMBER,
3042 p_is_super_user IN VARCHAR2
3043 )
3044 RETURN VARCHAR2
3045 IS
3046 --Cursor for checking if actuals are entered for the current unit schedule record.
3047 CURSOR get_curr_actuals_csr(p_unit_schedule_id IN NUMBER)
3048 IS
3049 SELECT actual_departure_time, actual_arrival_time
3050 FROM AHL_UNIT_SCHEDULES
3051 WHERE unit_schedule_id = p_unit_schedule_id;
3052 l_curr_acutals_rec get_curr_actuals_csr%ROWTYPE;
3053
3054 --l_is_super_user VARCHAR2(1);
3055 l_api_name CONSTANT VARCHAR2(30) := 'is_delete_allowed';
3056
3057 BEGIN
3058 --l_is_super_user := p_is_super_user;
3059 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3060 fnd_log.string
3061 (
3062 fnd_log.level_procedure,
3063 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
3064 'At the start of '||l_api_name
3065 );
3066 END IF;
3067
3068 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3069 fnd_log.string
3070 (
3071 fnd_log.level_statement,
3072 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3073 'Flight Schedule id --> '|| p_unit_schedule_id ||' and is super user? '||p_is_super_user
3074 );
3075 END IF;
3076
3077 --check current events actuals
3078 OPEN get_curr_actuals_csr(p_unit_schedule_id);
3079 FETCH get_curr_actuals_csr
3080 INTO
3081 l_curr_acutals_rec.actual_departure_time,
3082 l_curr_acutals_rec.actual_arrival_time;
3083
3084 IF (l_curr_acutals_rec.actual_departure_time IS NULL
3085 AND l_curr_acutals_rec.actual_arrival_time IS NULL)
3086 THEN
3087 RETURN FND_API.G_TRUE;
3088 ELSIF (p_is_super_user = FND_API.G_TRUE
3089 AND l_curr_acutals_rec.actual_departure_time IS NOT NULL
3090 AND l_curr_acutals_rec.actual_arrival_time IS NOT NULL )
3091 THEN
3092 RETURN FND_API.G_TRUE;
3093 ELSE
3094 RETURN FND_API.G_FALSE;
3095 END IF;
3096
3097 END is_delete_allowed;
3098
3099 ------------------------------------------------------------------------------------------------
3100 -- Function to check if update is allowed for an unit schedule record
3101 ------------------------------------------------------------------------------------------------
3102
3103 FUNCTION is_update_allowed
3104 (
3105 p_unit_schedule_id IN NUMBER,
3106 p_is_super_user IN VARCHAR2
3107 )
3108 RETURN VARCHAR2
3109 IS
3110
3111 --Cursor for getting the succeeding event of an Unit Schedule.
3112 CURSOR get_succeeding_us_csr(p_unit_schedule_id IN NUMBER) IS
3113 SELECT actual_departure_time, actual_arrival_time
3114 FROM AHL_UNIT_SCHEDULES
3115 WHERE preceding_us_id = p_unit_schedule_id;
3116
3117 l_api_name CONSTANT VARCHAR2(30) := 'is_update_allowed';
3118 l_succeeding_us_rec get_succeeding_us_csr%ROWTYPE;
3119 l_actual_recorded VARCHAR2(1);
3120
3121 BEGIN
3122
3123 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3124 fnd_log.string
3125 (
3126 fnd_log.level_procedure,
3127 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
3128 'At the start of '||l_api_name
3129 );
3130 END IF;
3131
3132 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3133 fnd_log.string
3134 (
3135 fnd_log.level_statement,
3136 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3137 'Flight Schedule id --> '|| p_unit_schedule_id ||' and is super user? '||p_is_super_user
3138 );
3139 END IF;
3140
3141 OPEN get_succeeding_us_csr(p_unit_schedule_id);
3142 FETCH get_succeeding_us_csr INTO l_succeeding_us_rec;
3143 CLOSE get_succeeding_us_csr;
3144
3145 l_actual_recorded := 'N';
3146
3147 IF l_succeeding_us_rec.actual_departure_time IS NOT NULL OR
3148 l_succeeding_us_rec.actual_arrival_time IS NOT NULL
3149 THEN
3150 l_actual_recorded := 'Y';
3151 END IF;
3152
3153 IF l_actual_recorded = 'N'
3154 THEN
3155 RETURN FND_API.G_TRUE;
3156 ELSIF l_actual_recorded = 'Y' AND p_is_super_user = FND_API.G_TRUE
3157 THEN
3158 RETURN FND_API.G_TRUE;
3159 ELSE
3160 RETURN FND_API.G_FALSE;
3161 END IF;
3162
3163 END is_update_allowed;
3164 END AHL_UA_FLIGHT_SCHEDULES_PVT;