[Home] [Help]
PACKAGE BODY: APPS.AHL_UA_FLIGHT_SCHEDULES_PUB
Source
1 PACKAGE BODY AHL_UA_FLIGHT_SCHEDULES_PUB AS
2 /* $Header: AHLPUFSB.pls 120.4 2006/09/15 23:16:44 sikumar noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) :='AHL_UA_FLIGHT_SCHEDULES_PUB';
5
6 -- Flag for determining wether to use Actual dates or Estimated dates.
7 G_USE_ACTUALS CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
8
9 PROCEDURE Get_Flight_Schedule_Details
10 (
11 -- standard IN params
12 p_api_version IN NUMBER,
13 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
14 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
15 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
16 p_default IN VARCHAR2 :=FND_API.G_FALSE,
17 p_module_type IN VARCHAR2 :=NULL,
18 -- standard OUT params
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2,
22 -- procedure params
23 p_flight_search_rec IN FLIGHT_SEARCH_REC_TYPE,
24 x_flight_schedules_tbl OUT NOCOPY AHL_UA_FLIGHT_SCHEDULES_PVT.FLIGHT_SCHEDULES_TBL_TYPE
25 )
26 IS
27
28 l_api_name CONSTANT VARCHAR2(30) := 'SEARCH_FLIGHT_SCHEDULES';
29 l_api_version CONSTANT NUMBER := 1.0;
30 l_return_status VARCHAR2(1);
31 l_search_Query VARCHAR2(5000);
32 i NUMBER;
33 l_flight_schedule_csr AHL_OSP_UTIL_PKG.ahl_search_csr;
34 l_bind_value_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
35 l_bind_index NUMBER;
36 l_super_user VARCHAR2(1);
37 l_unit_schedule_id NUMBER;
38 l_flight_schedule_rec AHL_UA_FLIGHT_SCHEDULES_PVT.FLIGHT_SCHEDULE_REC_TYPE;
39 BEGIN
40
41 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
42 fnd_log.string
43 (
44 fnd_log.level_procedure,
45 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
46 'At the start of PLSQL procedure'
47 );
48 END IF;
49
50 --savepoint is not required for procedures which does only query
51 --SAVEPOINT search_flight_schedules_pub;
52
53 -- Initialize return status to success initially
54 x_return_status:=FND_API.G_RET_STS_SUCCESS;
55
56 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
57 p_api_version,
58 l_api_name,G_PKG_NAME)
59 THEN
60 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
61 END IF;
62
63 -- Initialize message list if p_init_msg_list is set to TRUE.
64 IF FND_API.to_boolean(p_init_msg_list) THEN
65 FND_MSG_PUB.initialize;
66 END IF;
67
68 -- Validations for dates-based search... If dates criteria applied to arrival / departure, then both start and end need to be passed
69 IF (p_flight_search_rec.DATE_APPLY_TO_FLAG IS NOT NULL AND (p_flight_search_rec.START_DATE IS NULL OR p_flight_search_rec.END_DATE IS NULL))
70 THEN
71 FND_MESSAGE.set_name('AHL', 'AHL_UA_SEARCH_DATE_APP_FLAG');
72 FND_MSG_PUB.add;
73
74 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
75 END IF;
76
77 -- Search query...
78 l_search_Query := 'SELECT
79 UNIT_SCHEDULE_ID,
80 FLIGHT_NUMBER, SEGMENT,
81 DEPARTURE_DEPT_ID,
82 DEPARTURE_DEPT_CODE,
83 DEPARTURE_ORG_ID,
84 DEPARTURE_ORG_CODE,
85 ARRIVAL_DEPT_ID,
86 ARRIVAL_DEPT_CODE,
87 ARRIVAL_ORG_ID,
88 ARRIVAL_ORG_CODE,
89 EST_DEPARTURE_TIME,
90 EST_ARRIVAL_TIME,
91 ACTUAL_DEPARTURE_TIME,
92 ACTUAL_ARRIVAL_TIME,
93 PRECEDING_US_ID,
94 CSI_ITEM_INSTANCE_ID,
95 UNIT_CONFIG_HEADER_ID,
96 UNIT_CONFIG_NAME,
97 VISIT_RESCHEDULE_MODE,
98 VISIT_RESCHEDULE_MEANING,
99 ITEM_NUMBER,
100 SERIAL_NUMBER,
101 OBJECT_VERSION_NUMBER,
102 ATTRIBUTE_CATEGORY,
103 ATTRIBUTE1,
104 ATTRIBUTE2,
105 ATTRIBUTE3,
106 ATTRIBUTE4,
107 ATTRIBUTE5,
108 ATTRIBUTE6,
109 ATTRIBUTE7,
110 ATTRIBUTE8,
111 ATTRIBUTE9,
112 ATTRIBUTE10,
113 ATTRIBUTE11,
114 ATTRIBUTE12,
115 ATTRIBUTE13,
116 ATTRIBUTE14,
117 ATTRIBUTE15
118 FROM
119 AHL_UNIT_SCHEDULES_V
120 WHERE AHL_UTIL_UC_PKG.GET_UC_STATUS_CODE(UNIT_CONFIG_HEADER_ID) NOT IN (''DRAFT'',''EXPIRED'')';
121
122 l_bind_index := 1;
123
124 IF p_flight_search_rec.unit_schedule_id IS NOT NULL THEN
125 l_search_Query := l_search_Query || ' AND UNIT_SCHEDULE_ID = :'||l_bind_index;
126 l_bind_value_tbl(l_bind_index) := p_flight_search_rec.unit_schedule_id;
127 l_bind_index := l_bind_index + 1;
128 ELSE
129 IF p_flight_search_rec.UNIT_NAME IS NOT NULL THEN
130 l_search_Query := l_search_Query || ' AND UPPER(UNIT_CONFIG_NAME) LIKE UPPER(RTRIM(:'||l_bind_index||'))';
131 l_bind_value_tbl(l_bind_index) := p_flight_search_rec.UNIT_NAME;
132 l_bind_index := l_bind_index + 1;
133 END IF;
134
135 IF p_flight_search_rec.FLIGHT_NUMBER IS NOT NULL THEN
136 l_search_Query := l_search_Query || ' AND UPPER(FLIGHT_NUMBER) LIKE UPPER(RTRIM(:'||l_bind_index||'))';
137 l_bind_value_tbl(l_bind_index) := p_flight_search_rec.FLIGHT_NUMBER;
138 l_bind_index := l_bind_index + 1;
139 END IF;
140
141 IF p_flight_search_rec.ITEM_NUMBER IS NOT NULL THEN
142 l_search_Query := l_search_Query || ' AND UPPER(ITEM_NUMBER) LIKE UPPER(RTRIM(:'||l_bind_index||'))';
143 l_bind_value_tbl(l_bind_index) := p_flight_search_rec.ITEM_NUMBER;
144 l_bind_index := l_bind_index + 1;
145 END IF;
146
147 IF p_flight_search_rec.SERIAL_NUMBER IS NOT NULL THEN
148 l_search_Query := l_search_Query || ' AND UPPER(SERIAL_NUMBER) LIKE UPPER(RTRIM(:'||l_bind_index||'))';
149 l_bind_value_tbl(l_bind_index) := p_flight_search_rec.SERIAL_NUMBER;
150 l_bind_index := l_bind_index + 1;
151 END IF;
152
153 IF p_flight_search_rec.ARRIVAL_ORG_CODE IS NOT NULL THEN
154 l_search_Query := l_search_Query || ' AND UPPER(ARRIVAL_ORG_CODE) LIKE UPPER(RTRIM(:'||l_bind_index||'))';
155 l_bind_value_tbl(l_bind_index) := p_flight_search_rec.ARRIVAL_ORG_CODE;
156 l_bind_index := l_bind_index + 1;
157 END IF;
158
159 IF p_flight_search_rec.ARRIVAL_DEPT_CODE IS NOT NULL THEN
160 l_search_Query := l_search_Query || ' AND UPPER(ARRIVAL_DEPT_CODE) LIKE UPPER(RTRIM(:'||l_bind_index||'))';
161 l_bind_value_tbl(l_bind_index) := p_flight_search_rec.ARRIVAL_DEPT_CODE;
162 l_bind_index := l_bind_index + 1;
163 END IF;
164
165 IF p_flight_search_rec.DEPARTURE_ORG_CODE IS NOT NULL THEN
166 l_search_Query := l_search_Query || ' AND UPPER(DEPARTURE_ORG_CODE) LIKE UPPER(RTRIM(:'||l_bind_index||'))';
167 l_bind_value_tbl(l_bind_index) := p_flight_search_rec.DEPARTURE_ORG_CODE;
168 l_bind_index := l_bind_index + 1;
169 END IF;
170
171 IF p_flight_search_rec.DEPARTURE_DEPT_CODE IS NOT NULL THEN
172 l_search_Query := l_search_Query || ' AND UPPER(DEPARTURE_DEPT_CODE) LIKE UPPER(RTRIM(:'||l_bind_index||'))';
173 l_bind_value_tbl(l_bind_index) := p_flight_search_rec.DEPARTURE_DEPT_CODE;
174 l_bind_index := l_bind_index + 1;
175 END IF;
176
177 --Triway handling of start_date and End_date
178 IF p_flight_search_rec.START_DATE IS NOT NULL AND p_flight_search_rec.END_DATE IS NOT NULL
179 THEN
180 IF p_flight_search_rec.DATE_APPLY_TO_FLAG IS NULL
181 THEN
182 IF g_use_actuals = FND_API.G_TRUE
183 THEN
184 l_search_Query := l_search_Query ||' AND ( ';
185 l_search_Query := l_search_Query ||' ( ';
186 l_search_Query := l_search_Query ||' to_date(:start_time, ''DD-MON-RRRR HH24:MI:SS'') <= nvl(actual_departure_time, est_departure_time) and ';
187 l_search_Query := l_search_Query ||' nvl(actual_departure_time, est_departure_time) < to_date(:end_time, ''DD-MON-RRRR HH24:MI:SS'') ';
188 l_search_Query := l_search_Query ||' ) ';
189 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.START_DATE, 'DD-MON-RRRR HH24:MI:SS');
190 l_bind_index := l_bind_index + 1;
191 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.END_DATE, 'DD-MON-RRRR HH24:MI:SS');
192 l_bind_index := l_bind_index + 1;
193
194 l_search_Query := l_search_Query ||' or ';
195 l_search_Query := l_search_Query ||' ( ';
196 l_search_Query := l_search_Query ||' to_date(:start_time, ''DD-MON-RRRR HH24:MI:SS'') < nvl(actual_arrival_time, est_arrival_time) and ';
197 l_search_Query := l_search_Query ||' nvl(actual_arrival_time, est_arrival_time) <= to_date(:end_time, ''DD-MON-RRRR HH24:MI:SS'') ';
198 l_search_Query := l_search_Query ||' ) ';
199 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.START_DATE, 'DD-MON-RRRR HH24:MI:SS');
200 l_bind_index := l_bind_index + 1;
201 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.END_DATE, 'DD-MON-RRRR HH24:MI:SS');
202 l_bind_index := l_bind_index + 1;
203
204 l_search_Query := l_search_Query ||' or ';
205 l_search_Query := l_search_Query ||' ( ';
206 l_search_Query := l_search_Query ||' nvl(actual_departure_time, est_departure_time) <= to_date(:start_time, ''DD-MON-RRRR HH24:MI:SS'') and ';
207 l_search_Query := l_search_Query ||' to_date(:end_time, ''DD-MON-RRRR HH24:MI:SS'') <= nvl(actual_arrival_time, est_arrival_time) ';
208 l_search_Query := l_search_Query ||' ) ) ';
209 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.START_DATE, 'DD-MON-RRRR HH24:MI:SS');
210 l_bind_index := l_bind_index + 1;
211 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.END_DATE, 'DD-MON-RRRR HH24:MI:SS');
212 l_bind_index := l_bind_index + 1;
213 ELSE
214 l_search_Query := l_search_Query ||' AND ( ';
215 l_search_Query := l_search_Query ||' ( ';
216 l_search_Query := l_search_Query ||' to_date(:start_time1, ''DD-MON-RRRR HH24:MI:SS'') <= est_departure_time and ';
217 l_search_Query := l_search_Query ||' est_departure_time < to_date(:end_time1, ''DD-MON-RRRR HH24:MI:SS'') ';
218 l_search_Query := l_search_Query ||' ) ';
219 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.START_DATE, 'DD-MON-RRRR HH24:MI:SS');
220 l_bind_index := l_bind_index + 1;
221 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.END_DATE, 'DD-MON-RRRR HH24:MI:SS');
222 l_bind_index := l_bind_index + 1;
223
224 l_search_Query := l_search_Query ||' or ';
225 l_search_Query := l_search_Query ||' ( ';
226 l_search_Query := l_search_Query ||' to_date(:start_time2, ''DD-MON-RRRR HH24:MI:SS'') < est_arrival_time and ';
227 l_search_Query := l_search_Query ||' est_arrival_time <= to_date(:end_time2, ''DD-MON-RRRR HH24:MI:SS'') ';
228 l_search_Query := l_search_Query ||' ) ';
229 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.START_DATE, 'DD-MON-RRRR HH24:MI:SS');
230 l_bind_index := l_bind_index + 1;
231 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.END_DATE, 'DD-MON-RRRR HH24:MI:SS');
232 l_bind_index := l_bind_index + 1;
233
234 l_search_Query := l_search_Query ||' or ';
235 l_search_Query := l_search_Query ||' ( ';
236 l_search_Query := l_search_Query ||' est_departure_time <= to_date(:start_time3, ''DD-MON-RRRR HH24:MI:SS'') and ';
237 l_search_Query := l_search_Query ||' to_date(:end_time3, ''DD-MON-RRRR HH24:MI:SS'') <= est_arrival_time ';
238 l_search_Query := l_search_Query ||' ) ) ';
239 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.START_DATE, 'DD-MON-RRRR HH24:MI:SS');
240 l_bind_index := l_bind_index + 1;
241 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.END_DATE, 'DD-MON-RRRR HH24:MI:SS');
242 l_bind_index := l_bind_index + 1;
243 END IF;
244
245 ELSIF p_flight_search_rec.DATE_APPLY_TO_FLAG = G_APPLY_TO_ARRIVAL
246 THEN
247 IF g_use_actuals = FND_API.G_TRUE
248 THEN
249 l_search_Query := l_search_Query ||' AND ';
250 l_search_Query := l_search_Query ||' ( ';
251 l_search_Query := l_search_Query ||' to_date(:start_time, ''DD-MON-RRRR HH24:MI:SS'') < nvl(actual_arrival_time, est_arrival_time) and ';
252 l_search_Query := l_search_Query ||' nvl(actual_arrival_time, est_arrival_time) <= to_date(:end_time, ''DD-MON-RRRR HH24:MI:SS'') ';
253 l_search_Query := l_search_Query ||' ) ';
254 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.START_DATE, 'DD-MON-RRRR HH24:MI:SS');
255 l_bind_index := l_bind_index + 1;
256 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.END_DATE, 'DD-MON-RRRR HH24:MI:SS');
257 l_bind_index := l_bind_index + 1;
258 ELSE
259 l_search_Query := l_search_Query ||' AND ';
260 l_search_Query := l_search_Query ||' ( ';
261 l_search_Query := l_search_Query ||' to_date(:start_time2, ''DD-MON-RRRR HH24:MI:SS'') < est_arrival_time and ';
262 l_search_Query := l_search_Query ||' est_arrival_time <= to_date(:end_time2, ''DD-MON-RRRR HH24:MI:SS'') ';
263 l_search_Query := l_search_Query ||' ) ';
264 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.START_DATE, 'DD-MON-RRRR HH24:MI:SS');
265 l_bind_index := l_bind_index + 1;
266 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.END_DATE, 'DD-MON-RRRR HH24:MI:SS');
267 l_bind_index := l_bind_index + 1;
268 END IF;
269
270 ELSIF p_flight_search_rec.DATE_APPLY_TO_FLAG = G_APPLY_TO_DEPARTURE
271 THEN
272 IF g_use_actuals = FND_API.G_TRUE
273 THEN
274 l_search_Query := l_search_Query ||' AND ';
275 l_search_Query := l_search_Query ||' ( ';
276 l_search_Query := l_search_Query ||' to_date(:start_time, ''DD-MON-RRRR HH24:MI:SS'') <= nvl(actual_departure_time, est_departure_time) and ';
277 l_search_Query := l_search_Query ||' nvl(actual_departure_time, est_departure_time) < to_date(:end_time, ''DD-MON-RRRR HH24:MI:SS'') ';
278 l_search_Query := l_search_Query ||' ) ';
279 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.START_DATE, 'DD-MON-RRRR HH24:MI:SS');
280 l_bind_index := l_bind_index + 1;
281 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.END_DATE, 'DD-MON-RRRR HH24:MI:SS');
282 l_bind_index := l_bind_index + 1;
283 ELSE
284 l_search_Query := l_search_Query ||' AND ';
285 l_search_Query := l_search_Query ||' ( ';
286 l_search_Query := l_search_Query ||' to_date(:start_time1, ''DD-MON-RRRR HH24:MI:SS'') <= est_departure_time and ';
287 l_search_Query := l_search_Query ||' est_departure_time < to_date(:end_time1, ''DD-MON-RRRR HH24:MI:SS'') ';
288 l_search_Query := l_search_Query ||' ) ';
289 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.START_DATE, 'DD-MON-RRRR HH24:MI:SS');
290 l_bind_index := l_bind_index + 1;
291 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.END_DATE, 'DD-MON-RRRR HH24:MI:SS');
292 l_bind_index := l_bind_index + 1;
293 END IF;
294 END IF;
295
296 ELSIF p_flight_search_rec.START_DATE IS NOT NULL
297 THEN
298 IF g_use_actuals = FND_API.G_TRUE
299 THEN
300 l_search_Query := l_search_Query || ' AND NVL(ACTUAL_ARRIVAL_TIME,EST_ARRIVAL_TIME) >= to_date(:'||l_bind_index||', ''DD-MON-RRRR HH24:MI:SS'')';
301 ELSE
302 l_search_Query := l_search_Query || ' AND EST_ARRIVAL_TIME >= to_date(:'||l_bind_index||', ''DD-MON-RRRR HH24:MI:SS'')';
303 END IF;
304 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.START_DATE, 'DD-MON-RRRR HH24:MI:SS');
305 l_bind_index := l_bind_index + 1;
306
307 ELSIF p_flight_search_rec.END_DATE IS NOT NULL
308 THEN
309 IF g_use_actuals = FND_API.G_TRUE
310 THEN
311 l_search_Query := l_search_Query || ' AND NVL(ACTUAL_DEPARTURE_TIME,EST_DEPARTURE_TIME) <= to_date(:'||l_bind_index||', ''DD-MON-RRRR HH24:MI:SS'')';
312 ELSE
313 l_search_Query := l_search_Query || ' AND EST_DEPARTURE_TIME <= to_date(:'||l_bind_index||', ''DD-MON-RRRR HH24:MI:SS'')';
314 END IF;
315 l_bind_value_tbl(l_bind_index) := to_char(p_flight_search_rec.END_DATE, 'DD-MON-RRRR HH24:MI:SS');
316 l_bind_index := l_bind_index + 1;
317 END IF;
318
319 -- add order by. this doesnt have to be done when unit_schedule_id is passed.
320 l_search_Query := l_search_Query || ' ORDER BY UNIT_CONFIG_NAME DESC, EST_DEPARTURE_TIME DESC, EST_ARRIVAL_TIME DESC';
321 END IF;
322
323 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR(l_flight_schedule_csr, l_bind_value_tbl, l_search_Query);
324
325 i := 0;
326 l_super_user := AHL_UA_FLIGHT_SCHEDULES_PVT.is_super_user;
327
328 --OPEN l_flight_schedule_csr FOR l_search_Query;
329 LOOP
330 --Get search results
331 FETCH l_flight_schedule_csr INTO l_flight_schedule_rec.UNIT_SCHEDULE_ID,
332 l_flight_schedule_rec.FLIGHT_NUMBER,
333 l_flight_schedule_rec.SEGMENT,
334 l_flight_schedule_rec.DEPARTURE_DEPT_ID,
335 l_flight_schedule_rec.DEPARTURE_DEPT_CODE,
336 l_flight_schedule_rec.DEPARTURE_ORG_ID,
337 l_flight_schedule_rec.DEPARTURE_ORG_CODE,
338 l_flight_schedule_rec.ARRIVAL_DEPT_ID,
339 l_flight_schedule_rec.ARRIVAL_DEPT_CODE,
340 l_flight_schedule_rec.ARRIVAL_ORG_ID,
341 l_flight_schedule_rec.ARRIVAL_ORG_CODE,
342 l_flight_schedule_rec.EST_DEPARTURE_TIME,
343 l_flight_schedule_rec.EST_ARRIVAL_TIME,
344 l_flight_schedule_rec.ACTUAL_DEPARTURE_TIME,
345 l_flight_schedule_rec.ACTUAL_ARRIVAL_TIME,
346 l_flight_schedule_rec.PRECEDING_US_ID,
347 l_flight_schedule_rec.CSI_INSTANCE_ID,
348 l_flight_schedule_rec.UNIT_CONFIG_HEADER_ID,
349 l_flight_schedule_rec.UNIT_CONFIG_NAME,
350 l_flight_schedule_rec.VISIT_RESCHEDULE_MODE,
351 l_flight_schedule_rec.VISIT_RESCHEDULE_MEANING,
352 l_flight_schedule_rec.ITEM_NUMBER,
353 l_flight_schedule_rec.SERIAL_NUMBER,
354 l_flight_schedule_rec.OBJECT_VERSION_NUMBER,
355 l_flight_schedule_rec.ATTRIBUTE_CATEGORY,
356 l_flight_schedule_rec.ATTRIBUTE1,
357 l_flight_schedule_rec.ATTRIBUTE2,
358 l_flight_schedule_rec.ATTRIBUTE3,
359 l_flight_schedule_rec.ATTRIBUTE4,
360 l_flight_schedule_rec.ATTRIBUTE5,
361 l_flight_schedule_rec.ATTRIBUTE6,
362 l_flight_schedule_rec.ATTRIBUTE7,
363 l_flight_schedule_rec.ATTRIBUTE8,
364 l_flight_schedule_rec.ATTRIBUTE9,
365 l_flight_schedule_rec.ATTRIBUTE10,
366 l_flight_schedule_rec.ATTRIBUTE11,
367 l_flight_schedule_rec.ATTRIBUTE12,
368 l_flight_schedule_rec.ATTRIBUTE13,
369 l_flight_schedule_rec.ATTRIBUTE14,
370 l_flight_schedule_rec.ATTRIBUTE15;
371
372 EXIT WHEN l_flight_schedule_csr%NOTFOUND;
373 i := i + 1;
374 -- Copy values from local record to output table.
375 x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID := l_flight_schedule_rec.UNIT_SCHEDULE_ID;
376 x_flight_schedules_tbl(i).FLIGHT_NUMBER := l_flight_schedule_rec.FLIGHT_NUMBER;
377 x_flight_schedules_tbl(i).SEGMENT := l_flight_schedule_rec.SEGMENT;
378 x_flight_schedules_tbl(i).DEPARTURE_DEPT_ID := l_flight_schedule_rec.DEPARTURE_DEPT_ID;
379 x_flight_schedules_tbl(i).DEPARTURE_DEPT_CODE := l_flight_schedule_rec.DEPARTURE_DEPT_CODE;
380 x_flight_schedules_tbl(i).DEPARTURE_ORG_ID := l_flight_schedule_rec.DEPARTURE_ORG_ID;
381 x_flight_schedules_tbl(i).DEPARTURE_ORG_CODE := l_flight_schedule_rec.DEPARTURE_ORG_CODE;
382 x_flight_schedules_tbl(i).ARRIVAL_DEPT_ID := l_flight_schedule_rec.ARRIVAL_DEPT_ID;
383 x_flight_schedules_tbl(i).ARRIVAL_DEPT_CODE := l_flight_schedule_rec.ARRIVAL_DEPT_CODE;
384 x_flight_schedules_tbl(i).ARRIVAL_ORG_ID := l_flight_schedule_rec.ARRIVAL_ORG_ID;
385 x_flight_schedules_tbl(i).ARRIVAL_ORG_CODE := l_flight_schedule_rec.ARRIVAL_ORG_CODE;
386 x_flight_schedules_tbl(i).EST_DEPARTURE_TIME := l_flight_schedule_rec.EST_DEPARTURE_TIME;
387 x_flight_schedules_tbl(i).EST_ARRIVAL_TIME := l_flight_schedule_rec.EST_ARRIVAL_TIME;
388 x_flight_schedules_tbl(i).ACTUAL_DEPARTURE_TIME := l_flight_schedule_rec.ACTUAL_DEPARTURE_TIME;
389 x_flight_schedules_tbl(i).ACTUAL_ARRIVAL_TIME := l_flight_schedule_rec.ACTUAL_ARRIVAL_TIME;
390 x_flight_schedules_tbl(i).PRECEDING_US_ID := l_flight_schedule_rec.PRECEDING_US_ID;
391 x_flight_schedules_tbl(i).CSI_INSTANCE_ID := l_flight_schedule_rec.CSI_INSTANCE_ID;
392 x_flight_schedules_tbl(i).UNIT_CONFIG_HEADER_ID := l_flight_schedule_rec.UNIT_CONFIG_HEADER_ID;
393 x_flight_schedules_tbl(i).UNIT_CONFIG_NAME := l_flight_schedule_rec.UNIT_CONFIG_NAME;
394 x_flight_schedules_tbl(i).ITEM_NUMBER := l_flight_schedule_rec.ITEM_NUMBER;
395 x_flight_schedules_tbl(i).SERIAL_NUMBER := l_flight_schedule_rec.SERIAL_NUMBER;
396 x_flight_schedules_tbl(i).VISIT_RESCHEDULE_MODE := l_flight_schedule_rec.VISIT_RESCHEDULE_MODE;
397 x_flight_schedules_tbl(i).VISIT_RESCHEDULE_MEANING := l_flight_schedule_rec.VISIT_RESCHEDULE_MEANING;
398 x_flight_schedules_tbl(i).OBJECT_VERSION_NUMBER := l_flight_schedule_rec.OBJECT_VERSION_NUMBER;
399 x_flight_schedules_tbl(i).ATTRIBUTE_CATEGORY := l_flight_schedule_rec.ATTRIBUTE_CATEGORY;
400 x_flight_schedules_tbl(i).ATTRIBUTE1 := l_flight_schedule_rec.ATTRIBUTE1;
401 x_flight_schedules_tbl(i).ATTRIBUTE2 := l_flight_schedule_rec.ATTRIBUTE2;
402 x_flight_schedules_tbl(i).ATTRIBUTE3 := l_flight_schedule_rec.ATTRIBUTE3;
403 x_flight_schedules_tbl(i).ATTRIBUTE4 := l_flight_schedule_rec.ATTRIBUTE4;
404 x_flight_schedules_tbl(i).ATTRIBUTE5 := l_flight_schedule_rec.ATTRIBUTE5;
405 x_flight_schedules_tbl(i).ATTRIBUTE6 := l_flight_schedule_rec.ATTRIBUTE6;
406 x_flight_schedules_tbl(i).ATTRIBUTE7 := l_flight_schedule_rec.ATTRIBUTE7;
407 x_flight_schedules_tbl(i).ATTRIBUTE8 := l_flight_schedule_rec.ATTRIBUTE8;
408 x_flight_schedules_tbl(i).ATTRIBUTE9 := l_flight_schedule_rec.ATTRIBUTE9;
409 x_flight_schedules_tbl(i).ATTRIBUTE10 := l_flight_schedule_rec.ATTRIBUTE10;
410 x_flight_schedules_tbl(i).ATTRIBUTE11 := l_flight_schedule_rec.ATTRIBUTE11;
411 x_flight_schedules_tbl(i).ATTRIBUTE12 := l_flight_schedule_rec.ATTRIBUTE12;
412 x_flight_schedules_tbl(i).ATTRIBUTE13 := l_flight_schedule_rec.ATTRIBUTE13;
413 x_flight_schedules_tbl(i).ATTRIBUTE14 := l_flight_schedule_rec.ATTRIBUTE14;
414 x_flight_schedules_tbl(i).ATTRIBUTE15 := l_flight_schedule_rec.ATTRIBUTE15;
415
416 -- Find if record can be updated and deleted.
417 x_flight_schedules_tbl(i).is_delete_allowed := AHL_UA_FLIGHT_SCHEDULES_PVT.is_delete_allowed(
418 p_unit_schedule_id => x_flight_schedules_tbl(i).unit_schedule_id,
419 p_is_super_user => l_super_user
420 );
421 x_flight_schedules_tbl(i).is_update_allowed := AHL_UA_FLIGHT_SCHEDULES_PVT.is_update_allowed(
422 p_unit_schedule_id => x_flight_schedules_tbl(i).unit_schedule_id,
423 p_is_super_user => l_super_user
424 );
425 END LOOP;
426 CLOSE l_flight_schedule_csr;
427
428 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
429 fnd_log.string
430 (
431 fnd_log.level_procedure,
432 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
433 'At the end of PLSQL procedure'
434 );
435 END IF;
436
437 EXCEPTION
438 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
439 --ROLLBACK TO search_flight_schedules_pub;
440 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
441 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
442 p_count => x_msg_count,
443 p_data => x_msg_data);
444
445 WHEN OTHERS THEN
446 --ROLLBACK TO search_flight_schedules_pub;
447 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
448 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
449 THEN
450 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
451 p_procedure_name => l_api_name,
452 p_error_text => SUBSTR(SQLERRM,1,240));
453
454 END IF;
455 END Get_Flight_Schedule_Details;
456
457
458
459 --------------------------------------------------------------------------------
460 -- Public wrapper for AHL_UA_FLIGHT_SCHEDULES_PVT.process_flight_schedules
461 ------------------------------------------------------------------------------------------
462
463 PROCEDURE Process_Flight_Schedules
464 (
465 -- standard IN params
466 p_api_version IN NUMBER,
467 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
468 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
469 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
470 p_default IN VARCHAR2 :=FND_API.G_FALSE,
471 p_module_type IN VARCHAR2 :=NULL,
472 -- standard OUT params
473 x_return_status OUT NOCOPY VARCHAR2,
474 x_msg_count OUT NOCOPY NUMBER,
475 x_msg_data OUT NOCOPY VARCHAR2,
476 -- procedure params
477 p_x_flight_schedules_tbl IN OUT NOCOPY AHL_UA_FLIGHT_SCHEDULES_PVT.FLIGHT_SCHEDULES_TBL_TYPE
478 )
479 IS
480 l_api_name CONSTANT VARCHAR2(30) := 'Process_Flight_Schedules';
481 l_api_version CONSTANT NUMBER := 1.0;
482 l_msg_count NUMBER;
483 BEGIN
484 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
485 fnd_log.string
486 (
487 fnd_log.level_procedure,
488 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
489 'At the start of PLSQL procedure'
490 );
491 END IF;
492
493 --define a savepoint for the procedure
494 SAVEPOINT Process_Flight_Schedules_pub;
495
496 -- Initialize return status to success initially
497 x_return_status:=FND_API.G_RET_STS_SUCCESS;
498
499 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
500 p_api_version,
501 l_api_name,G_PKG_NAME)
502 THEN
503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
504 END IF;
505
506 -- Initialize message list if p_init_msg_list is set to TRUE.
507 IF FND_API.to_boolean(p_init_msg_list) THEN
508 FND_MSG_PUB.initialize;
509 END IF;
510
511
512 FOR i IN p_x_flight_schedules_tbl.FIRST..p_x_flight_schedules_tbl.LAST
513 LOOP
514 --Validate DML flag
515 IF (p_x_flight_schedules_tbl(i).DML_OPERATION <> 'D' AND p_x_flight_schedules_tbl(i).DML_OPERATION <> 'd' AND
516 p_x_flight_schedules_tbl(i).DML_OPERATION <> 'U' AND p_x_flight_schedules_tbl(i).DML_OPERATION <> 'u' AND
517 p_x_flight_schedules_tbl(i).DML_OPERATION <> 'C' AND p_x_flight_schedules_tbl(i).DML_OPERATION <> 'c')
518 THEN
519 FND_MESSAGE.set_name( 'AHL','AHL_COM_INVALID_DML' );
520 FND_MESSAGE.set_token( 'RECORD', p_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID);
521 FND_MESSAGE.set_token( 'FIELD', p_x_flight_schedules_tbl(i).DML_OPERATION);
522 FND_MSG_PUB.add;
523 END IF;
524
525
526 --Obj version number and Unit Schedule id check in case of update or delete.
527 IF ( p_x_flight_schedules_tbl(i).DML_OPERATION = 'D' OR p_x_flight_schedules_tbl(i).DML_OPERATION = 'd' OR
528 p_x_flight_schedules_tbl(i).DML_OPERATION = 'U' OR p_x_flight_schedules_tbl(i).DML_OPERATION = 'u' )
529 THEN
530 --Unit Schedule id cannot be null
531 IF (p_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID IS NULL OR
532 p_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID = FND_API.G_MISS_NUM)
533 THEN
534 FND_MESSAGE.set_name( 'AHL','AHL_UA_US_NOT_FOUND' );
535 FND_MESSAGE.set_token( 'RECORD', p_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID);
536 FND_MSG_PUB.add;
537 END IF;
538
539 -- Check for Object Version number.
540 IF (p_x_flight_schedules_tbl(i).OBJECT_VERSION_NUMBER IS NULL OR
541 p_x_flight_schedules_tbl(i).OBJECT_VERSION_NUMBER = FND_API.G_MISS_NUM)
542 THEN
543 FND_MESSAGE.set_name( 'AHL','AHL_UA_OBJ_VERNO_NULL' );
544 FND_MESSAGE.set_token( 'RECORD', p_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID);
545 FND_MSG_PUB.add;
546 END IF;
547 END IF;
548
549 IF p_x_flight_schedules_tbl(i).DML_OPERATION = 'C' OR
550 p_x_flight_schedules_tbl(i).DML_OPERATION = 'c' OR
551 p_x_flight_schedules_tbl(i).DML_OPERATION = 'U' OR
552 p_x_flight_schedules_tbl(i).DML_OPERATION = 'u'
553 THEN
554 -- Unit Config Header id is a mandatory input field and cannot be null.
555 IF (p_x_flight_schedules_tbl(i).UNIT_CONFIG_HEADER_ID IS NULL OR
556 p_x_flight_schedules_tbl(i).UNIT_CONFIG_HEADER_ID = FND_API.G_MISS_NUM )AND
557 (p_x_flight_schedules_tbl(i).UNIT_CONFIG_NAME IS NULL OR
558 p_x_flight_schedules_tbl(i).UNIT_CONFIG_NAME = FND_API.G_MISS_CHAR)
559
560 THEN
561 FND_MESSAGE.set_name( 'AHL','AHL_UA_INV_UC_NAME' );
562 FND_MESSAGE.set_token( 'RECORD', p_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID);
563 FND_MSG_PUB.add;
564 END IF;
565
566 --Flight Number is a mandatory input field and cannot be null.
567 IF p_x_flight_schedules_tbl(i).FLIGHT_NUMBER IS NULL OR
568 p_x_flight_schedules_tbl(i).FLIGHT_NUMBER = FND_API.G_MISS_CHAR
569 THEN
570 FND_MESSAGE.set_name( 'AHL','AHL_UA_FLG_NUMBER_NULL' );
571 FND_MESSAGE.set_token( 'RECORD', p_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID);
572 FND_MSG_PUB.add;
573 END IF;
574
575 --Segment is a mandatory input field and cannot be null
576 IF p_x_flight_schedules_tbl(i).SEGMENT IS NULL OR
577 p_x_flight_schedules_tbl(i).SEGMENT = FND_API.G_MISS_CHAR
578 THEN
579 FND_MESSAGE.set_name( 'AHL','AHL_UA_SEGMENT_NULL' );
580 FND_MESSAGE.set_token( 'RECORD', p_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID);
581 FND_MSG_PUB.add;
582 END IF;
583
584 --Departure_Dept_Id is a mandatory input field and cannot be null
585 IF ( p_x_flight_schedules_tbl(i).DEPARTURE_DEPT_ID IS NULL OR
586 p_x_flight_schedules_tbl(i).DEPARTURE_DEPT_ID = FND_API.G_MISS_NUM ) AND
587 ( p_x_flight_schedules_tbl(i).DEPARTURE_DEPT_CODE IS NULL OR
588 p_x_flight_schedules_tbl(i).DEPARTURE_DEPT_CODE = FND_API.G_MISS_CHAR )
589 THEN
590 FND_MESSAGE.set_name( 'AHL','AHL_UA_DEP_DEPT_NULL' );
591 FND_MESSAGE.set_token( 'RECORD', p_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID);
592 FND_MSG_PUB.add;
593 END IF;
594
595 --Departure_Org_Id is a mandatory input field and cannot be null
596 IF ( p_x_flight_schedules_tbl(i).DEPARTURE_ORG_ID IS NULL OR
597 p_x_flight_schedules_tbl(i).DEPARTURE_ORG_ID = FND_API.G_MISS_NUM ) AND
598 ( p_x_flight_schedules_tbl(i).DEPARTURE_ORG_CODE IS NULL OR
599 p_x_flight_schedules_tbl(i).DEPARTURE_ORG_CODE = FND_API.G_MISS_CHAR )
600 THEN
601 FND_MESSAGE.set_name( 'AHL','AHL_UA_DEP_ORG_NULL' );
602 FND_MESSAGE.set_token( 'RECORD', p_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID);
603 FND_MSG_PUB.add;
604 END IF;
605
606 --Arrival_Dept_Id is a mandatory input field and cannot be null
607 IF ( p_x_flight_schedules_tbl(i).ARRIVAL_DEPT_ID IS NULL OR
608 p_x_flight_schedules_tbl(i).ARRIVAL_DEPT_ID = FND_API.G_MISS_NUM ) AND
609 ( p_x_flight_schedules_tbl(i).ARRIVAL_DEPT_CODE IS NULL OR
610 p_x_flight_schedules_tbl(i).ARRIVAL_DEPT_CODE = FND_API.G_MISS_CHAR )
611
612 THEN
613 FND_MESSAGE.set_name( 'AHL','AHL_UA_ARR_DEPT_NULL' );
614 FND_MESSAGE.set_token( 'RECORD', p_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID);
615 FND_MSG_PUB.add;
616 END IF;
617
618 --Arrival_Org_Id is a mandatory input field and cannot be null
619 IF ( p_x_flight_schedules_tbl(i).ARRIVAL_ORG_ID IS NULL OR
620 p_x_flight_schedules_tbl(i).ARRIVAL_ORG_ID = FND_API.G_MISS_NUM ) AND
621 ( p_x_flight_schedules_tbl(i).ARRIVAL_ORG_CODE IS NULL OR
622 p_x_flight_schedules_tbl(i).ARRIVAL_ORG_CODE = FND_API.G_MISS_CHAR )
623 THEN
624 FND_MESSAGE.set_name( 'AHL','AHL_UA_ARR_ORG_NULL' );
625 FND_MESSAGE.set_token( 'RECORD', p_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID);
626 FND_MSG_PUB.add;
627 END IF;
628
629 --Est_Departure_Time is a mandatory input field and cannot be null
630 IF p_x_flight_schedules_tbl(i).EST_DEPARTURE_TIME IS NULL OR
631 p_x_flight_schedules_tbl(i).EST_DEPARTURE_TIME = FND_API.G_MISS_DATE
632 THEN
633 FND_MESSAGE.set_name( 'AHL','AHL_UA_EST_DEP_TIME_NULL' );
634 FND_MESSAGE.set_token( 'RECORD', p_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID);
635 FND_MSG_PUB.add;
636 END IF;
637
638 --Est_Arrival_Time is a mandatory input field and cannot be null
639 IF p_x_flight_schedules_tbl(i).EST_ARRIVAL_TIME IS NULL OR
640 p_x_flight_schedules_tbl(i).EST_ARRIVAL_TIME = FND_API.G_MISS_DATE
641 THEN
642 FND_MESSAGE.set_name( 'AHL','AHL_UA_EST_ARR_TIME_NULL' );
643 FND_MESSAGE.set_token( 'RECORD', p_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID);
644 FND_MSG_PUB.add;
645 END IF;
646
647 --Visit_Reschedule_Mode is a mandatory input field and cannot be null
648 IF ( p_x_flight_schedules_tbl(i).VISIT_RESCHEDULE_MODE IS NULL OR
649 p_x_flight_schedules_tbl(i).VISIT_RESCHEDULE_MODE = FND_API.G_MISS_CHAR ) AND
650 ( p_x_flight_schedules_tbl(i).VISIT_RESCHEDULE_MEANING IS NULL OR
651 p_x_flight_schedules_tbl(i).VISIT_RESCHEDULE_MEANING = FND_API.G_MISS_CHAR )
652 THEN
653 FND_MESSAGE.set_name( 'AHL','AHL_UA_VST_RES_MODE_NULL' );
654 FND_MESSAGE.set_token( 'RECORD', p_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID);
655 FND_MSG_PUB.add;
656 END IF;
657
658 END IF;
659
660 END LOOP;
661
662 l_msg_count := FND_MSG_PUB.COUNT_MSG;
663 IF (l_msg_count > 0)
664 THEN
665 RAISE FND_API.G_EXC_ERROR;
666 END IF;
667
668
669 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
670 fnd_log.string
671 (
672 fnd_log.level_statement,
673 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
674 'Before calling the private API.....'
675 );
676 END IF;
677
678 AHL_UA_FLIGHT_SCHEDULES_PVT.Process_Flight_Schedules(
679 p_api_version => p_api_version,
680 p_init_msg_list => p_init_msg_list,
681 p_commit => p_commit,
682 p_validation_level => p_validation_level,
683 p_default => p_default,
684 p_module_type => p_module_type,
685 x_return_status => x_return_status,
686 x_msg_count => x_msg_count,
687 x_msg_data => x_msg_data,
688 p_x_flight_schedules_tbl => p_x_flight_schedules_tbl
689 );
690
691 -- Check Error Message stack.
692 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
693 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
694 fnd_log.string
695 (
696 fnd_log.level_error,
697 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
698 'Private API raised expected error....'
699 );
700 END IF;
701 RAISE FND_API.G_EXC_ERROR;
702 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
703 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
704 fnd_log.string
705 (
706 fnd_log.level_error,
707 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
708 'Private API raised unexpected error....'
709 );
710 END IF;
711 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
712 END IF;
713
714 -- Standard check for p_commit
715 IF FND_API.To_Boolean (p_commit)
716 THEN
717 COMMIT;
718 END IF;
719
720 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
721 fnd_log.string
722 (
723 fnd_log.level_procedure,
724 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
725 'At the end of PLSQL procedure'
726 );
727 END IF;
728
729 EXCEPTION
730 WHEN FND_API.G_EXC_ERROR THEN
731 x_return_status := FND_API.G_RET_STS_ERROR;
732 Rollback to Process_Flight_Schedules_pub;
733 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
734 p_data => x_msg_data,
735 p_encoded => fnd_api.g_false );
736
737 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
738 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
739 Rollback to Process_Flight_Schedules_pub;
740 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
741 p_data => x_msg_data,
742 p_encoded => fnd_api.g_false );
743
744 WHEN OTHERS THEN
745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
746 Rollback to Process_Flight_Schedules_pub;
747 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
748 THEN
749 fnd_msg_pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
750 p_procedure_name => l_api_name,
751 p_error_text => SUBSTR(SQLERRM,1,240) );
752 END IF;
753 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
754 p_data => x_msg_data,
755 p_encoded => fnd_api.g_false );
756
757 END Process_Flight_Schedules;
758
759 -----------------------------------------------------------------------------------------------------
760 -- Function for constructing record identifier for error messages.
761 -----------------------------------------------------------------------------------------------------
762
763 FUNCTION get_record_identifier(
764 p_flght_visit_schedules_rec IN FLIGHT_VISIT_SCH_REC_TYPE
765 )
766 RETURN VARCHAR2
767 IS
768 l_record_identifier VARCHAR2(200);
769 BEGIN
770 l_record_identifier := '';
771
772 IF p_flght_visit_schedules_rec.UNIT_CONFIG_NAME IS NOT NULL
773 THEN
774 l_record_identifier := l_record_identifier||p_flght_visit_schedules_rec.UNIT_CONFIG_NAME;
775 END IF;
776
777 IF p_flght_visit_schedules_rec.FLIGHT_NUMBER IS NOT NULL
778 THEN
779 l_record_identifier := l_record_identifier||','||p_flght_visit_schedules_rec.FLIGHT_NUMBER;
780 END IF;
781
782 IF p_flght_visit_schedules_rec.SEGMENT IS NOT NULL
783 THEN
784 l_record_identifier := l_record_identifier||','||p_flght_visit_schedules_rec.SEGMENT;
785 END IF;
786
787 RETURN l_record_identifier;
788
789 END get_record_identifier;
790 ------------------------------------------------------------------------------------------
791 -- Internal procedure which does the work of populating flight schedule rec,
792 -- call pvt procedure and repopulate the ids back to flight_visit_rec.
793 ------------------------------------------------------------------------------------------
794
795
796 PROCEDURE handle_flight_schedules(
797 p_x_flght_visit_schedules_tbl IN OUT NOCOPY FLIGHT_VISIT_SCH_TBL_TYPE,
798 x_return_status OUT NOCOPY VARCHAR2,
799 x_msg_count OUT NOCOPY NUMBER,
800 x_msg_data OUT NOCOPY VARCHAR2
801 )
802 IS
803 i NUMBER;
804 l_x_flight_schedules_tbl AHL_UA_FLIGHT_SCHEDULES_PVT.FLIGHT_SCHEDULES_TBL_TYPE;
805 l_api_name CONSTANT VARCHAR2(30) := 'handle_flight_schedules';
806 BEGIN
807
808 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
809 fnd_log.string
810 (
811 fnd_log.level_procedure,
812 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
813 'At the start of procedure'
814 );
815 END IF;
816
817 FOR i IN p_x_flght_visit_schedules_tbl.FIRST..p_x_flght_visit_schedules_tbl.LAST
818 LOOP
819 l_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID := p_x_flght_visit_schedules_tbl(i).UNIT_SCHEDULE_ID;
820 l_x_flight_schedules_tbl(i).FLIGHT_NUMBER := p_x_flght_visit_schedules_tbl(i).FLIGHT_NUMBER ;
821 l_x_flight_schedules_tbl(i).SEGMENT := p_x_flght_visit_schedules_tbl(i).SEGMENT;
822 l_x_flight_schedules_tbl(i).EST_DEPARTURE_TIME := p_x_flght_visit_schedules_tbl(i).EST_DEPARTURE_TIME;
823 l_x_flight_schedules_tbl(i).ACTUAL_DEPARTURE_TIME := p_x_flght_visit_schedules_tbl(i).ACTUAL_DEPARTURE_TIME;
824 l_x_flight_schedules_tbl(i).DEPARTURE_DEPT_ID := p_x_flght_visit_schedules_tbl(i).DEPARTURE_DEPT_ID;
825 l_x_flight_schedules_tbl(i).DEPARTURE_DEPT_CODE := p_x_flght_visit_schedules_tbl(i).DEPARTURE_DEPT_CODE;
826 l_x_flight_schedules_tbl(i).DEPARTURE_ORG_ID := p_x_flght_visit_schedules_tbl(i).DEPARTURE_ORG_ID;
827 l_x_flight_schedules_tbl(i).DEPARTURE_ORG_CODE := p_x_flght_visit_schedules_tbl(i).DEPARTURE_ORG_CODE;
828 l_x_flight_schedules_tbl(i).EST_ARRIVAL_TIME := p_x_flght_visit_schedules_tbl(i).EST_ARRIVAL_TIME;
829 l_x_flight_schedules_tbl(i).ACTUAL_ARRIVAL_TIME := p_x_flght_visit_schedules_tbl(i).ACTUAL_ARRIVAL_TIME;
830 l_x_flight_schedules_tbl(i).ARRIVAL_DEPT_ID := p_x_flght_visit_schedules_tbl(i).ARRIVAL_DEPT_ID;
831 l_x_flight_schedules_tbl(i).ARRIVAL_DEPT_CODE := p_x_flght_visit_schedules_tbl(i).ARRIVAL_DEPT_CODE;
832 l_x_flight_schedules_tbl(i).ARRIVAL_ORG_ID := p_x_flght_visit_schedules_tbl(i).ARRIVAL_ORG_ID;
833 l_x_flight_schedules_tbl(i).ARRIVAL_ORG_CODE := p_x_flght_visit_schedules_tbl(i).ARRIVAL_ORG_CODE;
834 l_x_flight_schedules_tbl(i).PRECEDING_US_ID := p_x_flght_visit_schedules_tbl(i).PRECEDING_US_ID;
835 l_x_flight_schedules_tbl(i).UNIT_CONFIG_HEADER_ID := p_x_flght_visit_schedules_tbl(i).UNIT_CONFIG_HEADER_ID;
836 l_x_flight_schedules_tbl(i).UNIT_CONFIG_NAME := p_x_flght_visit_schedules_tbl(i).UNIT_CONFIG_NAME;
837 l_x_flight_schedules_tbl(i).CSI_INSTANCE_ID := p_x_flght_visit_schedules_tbl(i).CSI_INSTANCE_ID;
838 l_x_flight_schedules_tbl(i).INSTANCE_NUMBER := p_x_flght_visit_schedules_tbl(i).INSTANCE_NUMBER;
839 l_x_flight_schedules_tbl(i).ITEM_NUMBER := p_x_flght_visit_schedules_tbl(i).ITEM_NUMBER;
840 l_x_flight_schedules_tbl(i).SERIAL_NUMBER := p_x_flght_visit_schedules_tbl(i).SERIAL_NUMBER;
841 l_x_flight_schedules_tbl(i).VISIT_RESCHEDULE_MODE := p_x_flght_visit_schedules_tbl(i).VISIT_RESCHEDULE_MODE;
842 l_x_flight_schedules_tbl(i).VISIT_RESCHEDULE_MEANING := p_x_flght_visit_schedules_tbl(i).VISIT_RESCHEDULE_MEANING;
843 l_x_flight_schedules_tbl(i).OBJECT_VERSION_NUMBER := p_x_flght_visit_schedules_tbl(i).OBJECT_VERSION_NUMBER;
844 --l_x_flight_schedules_tbl(i).IS_UPDATE_ALLOWED := p_x_flght_visit_schedules_tbl(i).IS_UPDATE_ALLOWED;
845 --l_x_flight_schedules_tbl(i).IS_DELETE_ALLOWED := p_x_flght_visit_schedules_tbl(i).IS_DELETE_ALLOWED;
846 l_x_flight_schedules_tbl(i).ATTRIBUTE_CATEGORY := p_x_flght_visit_schedules_tbl(i).ATTRIBUTE_CATEGORY;
847 l_x_flight_schedules_tbl(i).ATTRIBUTE1 := p_x_flght_visit_schedules_tbl(i).ATTRIBUTE1;
848 l_x_flight_schedules_tbl(i).ATTRIBUTE2 := p_x_flght_visit_schedules_tbl(i).ATTRIBUTE2;
849 l_x_flight_schedules_tbl(i).ATTRIBUTE3 := p_x_flght_visit_schedules_tbl(i).ATTRIBUTE3;
850 l_x_flight_schedules_tbl(i).ATTRIBUTE4 := p_x_flght_visit_schedules_tbl(i).ATTRIBUTE4;
851 l_x_flight_schedules_tbl(i).ATTRIBUTE5 := p_x_flght_visit_schedules_tbl(i).ATTRIBUTE5;
852 l_x_flight_schedules_tbl(i).ATTRIBUTE6 := p_x_flght_visit_schedules_tbl(i).ATTRIBUTE6;
853 l_x_flight_schedules_tbl(i).ATTRIBUTE7 := p_x_flght_visit_schedules_tbl(i).ATTRIBUTE7;
854 l_x_flight_schedules_tbl(i).ATTRIBUTE8 := p_x_flght_visit_schedules_tbl(i).ATTRIBUTE8;
855 l_x_flight_schedules_tbl(i).ATTRIBUTE9 := p_x_flght_visit_schedules_tbl(i).ATTRIBUTE9;
856 l_x_flight_schedules_tbl(i).ATTRIBUTE10 := p_x_flght_visit_schedules_tbl(i).ATTRIBUTE10;
857 l_x_flight_schedules_tbl(i).ATTRIBUTE11 := p_x_flght_visit_schedules_tbl(i).ATTRIBUTE11;
858 l_x_flight_schedules_tbl(i).ATTRIBUTE12 := p_x_flght_visit_schedules_tbl(i).ATTRIBUTE12;
859 l_x_flight_schedules_tbl(i).ATTRIBUTE13 := p_x_flght_visit_schedules_tbl(i).ATTRIBUTE13;
860 l_x_flight_schedules_tbl(i).ATTRIBUTE14 := p_x_flght_visit_schedules_tbl(i).ATTRIBUTE14;
861 l_x_flight_schedules_tbl(i).ATTRIBUTE15 := p_x_flght_visit_schedules_tbl(i).ATTRIBUTE15;
862 l_x_flight_schedules_tbl(i).DML_OPERATION := p_x_flght_visit_schedules_tbl(i).DML_OPERATION;
863 END LOOP;
864
865 -- Call the private procedure for processing visits
866 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
867 fnd_log.string
868 (
869 fnd_log.level_statement,
870 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
871 'Calling AHL_UA_FLIGHT_SCHEDULES_PVT.Process_Flight_Schedules..'
872 );
873 END IF;
874
875 AHL_UA_FLIGHT_SCHEDULES_PVT.Process_Flight_Schedules(
876 p_api_version => 1.0,
877 p_init_msg_list => FND_API.G_FALSE,
878 p_commit => FND_API.G_FALSE,
879 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
880 p_default => FND_API.G_FALSE,
881 p_module_type => null,
882 x_return_status => x_return_status,
883 x_msg_count => x_msg_count,
884 x_msg_data => x_msg_data,
885 p_x_flight_schedules_tbl => l_x_flight_schedules_tbl
886 );
887
888 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
889 fnd_log.string
890 (
891 fnd_log.level_statement,
892 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
893 'After calling AHL_UA_FLIGHT_SCHEDULES_PVT.Process_Flight_Schedules....'
894 );
895 END IF;
896
897 -- Check Error Message stack.
898 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
899 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
900 fnd_log.string
901 (
902 fnd_log.level_error,
903 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
904 'AHL_UA_FLIGHT_SCHEDULES_PVT.Process_Flight_Schedules API raised expected error....'
905 );
906 END IF;
907 RAISE FND_API.G_EXC_ERROR;
908 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
909 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
910 fnd_log.string
911 (
912 fnd_log.level_error,
913 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
914 'AHL_UA_FLIGHT_SCHEDULES_PVT.Process_Flight_Schedules API raised unexpected error....'
915 );
916 END IF;
917 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
918 END IF;
919
920
921 --Populate the ids back to flight_visit_rec
922 FOR i IN p_x_flght_visit_schedules_tbl.FIRST..p_x_flght_visit_schedules_tbl.LAST
923 LOOP
924 p_x_flght_visit_schedules_tbl(i).UNIT_SCHEDULE_ID := l_x_flight_schedules_tbl(i).UNIT_SCHEDULE_ID;
925 p_x_flght_visit_schedules_tbl(i).FLIGHT_NUMBER := l_x_flight_schedules_tbl(i).FLIGHT_NUMBER;
926 p_x_flght_visit_schedules_tbl(i).SEGMENT := l_x_flight_schedules_tbl(i).SEGMENT;
927 p_x_flght_visit_schedules_tbl(i).EST_DEPARTURE_TIME := l_x_flight_schedules_tbl(i).EST_DEPARTURE_TIME;
928 p_x_flght_visit_schedules_tbl(i).ACTUAL_DEPARTURE_TIME := l_x_flight_schedules_tbl(i).ACTUAL_DEPARTURE_TIME;
929 p_x_flght_visit_schedules_tbl(i).DEPARTURE_DEPT_ID := l_x_flight_schedules_tbl(i).DEPARTURE_DEPT_ID;
930 p_x_flght_visit_schedules_tbl(i).DEPARTURE_DEPT_CODE := l_x_flight_schedules_tbl(i).DEPARTURE_DEPT_CODE;
931 p_x_flght_visit_schedules_tbl(i).DEPARTURE_ORG_ID := l_x_flight_schedules_tbl(i).DEPARTURE_ORG_ID;
932 p_x_flght_visit_schedules_tbl(i).DEPARTURE_ORG_CODE := l_x_flight_schedules_tbl(i).DEPARTURE_ORG_CODE;
933 p_x_flght_visit_schedules_tbl(i).EST_ARRIVAL_TIME := l_x_flight_schedules_tbl(i).EST_ARRIVAL_TIME;
934 p_x_flght_visit_schedules_tbl(i).ACTUAL_ARRIVAL_TIME := l_x_flight_schedules_tbl(i).ACTUAL_ARRIVAL_TIME;
935 p_x_flght_visit_schedules_tbl(i).ARRIVAL_DEPT_ID := l_x_flight_schedules_tbl(i).ARRIVAL_DEPT_ID;
936 p_x_flght_visit_schedules_tbl(i).ARRIVAL_DEPT_CODE := l_x_flight_schedules_tbl(i).ARRIVAL_DEPT_CODE;
937 p_x_flght_visit_schedules_tbl(i).ARRIVAL_ORG_ID := l_x_flight_schedules_tbl(i).ARRIVAL_ORG_ID;
938 p_x_flght_visit_schedules_tbl(i).ARRIVAL_ORG_CODE := l_x_flight_schedules_tbl(i).ARRIVAL_ORG_CODE;
939 p_x_flght_visit_schedules_tbl(i).PRECEDING_US_ID := l_x_flight_schedules_tbl(i).PRECEDING_US_ID;
940 p_x_flght_visit_schedules_tbl(i).UNIT_CONFIG_HEADER_ID := l_x_flight_schedules_tbl(i).UNIT_CONFIG_HEADER_ID;
941 p_x_flght_visit_schedules_tbl(i).UNIT_CONFIG_NAME := l_x_flight_schedules_tbl(i).UNIT_CONFIG_NAME;
942 p_x_flght_visit_schedules_tbl(i).CSI_INSTANCE_ID := l_x_flight_schedules_tbl(i).CSI_INSTANCE_ID;
943 p_x_flght_visit_schedules_tbl(i).INSTANCE_NUMBER := l_x_flight_schedules_tbl(i).INSTANCE_NUMBER;
944 p_x_flght_visit_schedules_tbl(i).ITEM_NUMBER := l_x_flight_schedules_tbl(i).ITEM_NUMBER;
945 p_x_flght_visit_schedules_tbl(i).SERIAL_NUMBER := l_x_flight_schedules_tbl(i).SERIAL_NUMBER;
946 p_x_flght_visit_schedules_tbl(i).VISIT_RESCHEDULE_MODE := l_x_flight_schedules_tbl(i).VISIT_RESCHEDULE_MODE;
947 p_x_flght_visit_schedules_tbl(i).VISIT_RESCHEDULE_MEANING := l_x_flight_schedules_tbl(i).VISIT_RESCHEDULE_MEANING;
948 p_x_flght_visit_schedules_tbl(i).OBJECT_VERSION_NUMBER := l_x_flight_schedules_tbl(i).OBJECT_VERSION_NUMBER;
949 --p_x_flght_visit_schedules_tbl(i).IS_UPDATE_ALLOWED := p_x_flght_visit_schedules_tbl(i).IS_UPDATE_ALLOWED;
950 --p_x_flght_visit_schedules_tbl(i).IS_DELETE_ALLOWED := p_x_flght_visit_schedules_tbl(i).IS_DELETE_ALLOWED;
951 p_x_flght_visit_schedules_tbl(i).ATTRIBUTE_CATEGORY := l_x_flight_schedules_tbl(i).ATTRIBUTE_CATEGORY;
952 p_x_flght_visit_schedules_tbl(i).ATTRIBUTE1 := l_x_flight_schedules_tbl(i).ATTRIBUTE1;
953 p_x_flght_visit_schedules_tbl(i).ATTRIBUTE2 := l_x_flight_schedules_tbl(i).ATTRIBUTE2;
954 p_x_flght_visit_schedules_tbl(i).ATTRIBUTE3 := l_x_flight_schedules_tbl(i).ATTRIBUTE3;
955 p_x_flght_visit_schedules_tbl(i).ATTRIBUTE4 := l_x_flight_schedules_tbl(i).ATTRIBUTE4;
956 p_x_flght_visit_schedules_tbl(i).ATTRIBUTE5 := l_x_flight_schedules_tbl(i).ATTRIBUTE5;
957 p_x_flght_visit_schedules_tbl(i).ATTRIBUTE6 := l_x_flight_schedules_tbl(i).ATTRIBUTE6;
958 p_x_flght_visit_schedules_tbl(i).ATTRIBUTE7 := l_x_flight_schedules_tbl(i).ATTRIBUTE7;
959 p_x_flght_visit_schedules_tbl(i).ATTRIBUTE8 := l_x_flight_schedules_tbl(i).ATTRIBUTE8;
960 p_x_flght_visit_schedules_tbl(i).ATTRIBUTE9 := l_x_flight_schedules_tbl(i).ATTRIBUTE9;
961 p_x_flght_visit_schedules_tbl(i).ATTRIBUTE10 := l_x_flight_schedules_tbl(i).ATTRIBUTE10;
962 p_x_flght_visit_schedules_tbl(i).ATTRIBUTE11 := l_x_flight_schedules_tbl(i).ATTRIBUTE11;
963 p_x_flght_visit_schedules_tbl(i).ATTRIBUTE12 := l_x_flight_schedules_tbl(i).ATTRIBUTE12;
964 p_x_flght_visit_schedules_tbl(i).ATTRIBUTE13 := l_x_flight_schedules_tbl(i).ATTRIBUTE13;
965 p_x_flght_visit_schedules_tbl(i).ATTRIBUTE14 := l_x_flight_schedules_tbl(i).ATTRIBUTE14;
966 p_x_flght_visit_schedules_tbl(i).ATTRIBUTE15 := l_x_flight_schedules_tbl(i).ATTRIBUTE15;
967 END LOOP;
968 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
969 fnd_log.string
970 (
971 fnd_log.level_procedure,
972 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
973 'At the end of procedure'
974 );
975 END IF;
976
977
978 END handle_flight_schedules;
979
980
981
982 ------------------------------------------------------------------------------------------
983 -- Internal procedure for Auto Creating Visits.
984 ------------------------------------------------------------------------------------------
985
986 PROCEDURE auto_create_transit_visit(
987 p_x_flight_visit_schedules_tbl IN OUT NOCOPY FLIGHT_VISIT_SCH_TBL_TYPE,
988 x_return_status OUT NOCOPY VARCHAR2,
989 x_msg_count OUT NOCOPY NUMBER,
990 x_msg_data OUT NOCOPY VARCHAR2
991 )
992 IS
993 l_x_visit_rec AHL_VWP_VISITS_PVT.Visit_Rec_Type;
994 l_x_visit_tbl AHL_VWP_VISITS_PVT.Visit_Tbl_Type;
995 l_is_conflict VARCHAR2(1);
996 l_x_succ_flight_schedule AHL_UA_FLIGHT_SCHEDULES_PVT.Flight_Schedule_Rec_Type;
997 i NUMBER;
998 j NUMBER;
999 l_api_name CONSTANT VARCHAR2(30) := 'auto_create_transit_visit';
1000 l_api_version CONSTANT NUMBER := 1.0;
1001 BEGIN
1002
1003 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1004 fnd_log.string
1005 (
1006 fnd_log.level_procedure,
1007 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
1008 'At the start of procedure'
1009 );
1010 END IF;
1011
1012 -- populate visit table with required parameters.
1013 j := 0;
1014
1015 FOR i IN p_x_flight_visit_schedules_tbl.FIRST..p_x_flight_visit_schedules_tbl.LAST
1016 LOOP
1017 IF p_x_flight_visit_schedules_tbl(i).DML_OPERATION = 'C' OR
1018 p_x_flight_visit_schedules_tbl(i).DML_OPERATION = 'c'
1019 THEN
1020
1021 -- check for visit_create_type visit_type_code not null (also the meaning)...
1022 j := j + 1;
1023
1024 l_x_visit_tbl(j).VISIT_NAME := p_x_flight_visit_schedules_tbl(i).FLIGHT_NUMBER;
1025 l_x_visit_tbl(j).ORGANIZATION_ID := p_x_flight_visit_schedules_tbl(i).ARRIVAL_ORG_ID;
1026 l_x_visit_tbl(j).DEPARTMENT_ID := p_x_flight_visit_schedules_tbl(i).ARRIVAL_DEPT_ID;
1027 l_x_visit_tbl(j).START_DATE := p_x_flight_visit_schedules_tbl(i).EST_ARRIVAL_TIME;
1028 --Splitting date into hour and minutes as VWP needs them separately.
1029 l_x_visit_tbl(j).START_HOUR := TO_NUMBER(TO_CHAR(p_x_flight_visit_schedules_tbl(i).EST_ARRIVAL_TIME,'HH24'));
1030 l_x_visit_tbl(j).START_MIN := TO_NUMBER(TO_CHAR(p_x_flight_visit_schedules_tbl(i).EST_ARRIVAL_TIME,'MI'));
1031 l_x_visit_tbl(j).VISIT_TYPE_CODE := p_x_flight_visit_schedules_tbl(i).VISIT_TYPE_CODE;
1032 l_x_visit_tbl(j).VISIT_TYPE_NAME := p_x_flight_visit_schedules_tbl(i).VISIT_TYPE_MEANING;
1033 l_x_visit_tbl(j).VISIT_CREATE_TYPE := p_x_flight_visit_schedules_tbl(i).VISIT_CREATE_TYPE;
1034 --l_x_visit_tbl(j).VISIT_CREATE_MEANING := p_x_flight_visit_schedules_tbl(i).VISIT_CREATE_MEANING;
1035 l_x_visit_tbl(j).UNIT_NAME := p_x_flight_visit_schedules_tbl(i).UNIT_CONFIG_NAME;
1036 --l_x_visit_tbl(j).UNIT_CONFIG_HEADER_ID := p_x_flight_visit_schedules_tbl(i).UNIT_CONFIG_HEADER_ID;
1037 l_x_visit_tbl(j).ITEM_INSTANCE_ID := p_x_flight_visit_schedules_tbl(i).CSI_INSTANCE_ID;
1038 l_x_visit_tbl(j).ITEM_NAME := p_x_flight_visit_schedules_tbl(i).ITEM_NUMBER;
1039 l_x_visit_tbl(j).SERIAL_NUMBER := p_x_flight_visit_schedules_tbl(i).SERIAL_NUMBER;
1040 l_x_visit_tbl(j).UNIT_SCHEDULE_ID := p_x_flight_visit_schedules_tbl(i).UNIT_SCHEDULE_ID;
1041 l_x_visit_tbl(j).TEMPLATE_FLAG := 'N';
1042 l_x_visit_tbl(j).OPERATION_FLAG := 'I';
1043
1044 -- Determine if the succeding event is visit and its details.
1045 AHL_UA_COMMON_PVT.Get_Succ_Visit_Info
1046 (
1047 p_api_version => l_api_version,
1048 x_return_status => x_return_status,
1049 x_msg_count => x_msg_count,
1050 x_msg_data => x_msg_data,
1051 p_unit_config_id => p_x_flight_visit_schedules_tbl(i).UNIT_CONFIG_HEADER_ID,
1052 p_end_date_time => p_x_flight_visit_schedules_tbl(i).EST_ARRIVAL_TIME,
1053 x_succ_visit => l_x_visit_rec,
1054 x_is_conflict => l_is_conflict
1055 );
1056
1057 -- Check Error Message stack.
1058 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1059 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1060 fnd_log.string
1061 (
1062 fnd_log.level_error,
1063 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1064 'AHL_UA_FLIGHT_SCHEDULES_PVT.Process_Flight_Schedules API raised expected error....'
1065 );
1066 END IF;
1067 RAISE FND_API.G_EXC_ERROR;
1068 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1069 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1070 fnd_log.string
1071 (
1072 fnd_log.level_error,
1073 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1074 'AHL_UA_FLIGHT_SCHEDULES_PVT.Process_Flight_Schedules API raised unexpected error....'
1075 );
1076 END IF;
1077 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1078 END IF;
1079
1080 -- Determine if the succeding event is Flight and its details.
1081 AHL_UA_COMMON_PVT.Get_Succ_Flight_Info
1082 (
1083 p_api_version => l_api_version,
1084 x_return_status => x_return_status,
1085 x_msg_count => x_msg_count,
1086 x_msg_data => x_msg_data,
1087 p_unit_config_id => p_x_flight_visit_schedules_tbl(i).UNIT_CONFIG_HEADER_ID,
1088 p_end_date_time => p_x_flight_visit_schedules_tbl(i).EST_ARRIVAL_TIME,
1089 p_use_actuals => FND_API.G_FALSE,
1090 x_succ_flight_schedule => l_x_succ_flight_schedule,
1091 x_is_conflict => l_is_conflict
1092 );
1093
1094 -- Check Error Message stack.
1095 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1096 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1097 fnd_log.string
1098 (
1099 fnd_log.level_error,
1100 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1101 'AHL_UA_FLIGHT_SCHEDULES_PVT.Process_Flight_Schedules API raised expected error....'
1102 );
1103 END IF;
1104 RAISE FND_API.G_EXC_ERROR;
1105 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1106 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1107 fnd_log.string
1108 (
1109 fnd_log.level_error,
1110 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1111 'AHL_UA_FLIGHT_SCHEDULES_PVT.Process_Flight_Schedules API raised unexpected error....'
1112 );
1113 END IF;
1114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1115 END IF;
1116
1117 IF l_x_visit_rec.visit_id IS NOT NULL THEN
1118 l_x_visit_tbl(j).PLAN_END_DATE := l_x_visit_rec.START_DATE;
1119 ELSIF l_x_succ_flight_schedule.UNIT_SCHEDULE_ID IS NOT NULL THEN
1120 l_x_visit_tbl(j).PLAN_END_DATE := l_x_succ_flight_schedule.EST_DEPARTURE_TIME;
1121 END IF;
1122
1123 IF l_x_visit_tbl(j).PLAN_END_DATE IS NOT NULL THEN
1124 l_x_visit_tbl(j).PLAN_END_HOUR := TO_NUMBER(TO_CHAR(l_x_visit_tbl(j).PLAN_END_DATE, 'HH24'));
1125 l_x_visit_tbl(j).PLAN_END_MIN := TO_NUMBER(TO_CHAR(l_x_visit_tbl(j).PLAN_END_DATE, 'MI'));
1126 END IF;
1127
1128 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1129 fnd_log.string
1130 (
1131 fnd_log.level_statement,
1132 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1133 'l_x_visit_tbl(j).VISIT_NAME->'||l_x_visit_tbl(j).VISIT_NAME
1134 );
1135 fnd_log.string
1136 (
1137 fnd_log.level_statement,
1138 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1139 'l_x_visit_tbl(j).ORGANIZATION_ID->'||l_x_visit_tbl(j).ORGANIZATION_ID
1140 );
1141 fnd_log.string
1142 (
1143 fnd_log.level_statement,
1144 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1145 'l_x_visit_tbl(j).DEPARTMENT_ID->'||l_x_visit_tbl(j).DEPARTMENT_ID
1146 );
1147 fnd_log.string
1148 (
1149 fnd_log.level_statement,
1150 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1151 'l_x_visit_tbl(j).START_DATE->'||l_x_visit_tbl(j).START_DATE
1152 );
1153 fnd_log.string
1154 (
1155 fnd_log.level_statement,
1156 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1157 'l_x_visit_tbl(j).VISIT_TYPE_CODE->'||l_x_visit_tbl(j).VISIT_TYPE_CODE
1158 );
1159 fnd_log.string
1160 (
1161 fnd_log.level_statement,
1162 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1163 'l_x_visit_tbl(j).VISIT_TYPE_NAME->'||l_x_visit_tbl(j).VISIT_TYPE_NAME
1164 );
1165 fnd_log.string
1166 (
1167 fnd_log.level_statement,
1168 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1169 'l_x_visit_tbl(j).VISIT_CREATE_TYPE->'||l_x_visit_tbl(j).VISIT_CREATE_TYPE
1170 );
1171 fnd_log.string
1172 (
1173 fnd_log.level_statement,
1174 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1175 'l_x_visit_tbl(j).UNIT_NAME->'||l_x_visit_tbl(j).UNIT_NAME
1176 );
1177 fnd_log.string
1178 (
1179 fnd_log.level_statement,
1180 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1181 'l_x_visit_tbl(j).ITEM_INSTANCE_ID->'||l_x_visit_tbl(j).ITEM_INSTANCE_ID
1182 );
1183 fnd_log.string
1184 (
1185 fnd_log.level_statement,
1186 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1187 'l_x_visit_tbl(j).ITEM_NAME->'||l_x_visit_tbl(j).ITEM_NAME
1188 );
1189 fnd_log.string
1190 (
1191 fnd_log.level_statement,
1192 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1193 'l_x_visit_tbl(j).SERIAL_NUMBER->'||l_x_visit_tbl(j).SERIAL_NUMBER
1194 );
1195 fnd_log.string
1196 (
1197 fnd_log.level_statement,
1198 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1199 'l_x_visit_tbl(j).UNIT_SCHEDULE_ID->'||l_x_visit_tbl(j).UNIT_SCHEDULE_ID
1200 );
1201
1202 fnd_log.string
1203 (
1204 fnd_log.level_statement,
1205 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1206 'l_x_visit_tbl(j).OPERATION_FLAG->'||l_x_visit_tbl(j).OPERATION_FLAG
1207 );
1208 END IF;
1209 END IF;
1210 END LOOP;
1211
1212 IF l_x_visit_tbl.COUNT > 0
1213 THEN
1214
1215 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1216 fnd_log.string
1217 (
1218 fnd_log.level_statement,
1219 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1220 'Calling AHL_VWP_VISITS_PVT.Process_Visit'
1221 );
1222 END IF;
1223
1224 AHL_VWP_VISITS_PVT.Process_Visit (
1225 p_api_version => 1.0,
1226 p_init_msg_list => FND_API.G_FALSE,
1227 p_commit => FND_API.G_FALSE,
1228 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1229 p_module_type => NULL,
1230 p_x_Visit_tbl => l_x_visit_tbl,
1231 x_return_status => x_return_status,
1232 x_msg_count => x_msg_count,
1233 x_msg_data => x_msg_data
1234 );
1235
1236 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1237 fnd_log.string
1238 (
1239 fnd_log.level_statement,
1240 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1241 'After calling AHL_VWP_VISITS_PVT.Process_Visit'
1242 );
1243 END IF;
1244
1245 -- Check Error Message stack.
1246 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1247 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1248 fnd_log.string
1249 (
1250 fnd_log.level_error,
1251 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1252 'AHL_VWP_VISITS_PVT.Process_Visit API raised expected error....'
1253 );
1254 END IF;
1255 RAISE FND_API.G_EXC_ERROR;
1256 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1257 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1258 fnd_log.string
1259 (
1260 fnd_log.level_error,
1261 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1262 'AHL_VWP_VISITS_PVT.Process_Visit API raised unexpected error....'
1263 );
1264 END IF;
1265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1266 END IF;
1267
1268 -- Re-assign visit id.
1269 j := l_x_visit_tbl.FIRST;
1270 FOR i IN p_x_flight_visit_schedules_tbl.FIRST..p_x_flight_visit_schedules_tbl.LAST
1271 LOOP
1272 IF p_x_flight_visit_schedules_tbl(i).DML_OPERATION = 'I' OR
1273 p_x_flight_visit_schedules_tbl(i).DML_OPERATION = 'i'
1274 THEN
1275 p_x_flight_visit_schedules_tbl(i).visit_id := l_x_visit_tbl(j).visit_id;
1276 j := j + 1;
1277 IF j >= l_x_visit_tbl.COUNT
1278 THEN
1279 EXIT;
1280 END IF;
1281 END IF;
1282 END LOOP;
1283
1284 END IF;
1285
1286 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1287 fnd_log.string
1288 (
1289 fnd_log.level_procedure,
1290 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
1291 'At the end of procedure'
1292 );
1293 END IF;
1294 END auto_create_transit_visit;
1295
1296
1297 PROCEDURE validate_api_inputs(
1298 p_flight_visit_sch_tbl IN FLIGHT_VISIT_SCH_TBL_TYPE,
1299 x_return_status OUT NOCOPY VARCHAR2
1300 )
1301 IS
1302 l_api_name CONSTANT VARCHAR2(30) := 'validate_api_inputs';
1303
1304 BEGIN
1305 -- Initialize return status to success initially
1306 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1307
1308 FOR i IN p_flight_visit_sch_tbl.FIRST..p_flight_visit_sch_tbl.LAST
1309 LOOP
1310
1311 --Validate DML flag
1312 IF (
1313 p_flight_visit_sch_tbl(i).DML_OPERATION IS NULL
1314 OR
1315 (
1316 p_flight_visit_sch_tbl(i).DML_OPERATION <> 'D' AND p_flight_visit_sch_tbl(i).DML_OPERATION <> 'd' AND
1317 p_flight_visit_sch_tbl(i).DML_OPERATION <> 'U' AND p_flight_visit_sch_tbl(i).DML_OPERATION <> 'u' AND
1318 p_flight_visit_sch_tbl(i).DML_OPERATION <> 'C' AND p_flight_visit_sch_tbl(i).DML_OPERATION <> 'c'
1319 )
1320 )
1321 THEN
1322 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1323 fnd_log.string
1324 (
1325 fnd_log.level_error,
1326 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1327 'Invalid DML Operation flag for '
1328 ||p_flight_visit_sch_tbl(i).unit_schedule_id
1329 );
1330 END IF;
1331 FND_MESSAGE.set_name( 'AHL','AHL_COM_INVALID_DML' );
1332 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(p_flight_visit_sch_tbl(i)));
1333 FND_MESSAGE.set_token( 'FIELD', p_flight_visit_sch_tbl(i).DML_OPERATION);
1334 FND_MSG_PUB.add;
1335 x_return_status := FND_API.G_RET_STS_ERROR;
1336 END IF;
1337
1338
1339 --Obj version number and Unit Schedule id check in case of update or delete.
1340 IF ( p_flight_visit_sch_tbl(i).DML_OPERATION = 'D' OR p_flight_visit_sch_tbl(i).DML_OPERATION = 'd' OR
1341 p_flight_visit_sch_tbl(i).DML_OPERATION = 'U' OR p_flight_visit_sch_tbl(i).DML_OPERATION = 'u' )
1342 THEN
1343 --Unit Schedule id cannot be null
1344 IF (p_flight_visit_sch_tbl(i).UNIT_SCHEDULE_ID IS NULL OR
1345 p_flight_visit_sch_tbl(i).UNIT_SCHEDULE_ID = FND_API.G_MISS_NUM)
1346 THEN
1347 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1348 fnd_log.string
1349 (
1350 fnd_log.level_error,
1351 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1352 'Flight Schedule doesnt id cannot be null '
1353 );
1354 END IF;
1355 FND_MESSAGE.set_name( 'AHL','AHL_UA_US_NOT_FOUND' );
1356 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(p_flight_visit_sch_tbl(i)));
1357 FND_MSG_PUB.add;
1358 x_return_status := FND_API.G_RET_STS_ERROR;
1359 END IF;
1360
1361 -- Check for Object Version number.
1362 IF (p_flight_visit_sch_tbl(i).OBJECT_VERSION_NUMBER IS NULL OR
1363 p_flight_visit_sch_tbl(i).OBJECT_VERSION_NUMBER = FND_API.G_MISS_NUM)
1364 THEN
1365 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1366 fnd_log.string
1367 (
1368 fnd_log.level_error,
1369 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1370 'Object version number cannot be null for '
1371 ||p_flight_visit_sch_tbl(i).unit_schedule_id
1372 );
1373 END IF;
1374 FND_MESSAGE.set_name( 'AHL','AHL_UA_OBJ_VERNO_NULL' );
1375 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(p_flight_visit_sch_tbl(i)));
1376 FND_MSG_PUB.add;
1377 x_return_status := FND_API.G_RET_STS_ERROR;
1378 END IF;
1379 END IF;
1380
1381 IF p_flight_visit_sch_tbl(i).DML_OPERATION = 'C' OR
1382 p_flight_visit_sch_tbl(i).DML_OPERATION = 'c' OR
1383 p_flight_visit_sch_tbl(i).DML_OPERATION = 'U' OR
1384 p_flight_visit_sch_tbl(i).DML_OPERATION = 'u'
1385 THEN
1386 -- Unit Config Header id is a mandatory input field and cannot be null.
1387 IF (p_flight_visit_sch_tbl(i).UNIT_CONFIG_HEADER_ID IS NULL OR
1388 p_flight_visit_sch_tbl(i).UNIT_CONFIG_HEADER_ID = FND_API.G_MISS_NUM )AND
1389 (p_flight_visit_sch_tbl(i).UNIT_CONFIG_NAME IS NULL OR
1390 p_flight_visit_sch_tbl(i).UNIT_CONFIG_NAME = FND_API.G_MISS_CHAR)
1391
1392 THEN
1393 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1394 fnd_log.string
1395 (
1396 fnd_log.level_error,
1397 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1398 'Unit config header id and name, both cannot be null for '
1399 ||p_flight_visit_sch_tbl(i).unit_schedule_id
1400 );
1401 END IF;
1402 FND_MESSAGE.set_name( 'AHL','AHL_UA_INV_UC_NAME' );
1403 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(p_flight_visit_sch_tbl(i)));
1404 FND_MSG_PUB.add;
1405 x_return_status := FND_API.G_RET_STS_ERROR;
1406 END IF;
1407
1408 --Flight Number is a mandatory input field and cannot be null.
1409 IF p_flight_visit_sch_tbl(i).FLIGHT_NUMBER IS NULL OR
1410 p_flight_visit_sch_tbl(i).FLIGHT_NUMBER = FND_API.G_MISS_CHAR
1411 THEN
1412 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1413 fnd_log.string
1414 (
1415 fnd_log.level_error,
1416 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1417 'Flight Number cannot be null for '
1418 ||p_flight_visit_sch_tbl(i).unit_schedule_id
1419 );
1420 END IF;
1421 FND_MESSAGE.set_name( 'AHL','AHL_UA_FLG_NUMBER_NULL' );
1422 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(p_flight_visit_sch_tbl(i)));
1423 FND_MSG_PUB.add;
1424 x_return_status := FND_API.G_RET_STS_ERROR;
1425 END IF;
1426
1427 --Segment is a mandatory input field and cannot be null
1428 IF p_flight_visit_sch_tbl(i).SEGMENT IS NULL OR
1429 p_flight_visit_sch_tbl(i).SEGMENT = FND_API.G_MISS_CHAR
1430 THEN
1431 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1432 fnd_log.string
1433 (
1434 fnd_log.level_error,
1435 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1436 'Flight Segment cannot be null for '
1437 ||p_flight_visit_sch_tbl(i).unit_schedule_id
1438 );
1439 END IF;
1440 FND_MESSAGE.set_name( 'AHL','AHL_UA_SEGMENT_NULL' );
1441 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(p_flight_visit_sch_tbl(i)));
1442 FND_MSG_PUB.add;
1443 x_return_status := FND_API.G_RET_STS_ERROR;
1444 END IF;
1445
1446 --Departure_Dept_Id is a mandatory input field and cannot be null
1447 IF ( p_flight_visit_sch_tbl(i).DEPARTURE_DEPT_ID IS NULL OR
1448 p_flight_visit_sch_tbl(i).DEPARTURE_DEPT_ID = FND_API.G_MISS_NUM ) AND
1449 ( p_flight_visit_sch_tbl(i).DEPARTURE_DEPT_CODE IS NULL OR
1450 p_flight_visit_sch_tbl(i).DEPARTURE_DEPT_CODE = FND_API.G_MISS_CHAR )
1451 THEN
1452 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1453 fnd_log.string
1454 (
1455 fnd_log.level_error,
1456 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1457 'Departure Department id and code, both cannot be null '
1458 ||p_flight_visit_sch_tbl(i).unit_schedule_id
1459 );
1460 END IF;
1461 FND_MESSAGE.set_name( 'AHL','AHL_UA_DEP_DEPT_NULL' );
1462 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(p_flight_visit_sch_tbl(i)));
1463 FND_MSG_PUB.add;
1464 x_return_status := FND_API.G_RET_STS_ERROR;
1465 END IF;
1466
1467 --Departure_Org_Id is a mandatory input field and cannot be null
1468 IF ( p_flight_visit_sch_tbl(i).DEPARTURE_ORG_ID IS NULL OR
1469 p_flight_visit_sch_tbl(i).DEPARTURE_ORG_ID = FND_API.G_MISS_NUM ) AND
1470 ( p_flight_visit_sch_tbl(i).DEPARTURE_ORG_CODE IS NULL OR
1471 p_flight_visit_sch_tbl(i).DEPARTURE_ORG_CODE = FND_API.G_MISS_CHAR )
1472 THEN
1473 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1474 fnd_log.string
1475 (
1476 fnd_log.level_error,
1477 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1478 'Departure Org id and code, both cannot be null '
1479 ||p_flight_visit_sch_tbl(i).unit_schedule_id
1480 );
1481 END IF;
1482 FND_MESSAGE.set_name( 'AHL','AHL_UA_DEP_ORG_NULL' );
1483 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(p_flight_visit_sch_tbl(i)));
1484 FND_MSG_PUB.add;
1485 x_return_status := FND_API.G_RET_STS_ERROR;
1486 END IF;
1487
1488 --Arrival_Dept_Id is a mandatory input field and cannot be null
1489 IF ( p_flight_visit_sch_tbl(i).ARRIVAL_DEPT_ID IS NULL OR
1490 p_flight_visit_sch_tbl(i).ARRIVAL_DEPT_ID = FND_API.G_MISS_NUM ) AND
1491 ( p_flight_visit_sch_tbl(i).ARRIVAL_DEPT_CODE IS NULL OR
1492 p_flight_visit_sch_tbl(i).ARRIVAL_DEPT_CODE = FND_API.G_MISS_CHAR )
1493
1494 THEN
1495 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1496 fnd_log.string
1497 (
1498 fnd_log.level_error,
1499 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1500 'Arrival dept id and code, both cannot be null '
1501 ||p_flight_visit_sch_tbl(i).unit_schedule_id
1502 );
1503 END IF;
1504 FND_MESSAGE.set_name( 'AHL','AHL_UA_ARR_DEPT_NULL' );
1505 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(p_flight_visit_sch_tbl(i)));
1506 FND_MSG_PUB.add;
1507 x_return_status := FND_API.G_RET_STS_ERROR;
1508 END IF;
1509
1510 --Arrival_Org_Id is a mandatory input field and cannot be null
1511 IF ( p_flight_visit_sch_tbl(i).ARRIVAL_ORG_ID IS NULL OR
1512 p_flight_visit_sch_tbl(i).ARRIVAL_ORG_ID = FND_API.G_MISS_NUM ) AND
1513 ( p_flight_visit_sch_tbl(i).ARRIVAL_ORG_CODE IS NULL OR
1514 p_flight_visit_sch_tbl(i).ARRIVAL_ORG_CODE = FND_API.G_MISS_CHAR )
1515 THEN
1516 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1517 fnd_log.string
1518 (
1519 fnd_log.level_error,
1520 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1521 'Arrival org id and code, both cannot be null '
1522 ||p_flight_visit_sch_tbl(i).unit_schedule_id
1523 );
1524 END IF;
1525 FND_MESSAGE.set_name( 'AHL','AHL_UA_ARR_ORG_NULL' );
1526 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(p_flight_visit_sch_tbl(i)));
1527 FND_MSG_PUB.add;
1528 x_return_status := FND_API.G_RET_STS_ERROR;
1529 END IF;
1530
1531 --Est_Departure_Time is a mandatory input field and cannot be null
1532 IF p_flight_visit_sch_tbl(i).EST_DEPARTURE_TIME IS NULL OR
1533 p_flight_visit_sch_tbl(i).EST_DEPARTURE_TIME = FND_API.G_MISS_DATE
1534 THEN
1535 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1536 fnd_log.string
1537 (
1538 fnd_log.level_error,
1539 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1540 'estimated departure time cannot be null '
1541 ||p_flight_visit_sch_tbl(i).unit_schedule_id
1542 );
1543 END IF;
1544 FND_MESSAGE.set_name( 'AHL','AHL_UA_EST_DEP_TIME_NULL' );
1545 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(p_flight_visit_sch_tbl(i)));
1546 FND_MSG_PUB.add;
1547 x_return_status := FND_API.G_RET_STS_ERROR;
1548 END IF;
1549
1550 --Est_Arrival_Time is a mandatory input field and cannot be null
1551 IF p_flight_visit_sch_tbl(i).EST_ARRIVAL_TIME IS NULL OR
1552 p_flight_visit_sch_tbl(i).EST_ARRIVAL_TIME = FND_API.G_MISS_DATE
1553 THEN
1554 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1555 fnd_log.string
1556 (
1557 fnd_log.level_error,
1558 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1559 'Estimated arrival cannot be null '
1560 ||p_flight_visit_sch_tbl(i).unit_schedule_id
1561 );
1562 END IF;
1563 FND_MESSAGE.set_name( 'AHL','AHL_UA_EST_ARR_TIME_NULL' );
1564 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(p_flight_visit_sch_tbl(i)));
1565 FND_MSG_PUB.add;
1566 x_return_status := FND_API.G_RET_STS_ERROR;
1567 END IF;
1568
1569 --Visit_Reschedule_Mode is a mandatory input field and cannot be null
1570 IF ( p_flight_visit_sch_tbl(i).VISIT_RESCHEDULE_MODE IS NULL OR
1571 p_flight_visit_sch_tbl(i).VISIT_RESCHEDULE_MODE = FND_API.G_MISS_CHAR ) AND
1572 ( p_flight_visit_sch_tbl(i).VISIT_RESCHEDULE_MEANING IS NULL OR
1573 p_flight_visit_sch_tbl(i).VISIT_RESCHEDULE_MEANING = FND_API.G_MISS_CHAR )
1574 THEN
1575 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1576 fnd_log.string
1577 (
1578 fnd_log.level_error,
1579 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1580 'Visit Reschedule mode cannot be null for '
1581 ||p_flight_visit_sch_tbl(i).unit_schedule_id
1582 );
1583 END IF;
1584 FND_MESSAGE.set_name( 'AHL','AHL_UA_VST_RES_MODE_NULL' );
1585 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(p_flight_visit_sch_tbl(i)));
1586 FND_MSG_PUB.add;
1587 x_return_status := FND_API.G_RET_STS_ERROR;
1588 END IF;
1589
1590 ----------------- Validate visit paramters here, only when DML_OPERATION is C -------------------
1591 IF p_flight_visit_sch_tbl(i).DML_OPERATION = 'C' OR
1592 p_flight_visit_sch_tbl(i).DML_OPERATION = 'c'
1593 THEN
1594 -- Check for visit type.
1595 IF ( p_flight_visit_sch_tbl(i).VISIT_TYPE_CODE IS NULL OR
1596 p_flight_visit_sch_tbl(i).VISIT_TYPE_CODE = FND_API.G_MISS_CHAR ) AND
1597 ( p_flight_visit_sch_tbl(i).VISIT_TYPE_MEANING IS NULL OR
1598 p_flight_visit_sch_tbl(i).VISIT_TYPE_MEANING = FND_API.G_MISS_CHAR )
1599 THEN
1600 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1601 fnd_log.string
1602 (
1603 fnd_log.level_error,
1604 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1605 'Visit Type Code cannot be null for '
1606 ||p_flight_visit_sch_tbl(i).unit_schedule_id
1607 );
1608 END IF;
1609 FND_MESSAGE.set_name( 'AHL','AHL_UA_VST_TYPE_NULL' );
1610 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(p_flight_visit_sch_tbl(i)));
1611 FND_MSG_PUB.add;
1612 x_return_status := FND_API.G_RET_STS_ERROR;
1613 END IF;
1614
1615 -- Check for visit create type.
1616 IF ( p_flight_visit_sch_tbl(i).VISIT_CREATE_TYPE IS NULL OR
1617 p_flight_visit_sch_tbl(i).VISIT_CREATE_TYPE = FND_API.G_MISS_CHAR ) AND
1618 ( p_flight_visit_sch_tbl(i).VISIT_CREATE_MEANING IS NULL OR
1619 p_flight_visit_sch_tbl(i).VISIT_CREATE_MEANING = FND_API.G_MISS_CHAR )
1620 THEN
1621 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1622 fnd_log.string
1623 (
1624 fnd_log.level_error,
1625 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1626 'Visit create type code cannot be null for '
1627 ||p_flight_visit_sch_tbl(i).unit_schedule_id
1628 );
1629 END IF;
1630 FND_MESSAGE.set_name( 'AHL','AHL_UA_VST_CRE_TYPE_NULL' );
1631 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(p_flight_visit_sch_tbl(i)));
1632 FND_MSG_PUB.add;
1633 x_return_status := FND_API.G_RET_STS_ERROR;
1634 END IF;
1635 END IF;
1636
1637 END IF;
1638 END LOOP;
1639 END validate_api_inputs;
1640
1641
1642 ------------------------------------------------------------------------------------------
1643 -- Public procedure for Processing(Create/Update/Delete) Flight Schedules
1644 -- and for Auto Creating Visits.
1645 ------------------------------------------------------------------------------------------
1646
1647 PROCEDURE Process_FlightVisit_Sch
1648 (
1649 -- standard IN params
1650 p_api_version IN NUMBER,
1651 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
1652 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
1653 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
1654 p_default IN VARCHAR2 :=FND_API.G_FALSE,
1655 p_module_type IN VARCHAR2 :=NULL,
1656 -- standard OUT params
1657 x_return_status OUT NOCOPY VARCHAR2,
1658 x_msg_count OUT NOCOPY NUMBER,
1659 x_msg_data OUT NOCOPY VARCHAR2,
1660 -- procedure params
1661 p_x_flight_visit_sch_tbl IN OUT NOCOPY FLIGHT_VISIT_SCH_TBL_TYPE
1662 )
1663 IS
1664 l_api_name CONSTANT VARCHAR2(30) := 'Process_FlightVisit_Sch';
1665 l_api_version CONSTANT NUMBER := 1.0;
1666 BEGIN
1667 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1668 fnd_log.string
1669 (
1670 fnd_log.level_procedure,
1671 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
1672 'At the start of PLSQL procedure Process_FlightVisit_Sch'
1673 );
1674 END IF;
1675
1676 --define a savepoint for the procedure
1677 SAVEPOINT p_x_flght_visit_schedules_pub;
1678
1679 -- Initialize return status to success initially
1680 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1681
1682 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1683 p_api_version,
1684 l_api_name,G_PKG_NAME)
1685 THEN
1686 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1687 END IF;
1688
1689 -- Initialize message list if p_init_msg_list is set to TRUE.
1690 IF FND_API.to_boolean(p_init_msg_list) THEN
1691 FND_MSG_PUB.initialize;
1692 END IF;
1693
1694
1695 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1696 fnd_log.string
1697 (
1698 fnd_log.level_statement,
1699 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1700 'Before calling the private API.....'
1701 );
1702 END IF;
1703
1704 -- API call to validate all API input parameters.
1705 validate_api_inputs(
1706 p_flight_visit_sch_tbl => p_x_flight_visit_sch_tbl,
1707 x_return_status => x_return_status
1708 );
1709
1710 -- If any severe error occurs, then, abort API.
1711 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1712 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1713 fnd_log.string
1714 (
1715 fnd_log.level_error,
1716 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1717 'validate_api_inputs API raised expected error....'
1718 );
1719 END IF;
1720 RAISE FND_API.G_EXC_ERROR;
1721 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1722 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1723 fnd_log.string
1724 (
1725 fnd_log.level_error,
1726 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1727 'validate_api_inputs API raised unexpected error....'
1728 );
1729 END IF;
1730 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1731 END IF;
1732
1733 --Procedure to populate Flight Schedule rec from flight_visit_rec
1734
1735 handle_flight_schedules(
1736 p_x_flght_visit_schedules_tbl => p_x_flight_visit_sch_tbl,
1737 x_return_status => x_return_status,
1738 x_msg_count => x_msg_count,
1739 x_msg_data => x_msg_data
1740
1741
1742 );
1743
1744 --Call procedure to auto create visit
1745 auto_create_transit_visit(
1746 p_x_flight_visit_schedules_tbl => p_x_flight_visit_sch_tbl,
1747 x_return_status => x_return_status,
1748 x_msg_count => x_msg_count,
1749 x_msg_data => x_msg_data
1750 );
1751
1752 -- Standard check for p_commit
1753 IF FND_API.To_Boolean (p_commit)
1754 THEN
1755 COMMIT;
1756 END IF;
1757
1758 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1759 fnd_log.string
1760 (
1761 fnd_log.level_procedure,
1762 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
1763 'At the end of PLSQL procedure Process_FlightVisit_Sch'
1764 );
1765 END IF;
1766
1767 EXCEPTION
1768 WHEN FND_API.G_EXC_ERROR THEN
1769 x_return_status := FND_API.G_RET_STS_ERROR;
1770 Rollback to p_x_flght_visit_schedules_pub;
1771 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1772 p_data => x_msg_data,
1773 p_encoded => fnd_api.g_false );
1774
1775 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1776 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1777 Rollback to p_x_flght_visit_schedules_pub;
1778 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1779 p_data => x_msg_data,
1780 p_encoded => fnd_api.g_false );
1781
1782 WHEN OTHERS THEN
1783 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1784 Rollback to p_x_flght_visit_schedules_pub;
1785 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1786 THEN
1787 fnd_msg_pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
1788 p_procedure_name => l_api_name,
1789 p_error_text => SUBSTR(SQLERRM,1,240) );
1790 END IF;
1791 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1792 p_data => x_msg_data,
1793 p_encoded => fnd_api.g_false );
1794
1795 END Process_FlightVisit_Sch;
1796
1797 END AHL_UA_FLIGHT_SCHEDULES_PUB;