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