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