[Home] [Help]
PACKAGE BODY: APPS.AHL_UA_UNIT_SCHEDULES_PVT
Source
1 PACKAGE BODY AHL_UA_UNIT_SCHEDULES_PVT AS
2 /* $Header: AHLVUUSB.pls 120.6 2008/03/05 00:05:14 adivenka ship $ */
3
4 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
5
6 ---------------------------------
7 -- Common constants and variables
8 ---------------------------------
9 l_dummy_varchar VARCHAR2(1);
10
11 /* Variable to indicate whether to use actual flight times */
12 G_USE_ACTUALS CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
13
14 /* Constants to define event type */
15 G_EVENT_TYPE_VISIT CONSTANT VARCHAR2(12) := 'VISIT';
16 G_EVENT_TYPE_FLIGHT CONSTANT VARCHAR2(12) := 'FLIGHT';
17
18 /* Variable to determine whether to consider department conflcits. Will be initialized from profile */
19 G_DEPT_CONFLICT_PROF CONSTANT VARCHAR2(1) := FND_PROFILE.VALUE('AHL_UA_USE_DEPT_CONFLICT');
20 G_DEPT_CONFLICT BOOLEAN;
21 /* Function to get minimum number of minutes to considered as MO from profiles*/
22 FUNCTION Get_Min_Time_MO RETURN NUMBER;
23 /* Variable to determine whether there is a maintenance opportunity
24 * Holds value in minutes. This should be equal or more as gap value between two events
25 * Will be initialized from profile
26 */
27 G_MIN_TIME_MO CONSTANT NUMBER := Get_Min_Time_MO;
28
29
30 -----------------------------------
31 -- Non-spec Procedure Signatures --
32 -----------------------------------
33 ---------------------------------------------------------------------------------
34 -- Determines Maintenance Opportunity and Conflcits for the current flight record
35 -- Prepare it with transient variable calculations
36 -- Determine whether to add record in appropriate table
37 ---------------------------------------------------------------------------------
38 PROCEDURE populate_unit_schedule_rec
39 (
40 p_unit_flight_schedule_rec IN AHL_UA_FLIGHT_SCHEDULES_PVT.Flight_Schedule_Rec_Type,
41 p_prev_event_type IN VARCHAR2,
42 p_window_event IN BOOLEAN,
43 p_prev_unit_schedule_rec IN AHL_UA_UNIT_SCHEDULES_PVT.Unit_Schedule_rec_type,
44 p_prev_visit_schedule_rec IN AHL_UA_UNIT_SCHEDULES_PVT.Visit_Schedule_rec_type,
45 p_x_MEvent_Header_Rec IN OUT NOCOPY AHL_UA_UNIT_SCHEDULES_PVT.MEvent_Header_Rec_Type,
46 p_x_Unit_Schedule_tbl IN OUT NOCOPY AHL_UA_UNIT_SCHEDULES_PVT.Unit_Schedule_Tbl_Type,
47 p_x_Visit_Schedule_tbl IN OUT NOCOPY AHL_UA_UNIT_SCHEDULES_PVT.Visit_Schedule_Tbl_Type,
48 x_Unit_Schedule_Rec OUT NOCOPY AHL_UA_UNIT_SCHEDULES_PVT.Unit_Schedule_rec_type
49 );
50
51 ---------------------------------------------------------------------------------
52 -- Determines Maintenance Opportunity and Conflcits for the current visit record
53 -- Prepare it with transient variable calculations
54 -- Determine whether to add record in appropriate table
55 ---------------------------------------------------------------------------------
56 PROCEDURE populate_visit_schedule_rec
57 (
58 p_visit_rec IN AHL_VWP_VISITS_PVT.Visit_Rec_Type,
59 p_prev_event_type IN VARCHAR2,
60 p_window_event IN BOOLEAN,
61 p_prev_unit_schedule_rec IN AHL_UA_UNIT_SCHEDULES_PVT.Unit_Schedule_rec_type,
62 p_prev_visit_schedule_rec IN AHL_UA_UNIT_SCHEDULES_PVT.Visit_Schedule_rec_type,
63 p_x_MEvent_Header_Rec IN OUT NOCOPY AHL_UA_UNIT_SCHEDULES_PVT.MEvent_Header_Rec_Type,
64 p_x_Unit_Schedule_tbl IN OUT NOCOPY AHL_UA_UNIT_SCHEDULES_PVT.Unit_Schedule_Tbl_Type,
65 p_x_Visit_Schedule_tbl IN OUT NOCOPY AHL_UA_UNIT_SCHEDULES_PVT.Visit_Schedule_Tbl_Type,
66 x_visit_schedule_rec OUT NOCOPY AHL_UA_UNIT_SCHEDULES_PVT.Visit_Schedule_rec_type
67 );
68
69 ------------------------------------------
70 -- Spec Procedure Search_Unit_Schedules --
71 ------------------------------------------
72 PROCEDURE Search_Unit_Schedules
73 (
74 p_api_version IN NUMBER,
75 x_return_status OUT NOCOPY VARCHAR2,
76 x_msg_count OUT NOCOPY NUMBER,
77 x_msg_data OUT NOCOPY VARCHAR2,
78 p_unit_schedules_search IN Unit_Schedules_Search_Rec_Type,
79 x_unit_schedules_results OUT NOCOPY Unit_Schedules_Result_Tbl_Type
80 )
81 IS
82 -- 1. Declare local variables
83 l_api_name CONSTANT VARCHAR2(30) := 'Search_Unit_Schedules';
84 l_api_version CONSTANT NUMBER := 1.0;
85 l_return_status VARCHAR2(1);
86 l_msg_count NUMBER;
87 l_msg_data VARCHAR2(2000);
88 L_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
89
90 l_srch_unit_schedule Unit_Schedules_Result_Rec_Type;
91
92 l_unit_config_id NUMBER;
93 l_unit_config_name VARCHAR2(80);
94
95 l_time_increment NUMBER(25,15) := 0;
96 l_start_time DATE;
97 l_end_time DATE;
98 l_col_idx NUMBER := 0;
99 l_row_idx NUMBER := 0;
100 l_tbl_dummy_idx NUMBER;
101
102 l_mevent_header_rec MEvent_Header_Rec_Type;
103 l_unit_schedule_tbl Unit_Schedule_Tbl_Type;
104 l_visit_schedule_tbl Visit_Schedule_Tbl_Type;
105
106 l_MO_tbl VARCHAR2(12) := NULL;
107 l_MO_tbl_idx NUMBER := 0;
108
109 l_temp_srch_sched_id NUMBER;
110 l_temp_sched_type VARCHAR2(2);
111 l_temp_unit_sched_id NUMBER;
112 l_temp_visit_id NUMBER;
113 l_temp_visit_status VARCHAR2(30);
114 l_temp_visit_is_org_valid VARCHAR2(1);
115 l_temp_visit_name VARCHAR2(80);
116 l_temp_start_time DATE;
117 l_temp_end_time DATE;
118 l_temp_dept_id NUMBER;
119 l_temp_org_id NUMBER;
120
121 -- Define a local "constant" to denote the number of time spans that will be returned in the search criteria. Each time span is of size p_time_increment
122 L_MAX_TIME_SPANS CONSTANT NUMBER := 10;
123
124 -- Define event schedule type constants
125 L_EVENT_CONFLICT CONSTANT VARCHAR2(2) := 'CO';
126 L_EVENT_MULT_EVENT CONSTANT VARCHAR2(2) := 'ME';
127 L_EVENT_MULT_MAINTOP CONSTANT VARCHAR2(2) := 'MM';
128 L_EVENT_MAINTOP CONSTANT VARCHAR2(2) := 'MO';
129 L_EVENT_MAINT_ORG_NOOU CONSTANT VARCHAR2(2) := 'MX';
130 L_EVENT_VISIT CONSTANT VARCHAR2(2) := 'VS';
131 L_EVENT_FLIGHT CONSTANT VARCHAR2(2) := 'FS';
132 L_EVENT_NOTHING CONSTANT VARCHAR2(2) := 'XX';
133
134 -- 3. Define cursor get_unit_details to retrieve details of units satisfying the search criteria
135 cursor get_unit_details
136 (
137 p_unit_name varchar2,
138 p_item_number varchar2,
139 p_serial_number varchar2
140 )
141 is
142 -- Bug No #4916304: APPSPERF fixes {priyan}
143 select
144 u.unit_config_header_id uc_header_id,
145 u.name uc_name
146 from
147 ahl_unit_config_headers u,
148 mtl_system_items_kfv i,
149 csi_item_instances c
150 where
151 --priyan Bug# 5303188
152 --ahl_util_uc_pkg.get_uc_status_code (u.unit_config_header_id) IN ('COMPLETE', 'INCOMPLETE') and
153 -- fix for bug number 5528416
154 ahl_util_uc_pkg.get_uc_status_code (u.unit_config_header_id) NOT IN ('DRAFT', 'EXPIRED') and
155 --u.unit_config_status_code in ('COMPLETE', 'INCOMPLETE') and
156 u.csi_item_instance_id = c.instance_id and
157 c.inventory_item_id = i.inventory_item_id and
158 c.last_vld_organization_id = i.organization_id and
159 upper(u.name) like upper(nvl(p_unit_schedules_search.unit_name, '%')) and
160 upper(i.concatenated_segments) like upper(nvl(p_unit_schedules_search.item_number, '%')) and
161 upper(c.serial_number) like upper(nvl(p_unit_schedules_search.serial_number, '%'))
162 order by uc_name desc;
163 -- Added sorting on DESC since OAF VO.insertRow inserts a row before the current row, effectively reversing the sort sequence in the UI
164 /*
165 select uc_header_id, uc_name
166 from ahl_unit_config_headers_v
167 where upper(uc_name) like nvl(upper(p_unit_schedules_search.unit_name), '%')
168 and upper(item_number) like nvl(upper(p_unit_schedules_search.item_number), '%')
169 and upper(serial_number) like nvl(upper(p_unit_schedules_search.serial_number), '%')
170 and uc_status_code IN ('COMPLETE', 'INCOMPLETE')
171 */
172
173 BEGIN
174 -- Standard start of API savepoint
175 SAVEPOINT Search_Unit_Schedules_SP;
176
177 -- Standard call to check for call compatibility
178 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
179 THEN
180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
181 END IF;
182
183 -- Initialize message list by default
184 FND_MSG_PUB.Initialize;
185
186 -- Initialize API return status to success
187 x_return_status := FND_API.G_RET_STS_SUCCESS;
188
189 -- Log API entry point
190 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
191 THEN
192 fnd_log.string
193 (
194 fnd_log.level_procedure,
195 L_DEBUG_MODULE||'.begin',
196 'At the start of PLSQL procedure'
197 );
198 END IF;
199
200 -- API body starts here
201 -- 4. Delete all records from global temporary table ahl_srch_unit_schedules
202 -- DELETE FROM AHL_SRCH_UNIT_SCHEDULES;
203 -- If global temp table is deleted, then multiple navigations to and fro from Search Unit Schedules UI cannot be
204 -- supported, hence retaining all data in the table till session is killed. Not sure whether it will be a
205 -- performance hit or not, will repeal both frontend and backend code in that case.
206
207 -- 5. If (p_unit_schedules_search is NULL or p_unit_schedules_search.start_date_time is null), then display error "Start date and time are mandatory parameters to perform a search on Unit Schedules"
208 IF (p_unit_schedules_search.start_date_time IS NULL OR p_unit_schedules_search.start_date_time = FND_API.G_MISS_DATE)
209 THEN
210 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UA_SUS_START_DATE_NULL');
211 FND_MSG_PUB.ADD;
212 RAISE FND_API.G_EXC_ERROR;
213 END IF;
214
215 -- 6. If (p_unit_schedules_search is NULL or p_unit_schedules_search.time_increment is null or p_unit_schedules_search.time_uom is null), then display error "Display increment is mandatory parameter to perform a search on Unit Schedules"
216 IF (
217 p_unit_schedules_search.time_increment IS NULL OR p_unit_schedules_search.time_increment = FND_API.G_MISS_NUM OR
218 p_unit_schedules_search.time_uom IS NULL OR p_unit_schedules_search.time_uom = FND_API.G_MISS_CHAR
219 )
220 THEN
221 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UA_SUS_TIME_UOM_NULL');
222 FND_MSG_PUB.ADD;
223 RAISE FND_API.G_EXC_ERROR;
224 END IF;
225
226 -- 7. If (p_unit_schedules_search.time_increment is not a positive integer), then display error "Display increment should be a positive integer"
227 IF (p_unit_schedules_search.time_increment <= 0 OR p_unit_schedules_search.time_increment <> TRUNC(p_unit_schedules_search.time_increment))
228 THEN
229 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UA_SUS_TIME_INTEGER');
230 FND_MSG_PUB.ADD;
231 RAISE FND_API.G_EXC_ERROR;
232 END IF;
233
234 -- 8. Validate p_unit_schedules_search.time_uom using cursor check_time_uom_exists. If cursor does not return 1 record (and only 1), the display error "Invalid UOM for display increment"
235 IF NOT (AHL_UTIL_MC_PKG.Validate_Lookup_Code('AHL_UA_TIME_UOM', p_unit_schedules_search.time_uom))
236 THEN
237 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UA_SUS_TIME_UOM_INVALID');
238 FND_MESSAGE.SET_TOKEN('UOM', p_unit_schedules_search.time_uom);
239 FND_MSG_PUB.ADD;
240 RAISE FND_API.G_EXC_ERROR;
241 END IF;
242
243 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
244 THEN
245 fnd_log.string
246 (
247 fnd_log.level_statement,
248 L_DEBUG_MODULE,
249 'Basic validations done'
250 );
251 END IF;
252
253 -- Calculat the time increment, based on the UOMs
254 IF (p_unit_schedules_search.time_uom = 'MIN')
255 THEN
256 l_time_increment := 1/1440;
257 ELSIF (p_unit_schedules_search.time_uom = 'HOUR')
258 THEN
259 l_time_increment := 1/24;
260 ELSIF (p_unit_schedules_search.time_uom = 'DAY')
261 THEN
262 l_time_increment := 1;
263 END IF;
264
265 l_time_increment := l_time_increment * p_unit_schedules_search.time_increment;
266
267 -- 10. Open cursor get_unit_details and loop through the records
268
269 OPEN get_unit_details
270 (
271 p_unit_schedules_search.unit_name,
272 p_unit_schedules_search.item_number,
273 p_unit_schedules_search.serial_number
274 );
275 LOOP
276 FETCH get_unit_details INTO l_unit_config_id, l_unit_config_name;
277 EXIT WHEN get_unit_details%NOTFOUND;
278
279 -- Initialize outer loop counter (for the particular unit_config_id)
280 l_row_idx := l_row_idx + 1;
281
282 -- Initialize start date time to user-entered start date time (this will be the current start date time for the first time span)
283 l_start_time := p_unit_schedules_search.start_date_time;
284
285 -- Start inner loop (for each time span)
286 FOR l_col_idx IN 1..L_MAX_TIME_SPANS
287 LOOP
288 -- Initialize end date time of the time span to current start date time + time increment/span
289 l_end_time := l_start_time + l_time_increment;
290
291 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
292 THEN
293 fnd_log.string
294 (
295 fnd_log.level_statement,
296 L_DEBUG_MODULE,
297 '[l_unit_config_id='||TO_CHAR(l_unit_config_id)||'][l_unit_config_name='||l_unit_config_name||'][l_row_idx='||TO_CHAR(l_row_idx)||'][l_col_idx='||TO_CHAR(l_col_idx)||']'||
298 '[l_start_time='||TO_CHAR(l_start_time, 'DD-MM-YYYY HH24:MI:SS')||'][l_end_time='||TO_CHAR(l_end_time, 'DD-MM-YYYY HH24:MI:SS')||'][l_time_increment='||TO_CHAR(l_time_increment)||']'
299 );
300 END IF;
301
302 -- Actual code logic for each time span starts here
303
304 -- ii. Call AHL_UA_UNIT_SCHEDULES_PVT.Get_MEvent_Details to retrieve details of unit schedule event for this l_unit_config_id and within the time period l_start_time to l_end_time.
305 -- Populate l_mevent_header_rec with these 3 parameters and pass to the API. Also pass p_module_type = 'US' to prevent unnecessary populating of parameters in this API.
306 l_mevent_header_rec.unit_config_header_id := l_unit_config_id;
307 l_mevent_header_rec.start_time := l_start_time;
308 l_mevent_header_rec.end_time := l_end_time;
309
310 Get_MEvent_Details
311 (
312 p_api_version => 1.0,
313 p_module_type => 'US',
314 x_return_status => l_return_status,
315 x_msg_count => l_msg_count,
316 x_msg_data => l_msg_data,
317 p_x_MEvent_Header_Rec => l_mevent_header_rec,
318 x_Unit_Schedule_tbl => l_unit_schedule_tbl,
319 x_Visit_Schedule_tbl => l_visit_schedule_tbl
320 );
321
322 -- Verify the Get_MEvent_Details does not throw unexpected errors, etc
323 IF (l_return_status = FND_API.G_RET_STS_ERROR)
324 THEN
325 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
326 THEN
327 fnd_log.string
328 (
329 fnd_log.level_error,
330 L_DEBUG_MODULE,
331 'Call to Get_MEvent_Details API returned EXPECTED error'
332 );
333 END IF;
334 RAISE FND_API.G_EXC_ERROR;
335 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
336 THEN
337 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
338 THEN
339 fnd_log.string
340 (
341 fnd_log.level_error,
342 L_DEBUG_MODULE,
343 'Call to Get_MEvent_Details API returned UNEXPECTED error'
344 );
345 END IF;
346 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
347 END IF;
348
349 -- Check Error Message stack.
350 x_msg_count := FND_MSG_PUB.count_msg;
351 IF x_msg_count > 0
352 THEN
353 RAISE FND_API.G_EXC_ERROR;
354 END IF;
355
356 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
357 THEN
358 fnd_log.string
359 (
360 fnd_log.level_statement,
361 L_DEBUG_MODULE,
362 'l_mevent_header_rec = [EVENT_COUNT='||TO_CHAR(l_mevent_header_rec.EVENT_COUNT)||'][HAS_CONFLICT='||l_mevent_header_rec.HAS_CONFLICT||'][HAS_MOPPORTUNITY='||l_mevent_header_rec.HAS_MOPPORTUNITY||']'||
363 'l_unit_schedule_tbl = [COUNT='||TO_CHAR(l_unit_schedule_tbl.COUNT)||']'||'l_visit_schedule_tbl = [COUNT='||TO_CHAR(l_visit_schedule_tbl.COUNT)||']'
364 );
365 END IF;
366
367 -- iii. Based on the following logic for the type of event, populate relevant details in the global temporary table ahl_srch_unit_schedules and also in the output record
368
369 -- 1. Initialize all temporary table values to null
370 l_temp_unit_sched_id := null;
371 l_temp_visit_id := null;
372 l_temp_visit_name := null;
373 l_temp_visit_status := null;
374 l_temp_visit_is_org_valid:= null;
375 l_temp_start_time := null;
376 l_temp_end_time := null;
377 l_temp_dept_id := null;
378 l_temp_org_id := null;
379
380 -- 2. If (l_mevent_header_rec.event_count > 1), implies that there are multiple events
381 IF (l_mevent_header_rec.event_count > 1)
382 THEN
383 -- a. If (l_mevent_header_rec.has_conflict = FND_API.G_TRUE), implies that there is a conflict
384 IF (l_mevent_header_rec.has_conflict = FND_API.G_TRUE)
385 THEN
386 l_temp_sched_type := L_EVENT_CONFLICT;
387
388 -- b. Else, if (l_mevent_header_rec.has_mopportunity = FND_API.G_TRUE), implies that there are a multiple events with maintenance opportunity
389 ELSIF (l_mevent_header_rec.has_mopportunity = FND_API.G_TRUE)
390 THEN
391 l_temp_sched_type := L_EVENT_MULT_MAINTOP;
392 -- c. Else, implies that there are multiple events
393 ELSE
394 l_temp_sched_type := L_EVENT_MULT_EVENT;
395 END IF;
396
397 l_temp_start_time := l_start_time;
398 l_temp_end_time := l_end_time;
399
400 -- 3. If (l_mevent_header_rec.event_count = 0), implies that there are no events
401 ELSIF (l_mevent_header_rec.event_count = 0)
402 THEN
403 -- a. If (l_mevent_header_rec.has_mopportunity = FND_API.G_TRUE), implies that there is a maintenance opportunity
404 IF (l_mevent_header_rec.has_mopportunity = FND_API.G_TRUE)
405 THEN
406 l_temp_sched_type := L_EVENT_MAINTOP;
407
408 -- The MO record can either be in l_visit_schedule_tbl or l_unit_schedule_tbl is one of its records, need to know
409 -- where and then retrieve the correct information from that record
410 l_MO_tbl := null;
411 l_MO_tbl_idx := 0;
412
413 -- First check l_unit_schedule_tbl
414 IF (l_unit_schedule_tbl.count > 0)
415 THEN
416 FOR l_tbl_dummy_idx IN l_unit_schedule_tbl.FIRST..l_unit_schedule_tbl.LAST
417 LOOP
418 IF (l_unit_schedule_tbl(l_tbl_dummy_idx).HAS_MOPPORTUNITY = FND_API.G_TRUE)
419 THEN
420 l_MO_tbl_idx := l_tbl_dummy_idx;
421 l_MO_tbl := G_EVENT_TYPE_FLIGHT;
422 EXIT;
423 END IF;
424 END LOOP;
425 END IF;
426
427 -- If not found in l_unit_schedule_tbl, then check l_visit_schedule_tbl
428 IF (l_visit_schedule_tbl.count > 0 AND l_MO_tbl IS NULL)
429 THEN
430 FOR l_tbl_dummy_idx IN l_visit_schedule_tbl.FIRST..l_visit_schedule_tbl.LAST
431 LOOP
432 IF (l_visit_schedule_tbl(l_tbl_dummy_idx).HAS_MOPPORTUNITY = FND_API.G_TRUE)
433 THEN
434 l_MO_tbl_idx := l_tbl_dummy_idx;
435 l_MO_tbl := G_EVENT_TYPE_VISIT;
436 EXIT;
437 END IF;
438 END LOOP;
439 END IF;
440
441 -- ii. If (l_MO_tbl = G_EVENT_TYPE_FLIGHT), implies that the preceding event to this time block is a flight schedule
442 IF (l_MO_tbl = G_EVENT_TYPE_FLIGHT)
443 THEN
444 -- 1. If (l_unit_schedule_tbl(l_MO_tbl).is_prev_org_valid = FND_API.G_FALSE), implies that the the org is not in the user's OU
445 IF (l_unit_schedule_tbl(l_MO_tbl_idx).is_prev_org_valid = FND_API.G_FALSE)
446 THEN
447 l_temp_sched_type := L_EVENT_MAINT_ORG_NOOU;
448 ELSE
449 l_temp_visit_name := l_unit_schedule_tbl(l_MO_tbl_idx).prev_flight_number;
450 l_temp_unit_sched_id := l_unit_schedule_tbl(l_MO_tbl_idx).prev_unit_schedule_id;
451 l_temp_start_time := l_unit_schedule_tbl(l_MO_tbl_idx).prev_event_end_time;
452 l_temp_end_time := l_unit_schedule_tbl(l_MO_tbl_idx).departure_time;
453 l_temp_dept_id := l_unit_schedule_tbl(l_MO_tbl_idx).prev_event_dep_id;
454 l_temp_org_id := l_unit_schedule_tbl(l_MO_tbl_idx).prev_event_org_id;
455 END IF;
456
457 -- iii. Else, if (l_MO_tbl = G_EVENT_TYPE_VISIT), implies that the preceding event to this time block is a visit schedule
458 ELSIF (l_MO_tbl = G_EVENT_TYPE_VISIT)
459 THEN
460 -- 1. If (l_visit_schedule_tbl(l_MO_tbl).is_prev_org_valid = FND_API.G_FALSE), implies that the the org is not in the user's OU
461 IF (l_visit_schedule_tbl(l_MO_tbl_idx).is_prev_org_valid = FND_API.G_FALSE)
462 THEN
463 l_temp_sched_type := L_EVENT_MAINT_ORG_NOOU;
464 ELSE
465 l_temp_visit_name := l_visit_schedule_tbl(l_MO_tbl_idx).prev_flight_number;
466 l_temp_unit_sched_id := l_visit_schedule_tbl(l_MO_tbl_idx).prev_unit_schedule_id;
467 l_temp_start_time := l_visit_schedule_tbl(l_MO_tbl_idx).prev_event_end_time;
468 l_temp_end_time := l_visit_schedule_tbl(l_MO_tbl_idx).start_time;
469 l_temp_dept_id := l_visit_schedule_tbl(l_MO_tbl_idx).prev_event_dep_id;
470 l_temp_org_id := l_visit_schedule_tbl(l_MO_tbl_idx).prev_event_org_id;
471 END IF;
472
473 -- iv. Else, there is something wrong (since atleast one l_unit_schedule_tbl or one l_visit_schedule_tbl is expected)
474 ELSE
475 l_temp_sched_type := L_EVENT_NOTHING;
476 END IF;
477
478 -- b. Else, implies that there is no event (based on the minimum window for a maintenance opportunity or no flight schedules are created for the unit)
479 ELSE
480 l_temp_sched_type := L_EVENT_NOTHING;
481 END IF;
482
483 -- 4. If (l_mevent_header_rec.event_count = 1)
484 ELSIF (l_mevent_header_rec.event_count = 1)
485 THEN
486 -- a. If (l_mevent_header_rec.has_conflict = FND_API.G_TRUE), implies that there is a conflict
487 IF (l_mevent_header_rec.has_conflict = FND_API.G_TRUE)
488 THEN
489 l_temp_sched_type := L_EVENT_CONFLICT;
490 l_temp_start_time := l_start_time;
491 l_temp_end_time := l_end_time;
492
493 -- b. Else, if (l_mevent_header_rec.has_mopportunity = FND_API.G_FALSE), implies either a visit or flight is scheduled
494 ELSIF (l_mevent_header_rec.has_mopportunity = FND_API.G_FALSE)
495 THEN
496 -- i. If (l_unit_schedule_tbl.count > 0), implies that a flight is scheduled
497 IF (l_unit_schedule_tbl.count > 0)
498 THEN
499 l_temp_sched_type := L_EVENT_FLIGHT;
500 l_temp_unit_sched_id := l_unit_schedule_tbl(0).unit_schedule_id;
501 -- ii. Else, if (l_unit_schedule_tbl.count > 0), implies that a visit is scheduled
502 ELSIF (l_visit_schedule_tbl.count > 0)
503 THEN
504 l_temp_sched_type := L_EVENT_VISIT;
505 l_temp_visit_id := l_visit_schedule_tbl(0).visit_id;
506 l_temp_visit_status := l_visit_schedule_tbl(0).visit_status_code;
507 l_temp_visit_is_org_valid := l_visit_schedule_tbl(0).is_org_valid;
508 -- iii. Else, there is something wrong
509 ELSE
510 l_temp_sched_type := L_EVENT_NOTHING;
511 END IF;
512
513 -- c. Else, if (l_mevent_header_rec.has_mopportunity = FND_API.G_TRUE), implies there is a maintenance opportunity
514 ELSIF (l_mevent_header_rec.has_mopportunity = FND_API.G_TRUE)
515 THEN
516 /*
517 -- The earlier logic was 1 event (flight / visit) with MO(s) will be displayed as MO according to the
518 -- priorities of displaying event icons
519 --
520 -- This was confusing to the User, also presented an incorrect picture, so after discussions with PM the logic
521 -- has now been modified to 1 event (flight / visit) with MO(s) is equivalent to Multiple Events with MO
522 --
523 -- NOTE: Please do not remove this code-block, the modifications are after this
524
525 l_temp_sched_type := L_EVENT_MAINTOP;
526
527 -- The MO record can either be in l_visit_schedule_tbl or l_unit_schedule_tbl is one of its records, need to know
528 -- where and then retrieve the correct information from that record
529 l_MO_tbl := null;
530 l_MO_tbl_idx := 0;
531
532 -- First check l_unit_schedule_tbl
533 IF (l_unit_schedule_tbl.count > 0)
534 THEN
535 FOR l_tbl_dummy_idx IN l_unit_schedule_tbl.FIRST..l_unit_schedule_tbl.LAST
536 LOOP
537 IF (l_unit_schedule_tbl(l_tbl_dummy_idx).HAS_MOPPORTUNITY = FND_API.G_TRUE)
538 THEN
539 l_MO_tbl_idx := l_tbl_dummy_idx;
540 l_MO_tbl := G_EVENT_TYPE_FLIGHT;
541 EXIT;
542 END IF;
543 END LOOP;
544 END IF;
545
546 -- If not found in l_unit_schedule_tbl, then check l_visit_schedule_tbl
547 IF (l_visit_schedule_tbl.count > 0 AND l_MO_tbl IS NULL)
548 THEN
549 FOR l_tbl_dummy_idx IN l_visit_schedule_tbl.FIRST..l_visit_schedule_tbl.LAST
550 LOOP
551 IF (l_visit_schedule_tbl(l_tbl_dummy_idx).HAS_MOPPORTUNITY = FND_API.G_TRUE)
552 THEN
553 l_MO_tbl_idx := l_tbl_dummy_idx;
554 l_MO_tbl := G_EVENT_TYPE_VISIT;
555 EXIT;
556 END IF;
557 END LOOP;
558 END IF;
559
560 -- ii. If (l_MO_tbl = G_EVENT_TYPE_FLIGHT), implies that the preceding event to this time block is a flight schedule
561 IF (l_MO_tbl = G_EVENT_TYPE_FLIGHT)
562 THEN
563 -- 1. If (l_unit_schedule_tbl(l_MO_tbl).is_prev_org_valid = FND_API.G_FALSE), implies that the the org is not in the user's OU
564 IF (l_unit_schedule_tbl(l_MO_tbl_idx).is_prev_org_valid = FND_API.G_FALSE)
565 THEN
566 l_temp_sched_type := L_EVENT_MAINT_ORG_NOOU;
567 ELSE
568 l_temp_visit_name := l_unit_schedule_tbl(l_MO_tbl_idx).prev_flight_number;
569 l_temp_unit_sched_id := l_unit_schedule_tbl(l_MO_tbl_idx).prev_unit_schedule_id;
570 l_temp_start_time := l_unit_schedule_tbl(l_MO_tbl_idx).prev_event_end_time;
571 l_temp_end_time := l_unit_schedule_tbl(l_MO_tbl_idx).departure_time;
572 l_temp_dept_id := l_unit_schedule_tbl(l_MO_tbl_idx).prev_event_dep_id;
573 l_temp_org_id := l_unit_schedule_tbl(l_MO_tbl_idx).prev_event_org_id;
574 END IF;
575
576 -- iii. Else, if (l_MO_tbl = G_EVENT_TYPE_VISIT), implies that the preceding event to this time block is a visit schedule
577 ELSIF (l_MO_tbl = G_EVENT_TYPE_VISIT)
578 THEN
579 -- 1. If (l_visit_schedule_tbl(l_MO_tbl).is_prev_org_valid = FND_API.G_FALSE), implies that the the org is not in the user's OU
580 IF (l_visit_schedule_tbl(l_MO_tbl_idx).is_prev_org_valid = FND_API.G_FALSE)
581 THEN
582 l_temp_sched_type := L_EVENT_MAINT_ORG_NOOU;
583 ELSE
584 l_temp_visit_name := l_visit_schedule_tbl(l_MO_tbl_idx).prev_flight_number;
585 l_temp_unit_sched_id := l_visit_schedule_tbl(l_MO_tbl_idx).prev_unit_schedule_id;
586 l_temp_start_time := l_visit_schedule_tbl(l_MO_tbl_idx).prev_event_end_time;
587 l_temp_end_time := l_visit_schedule_tbl(l_MO_tbl_idx).start_time;
588 l_temp_dept_id := l_visit_schedule_tbl(l_MO_tbl_idx).prev_event_dep_id;
589 l_temp_org_id := l_visit_schedule_tbl(l_MO_tbl_idx).prev_event_org_id;
590 END IF;
591
592 -- iv. Else, there is something wrong (since atleast one l_unit_schedule_tbl or one l_visit_schedule_tbl is expected)
593 ELSE
594 l_temp_sched_type := L_EVENT_NOTHING;
595 END IF;
596 */
597
598 l_temp_sched_type := L_EVENT_MULT_MAINTOP;
599 l_temp_start_time := l_start_time;
600 l_temp_end_time := l_end_time;
601 END IF;
602
603 -- Else, there is something wrong
604 ELSE
605 l_temp_sched_type := L_EVENT_NOTHING;
606 END IF;
607
608 -- 5. Insert record into global temporary table ahl_srch_unit_schedules
609
610 SELECT NVL(MAX(SRCH_UNIT_SCHEDULE_ID), 0) + 1 INTO l_temp_srch_sched_id FROM AHL_SRCH_UNIT_SCHEDULES;
611
612 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
613 THEN
614 fnd_log.string
615 (
616 fnd_log.level_statement,
617 L_DEBUG_MODULE,
618 '[l_temp_srch_sched_id='||TO_CHAR(l_temp_srch_sched_id)||'][l_temp_sched_type='||l_temp_sched_type||'][l_unit_config_id='||TO_CHAR(l_unit_config_id)||'][l_temp_unit_sched_id='||TO_CHAR(l_temp_unit_sched_id)||']'||
619 '[l_temp_visit_id='||TO_CHAR(l_temp_visit_id)||'][l_temp_visit_name='||l_temp_visit_name||'][l_temp_dept_id='||TO_CHAR(l_temp_dept_id)||'][l_temp_org_id='||TO_CHAR(l_temp_org_id)||']'||
620 '[l_temp_start_time='||TO_CHAR(l_temp_start_time, 'DD-MM-YYYY HH24:MI:SS')||'][l_temp_end_time='||TO_CHAR(l_temp_end_time, 'DD-MM-YYYY HH24:MI:SS')||']'||
621 '[l_temp_visit_status='||l_temp_visit_status||'][l_temp_visit_is_org_valid='||l_temp_visit_is_org_valid||']'
622 );
623 END IF;
624
625 INSERT INTO AHL_SRCH_UNIT_SCHEDULES
626 (
627 SRCH_UNIT_SCHEDULE_ID,
628 UNIT_CONFIG_HEADER_ID,
629 UNIT_SCHEDULE_TYPE,
630 UNIT_SCHEDULE_ID,
631 VISIT_ID,
632 VISIT_STATUS_CODE,
633 VISIT_IS_ORG_VALID,
634 VISIT_NAME,
635 START_TIME,
636 END_TIME,
637 DEPARTMENT_ID,
638 ORGANIZATION_ID
639 )
640 VALUES
641 (
642 l_temp_srch_sched_id,
643 l_unit_config_id,
644 l_temp_sched_type,
645 l_temp_unit_sched_id,
646 l_temp_visit_id,
647 l_temp_visit_status,
648 l_temp_visit_is_org_valid,
649 l_temp_visit_name,
650 l_temp_start_time,
651 l_temp_end_time,
652 l_temp_dept_id,
653 l_temp_org_id
654 );
655
656 -- iv. Populate the return parameters for this record in l_srch_unit_schedule
657 l_srch_unit_schedule.result_row_num := l_row_idx;
658 l_srch_unit_schedule.result_col_num := l_col_idx;
659 l_srch_unit_schedule.unit_name := l_unit_config_name;
660 l_srch_unit_schedule.unit_config_header_id := l_unit_config_id;
661 l_srch_unit_schedule.schedule_id := l_temp_srch_sched_id;
662 l_srch_unit_schedule.schedule_type := l_temp_sched_type;
663
664 -- v. Add l_srch_unit_schedule record to x_unit_schedules_results output table
665 x_unit_schedules_results(l_row_idx * L_MAX_TIME_SPANS + l_col_idx) := l_srch_unit_schedule;
666
667 -- Actual code logic for each time span ends here
668
669 -- Initialize start date time for the next time span = end date time of current time span
670 l_start_time := l_end_time;
671 END LOOP;
672 -- End of L_MAX_TIME_SPAN time blocks loops
673 END LOOP;
674 -- End of l_unit_config_id
675
676 -- API body ends here
677
678 -- Log API exit point
679 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
680 THEN
681 fnd_log.string
682 (
683 fnd_log.level_procedure,
684 L_DEBUG_MODULE||'.end',
685 'At the end of PLSQL procedure'
686 );
687 END IF;
688
689 -- Check Error Message stack.
690 x_msg_count := FND_MSG_PUB.count_msg;
691 IF x_msg_count > 0
692 THEN
693 RAISE FND_API.G_EXC_ERROR;
694 END IF;
695
696 -- Commit by default
697 COMMIT WORK;
698
699 -- Standard call to get message count and if count is 1, get message info
700 FND_MSG_PUB.count_and_get
701 (
702 p_count => x_msg_count,
703 p_data => x_msg_data,
704 p_encoded => FND_API.G_FALSE
705 );
706
707 EXCEPTION
708 WHEN FND_API.G_EXC_ERROR THEN
709 x_return_status := FND_API.G_RET_STS_ERROR;
710 Rollback to Search_Unit_Schedules_SP;
711 FND_MSG_PUB.count_and_get
712 (
713 p_count => x_msg_count,
714 p_data => x_msg_data,
715 p_encoded => FND_API.G_FALSE
716 );
717
718 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
719 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
720 Rollback to Search_Unit_Schedules_SP;
721 FND_MSG_PUB.count_and_get
722 (
723 p_count => x_msg_count,
724 p_data => x_msg_data,
725 p_encoded => FND_API.G_FALSE
726 );
727
728 WHEN OTHERS THEN
729 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
730 Rollback to Search_Unit_Schedules_SP;
731 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
732 THEN
733 FND_MSG_PUB.add_exc_msg
734 (
735 p_pkg_name => G_PKG_NAME,
736 p_procedure_name => 'Search_Unit_Schedules',
737 p_error_text => SUBSTR(SQLERRM,1,240)
738 );
739 END IF;
740 FND_MSG_PUB.count_and_get
741 (
742 p_count => x_msg_count,
743 p_data => x_msg_data,
744 p_encoded => FND_API.G_FALSE
745 );
746 END Search_Unit_Schedules;
747 ---------------------------------------
748 -- Spec Procedure Get_MEvent_Details --
749 ---------------------------------------
750 PROCEDURE Get_MEvent_Details
751 (
752 p_api_version IN NUMBER,
753 p_module_type IN VARCHAR2,
754 x_return_status OUT NOCOPY VARCHAR2,
755 x_msg_count OUT NOCOPY NUMBER,
756 x_msg_data OUT NOCOPY VARCHAR2,
757 p_x_MEvent_Header_Rec IN OUT NOCOPY MEvent_Header_Rec_Type,
758 x_Unit_Schedule_tbl OUT NOCOPY Unit_Schedule_Tbl_Type,
759 x_Visit_Schedule_tbl OUT NOCOPY Visit_Schedule_Tbl_Type
760 )
761 IS
762 -- 1. Declare local variables
763 l_api_name CONSTANT VARCHAR2(30) := 'Get_MEvent_Details';
764 l_api_version CONSTANT NUMBER := 1.0;
765 L_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
766
767 -- Variable to hold all the events in the time slot
768 l_event_schedule_tbl AHL_UA_COMMON_PVT.Event_schedule_Tbl_type;
769 -- to store the flight record
770 l_flight_schedule_rec AHL_UA_FLIGHT_SCHEDULES_PVT.Flight_Schedule_Rec_Type;
771 -- to store fetched visit record
772 l_visit_rec AHL_VWP_VISITS_PVT.Visit_Rec_Type;
773 -- to store previous and current record information
774 l_prev_event_type VARCHAR2(12);
775 l_prev_unit_schedule_rec AHL_UA_UNIT_SCHEDULES_PVT.Unit_Schedule_rec_type;
776 l_prev_visit_schedule_rec AHL_UA_UNIT_SCHEDULES_PVT. Visit_Schedule_rec_type;
777 l_curr_event_type VARCHAR2(12);
778 l_curr_unit_schedule_rec AHL_UA_UNIT_SCHEDULES_PVT.Unit_Schedule_rec_type;
779 l_curr_visit_schedule_rec AHL_UA_UNIT_SCHEDULES_PVT.Visit_Schedule_rec_type;
780
781 -- to fetcch information related to unit.. to be shown on UI as context information
782 CURSOR context_info_csr (p_unit_config_header_id IN NUMBER)
783 IS
784 -- Bug No #4916304: APPSPERF fixes {priyan}
785 SELECT
786 U.NAME,
787 I.CONCATENATED_SEGMENTS ITEM_NUMBER,
788 C.SERIAL_NUMBER
789 FROM
790 AHL_UNIT_CONFIG_HEADERS U,
791 CSI_ITEM_INSTANCES C,
792 MTL_SYSTEM_ITEMS_KFV I
793 WHERE
794 U.CSI_ITEM_INSTANCE_ID = C.INSTANCE_ID
795 AND C.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
796 AND C.LAST_VLD_ORGANIZATION_ID = I.ORGANIZATION_ID
797 AND UNIT_CONFIG_HEADER_ID = P_UNIT_CONFIG_HEADER_ID;
798 /*
799 SELECT name, item_number, serial_number
800 FROM ahl_unit_header_details_v
801 WHERE unit_config_header_id = p_unit_config_header_id;
802 */
803
804 -- to get a visit record information
805 CURSOR visit_schedule_info_csr (p_visit_id IN NUMBER)
806 IS
807 -- Bug No #4916304: APPSPERF fixes {priyan}
808 SELECT
809 AVTB.VISIT_NUMBER,
810 AVTT.VISIT_NAME,
811 AVTB.ORGANIZATION_ID ORG_ID,
812 MP.ORGANIZATION_CODE ORG_CODE,
813 AVTB.DEPARTMENT_ID DEPT_ID,
814 BDPT.DEPARTMENT_CODE DEPT_CODE,
815 FLVT.MEANING VISIT_TYPE_MEAN,
816 AVTB.STATUS_CODE,
817 FNVS.MEANING STATUS_MEAN
818 FROM
819 AHL_VISITS_B AVTB,
820 AHL_VISITS_TL AVTT,
821 MTL_PARAMETERS MP,
822 BOM_DEPARTMENTS BDPT,
823 FND_LOOKUP_VALUES_VL FLVT,
824 FND_LOOKUP_VALUES_VL FNVS
825 WHERE
826 AVTB.VISIT_ID = P_VISIT_ID
827 AND AVTB.VISIT_ID = AVTT.VISIT_ID
828 AND AVTT.LANGUAGE = USERENV('LANG')
829 AND AVTB.ORGANIZATION_ID = MP.ORGANIZATION_ID(+)
830 AND AVTB.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+)
831 AND FLVT.LOOKUP_TYPE(+) = 'AHL_PLANNING_VISIT_TYPE'
832 AND FLVT.LOOKUP_CODE(+) = AVTB.VISIT_TYPE_CODE
833 AND FNVS.LOOKUP_TYPE(+) = 'AHL_VWP_VISIT_STATUS'
834 AND FNVS.LOOKUP_CODE(+) = AVTB.STATUS_CODE;
835 /*
836 SELECT visit_number, visit_name, org_id, org_code, dept_id, dept_code,
837 visit_type_mean, status_code, status_mean --,unit_schedule_id
838 FROM ahl_visit_details_v
839 WHERE visit_id = p_visit_id;
840 */
841
842 -- temporary variable to call other APIs
843 l_is_conflict VARCHAR2(1);
844 l_is_org_in_user_ou VARCHAR2(1);
845
846
847 -- To search a flight record and hold search results
848 l_flight_search_rec AHL_UA_FLIGHT_SCHEDULES_PUB.FLIGHT_SEARCH_REC_TYPE;
849 l_flight_schedules_tbl AHL_UA_FLIGHT_SCHEDULES_PVT.FLIGHT_SCHEDULES_TBL_TYPE;
850
851 -- early exit flag for module type 'US' processing
852 l_early_exit_flag BOOLEAN;
853
854
855 BEGIN
856
857 -- Standard call to check for call compatibility
858 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
859 THEN
860 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
861 END IF;
862
863 -- Initialize message list by default
864 FND_MSG_PUB.Initialize;
865
866 -- Initialize API return status to success
867 x_return_status := FND_API.G_RET_STS_SUCCESS;
868
869 -- Log API entry point
870 ----dbms_output.put_line('At the start of PLSQL procedure');
871 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
872 THEN
873 fnd_log.string
874 (
875 fnd_log.level_procedure,
876 L_DEBUG_MODULE||'.begin',
877 'At the start of PLSQL procedure'
878 );
879 END IF;
880 -- API body starts here
881 -- log input values
882 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
883 fnd_log.string
884 (
885 fnd_log.level_statement,
886 L_DEBUG_MODULE,
887 'p_x_MEvent_Header_Rec.UNIT_CONFIG_HEADER_ID : ' || p_x_MEvent_Header_Rec.UNIT_CONFIG_HEADER_ID || ' ' ||
888 'p_x_MEvent_Header_Rec.START_TIME : ' || p_x_MEvent_Header_Rec.START_TIME || ' ' ||
889 'p_x_MEvent_Header_Rec.END_TIME : ' || p_x_MEvent_Header_Rec.END_TIME
890 );
891 END IF;
892 -- validate mandatory input values
893 IF(p_x_MEvent_Header_Rec.UNIT_CONFIG_HEADER_ID IS NULL OR
894 p_x_MEvent_Header_Rec.START_TIME IS NULL OR
895 p_x_MEvent_Header_Rec.END_TIME IS NULL OR
896 p_x_MEvent_Header_Rec.END_TIME <= p_x_MEvent_Header_Rec.START_TIME)THEN
897 ----dbms_output.put_line('Unexpected error : Invalid Input');
898 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
899 fnd_log.string
900 (
901 fnd_log.level_error,
902 L_DEBUG_MODULE,
903 'Unexpected error : Invalid Input'
904 );
905 END IF;
906 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
907 END IF;
908
909 --initialize profile values
910 IF(G_DEPT_CONFLICT_PROF = 'Y')THEN
911 G_DEPT_CONFLICT := TRUE;
912 ELSE
913 G_DEPT_CONFLICT := FALSE;
914 END IF;
915
916
917
918 -- populate header record with unit information
919 -- needs to be executed when module type is OAF and not US
920 IF(p_module_type <> 'US') THEN
921 OPEN context_info_csr (p_x_MEvent_Header_Rec.UNIT_CONFIG_HEADER_ID);
922 FETCH context_info_csr INTO p_x_MEvent_Header_Rec.UNIT_NAME, p_x_MEvent_Header_Rec.ITEM_NUMBER,
923 p_x_MEvent_Header_Rec.SERIAL_NUMBER;
924 IF(context_info_csr%NOTFOUND)THEN
925 ----dbms_output.put_line('Unexpected error : Unit not found');
926 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
927 fnd_log.string
928 (
929 fnd_log.level_error,
930 L_DEBUG_MODULE,
931 'Unexpected error : unit not found'
932 );
933 END IF;
934 CLOSE context_info_csr;
935 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
936 END IF;
937 CLOSE context_info_csr;
938 END IF;
939 -- initialize header level conflict and Maintenance Opportunity related flags with default values
940 p_x_MEvent_Header_Rec.HAS_CONFLICT := FND_API.G_FALSE;
941 p_x_MEvent_Header_Rec.HAS_MOPPORTUNITY := FND_API.G_FALSE;
942 ----dbms_output.put_line('Calling AHL_UA_COMMON_PVT.get_all_events');
943 -- Get all the events in the time slot
944 AHL_UA_COMMON_PVT.get_all_events(
945 p_api_version => 1.0 ,
946 x_return_status => x_return_status,
947 x_msg_count => x_msg_count,
948 x_msg_data => x_msg_data,
949 p_unit_config_id => p_x_MEvent_Header_Rec.UNIT_CONFIG_HEADER_ID,
950 p_start_date_time => p_x_MEvent_Header_Rec.START_TIME,
951 p_end_date_time => p_x_MEvent_Header_Rec.END_TIME,
952 p_use_actuals => G_USE_ACTUALS,
953 x_event_schedules => l_event_schedule_tbl);
954 -- Check for API errors
955 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
956 ----dbms_output.put_line('Unexpected error : AHL_UA_COMMON_PVT.get_all_events returned errors');
957 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
958 fnd_log.string
959 (
960 fnd_log.level_error,
961 L_DEBUG_MODULE,
962 'Unexpected error : AHL_UA_COMMON_PVT.get_all_events returned errors'
963 );
964 END IF;
965 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
966 END IF;
967
968 -- Set the event count
969 IF(l_event_schedule_tbl IS NULL)THEN
970 p_x_MEvent_Header_Rec.EVENT_COUNT := 0;
971 ELSE
972 p_x_MEvent_Header_Rec.EVENT_COUNT := l_event_schedule_tbl.COUNT;
973 END IF;
974
975 -- Get previous flight information in all cases
976 AHL_UA_COMMON_PVT.Get_Prec_Flight_Info
977 (
978 p_api_version => 1.0,
979 x_return_status => x_return_status,
980 x_msg_count => x_msg_count,
981 x_msg_data => x_msg_data,
982 p_unit_config_id => p_x_MEvent_Header_Rec.UNIT_CONFIG_HEADER_ID,
983 p_start_date_time => p_x_MEvent_Header_Rec.START_TIME,
984 p_use_actuals => G_USE_ACTUALS,
985 x_prec_flight_schedule => l_flight_schedule_rec,
986 x_is_conflict => l_is_conflict
987
988 );
989 -- Check for API errors
990 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
991 ----dbms_output.put_line('Unexpected error : AHL_UA_COMMON_PVT.Get_Prec_Flight_Info returned errors');
992 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
993 fnd_log.string
994 (
995 fnd_log.level_error,
996 L_DEBUG_MODULE,
997 'Unexpected error : AHL_UA_COMMON_PVT.Get_Prec_Flight_Info returned errors'
998 );
999 END IF;
1000 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1001 END IF;
1002
1003 -- assign previous flight info if available
1004 IF(l_flight_schedule_rec.UNIT_SCHEDULE_ID IS NOT NULL)THEN
1005 l_prev_event_type := NULL;
1006 l_prev_unit_schedule_rec.UNIT_SCHEDULE_ID := l_flight_schedule_rec.UNIT_SCHEDULE_ID;
1007 l_prev_unit_schedule_rec.FLIGHT_NUMBER := l_flight_schedule_rec.FLIGHT_NUMBER;
1008 END IF;
1009
1010 -- For first event(if it starts on or after start time of window) or if the event count is zero
1011 IF(
1012 p_x_MEvent_Header_Rec.EVENT_COUNT = 0 OR
1013 (p_module_type <> 'US' and l_event_schedule_tbl(l_event_schedule_tbl.FIRST).EVENT_START_TIME >= p_x_MEvent_Header_Rec.START_TIME) OR
1014 (p_module_type = 'US' and l_event_schedule_tbl(l_event_schedule_tbl.FIRST).EVENT_START_TIME > p_x_MEvent_Header_Rec.START_TIME)
1015 ) THEN
1016 AHL_UA_COMMON_PVT.Get_Prec_Event_Info
1017 (
1018 p_api_version => 1.0,
1019 x_return_status => x_return_status,
1020 x_msg_count => x_msg_count,
1021 x_msg_data => x_msg_data,
1022 p_unit_config_id => p_x_MEvent_Header_Rec.UNIT_CONFIG_HEADER_ID,
1023 p_start_date_time => p_x_MEvent_Header_Rec.START_TIME,
1024 p_use_actuals => G_USE_ACTUALS,
1025 x_prec_flight_schedule => l_flight_schedule_rec,
1026 x_prec_visit => l_visit_rec,
1027 x_is_conflict => l_is_conflict,
1028 x_is_org_in_user_ou => l_is_org_in_user_ou
1029
1030 );
1031 -- Check for API errors
1032 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1033 ----dbms_output.put_line('Unexpected error : AHL_UA_COMMON_PVT.Get_Prec_Visit_Info returned errors');
1034 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
1035 fnd_log.string
1036 (
1037 fnd_log.level_error,
1038 L_DEBUG_MODULE,
1039 'Unexpected error : AHL_UA_COMMON_PVT.Get_Prec_Event_Info returned errors'
1040 );
1041 END IF;
1042 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1043 END IF;
1044 -- determine type of previous event
1045 IF(l_flight_schedule_rec.unit_schedule_id IS NOT NULL)THEN
1046 l_prev_event_type := G_EVENT_TYPE_FLIGHT;
1047 ELSIF(l_visit_rec.visit_id IS NOT NULL)THEN
1048 l_prev_event_type := G_EVENT_TYPE_VISIT;
1049 END IF;
1050 -- populate prior event info into local variable for previous event
1051 IF(l_prev_event_type = G_EVENT_TYPE_FLIGHT)THEN
1052 ----dbms_output.put_line('calling populate_unit_schedule_rec for pre window event');
1053 populate_unit_schedule_rec(
1054 p_unit_flight_schedule_rec => l_flight_schedule_rec,
1055 p_prev_event_type => NULL,
1056 p_window_event => FALSE,
1057 p_prev_unit_schedule_rec => NULL,
1058 p_prev_visit_schedule_rec => NULL,
1059 p_x_MEvent_Header_Rec => p_x_MEvent_Header_Rec,
1060 p_x_Unit_Schedule_tbl => x_Unit_Schedule_tbl,
1061 p_x_Visit_Schedule_tbl => x_Visit_Schedule_tbl,
1062 x_unit_schedule_rec => l_prev_unit_schedule_rec
1063 );
1064
1065 ELSIF(l_prev_event_type = G_EVENT_TYPE_VISIT)THEN
1066 ----dbms_output.put_line('calling populate_visit_schedule_rec for pre window event');
1067 OPEN visit_schedule_info_csr(l_visit_rec.visit_id);
1068 FETCH visit_schedule_info_csr INTO l_visit_rec.visit_number,
1069 l_visit_rec.visit_name,
1070 l_visit_rec.organization_id,
1071 l_visit_rec.org_name,
1072 l_visit_rec.department_id,
1073 l_visit_rec.dept_name,
1074 l_visit_rec.visit_type_name,
1075 l_visit_rec.status_code,
1076 l_visit_rec.status_name;--,unit_schedule_id
1077 CLOSE visit_schedule_info_csr;
1078 populate_visit_schedule_rec(
1079 p_visit_rec => l_visit_rec,
1080 p_prev_event_type => NULL,
1081 p_window_event => FALSE,
1082 p_prev_unit_schedule_rec => NULL,
1083 p_prev_visit_schedule_rec => NULL,
1084 p_x_MEvent_Header_Rec => p_x_MEvent_Header_Rec,
1085 p_x_Unit_Schedule_tbl => x_Unit_Schedule_tbl,
1086 p_x_Visit_Schedule_tbl => x_Visit_Schedule_tbl,
1087 x_Visit_Schedule_Rec => l_prev_visit_schedule_rec
1088 );
1089 l_prev_visit_schedule_rec.PREV_UNIT_SCHEDULE_ID := l_prev_unit_schedule_rec.UNIT_SCHEDULE_ID ;
1090 l_prev_visit_schedule_rec.PREV_FLIGHT_NUMBER := l_prev_unit_schedule_rec.FLIGHT_NUMBER;
1091 END IF;
1092 END IF;
1093 -- For all events in the window
1094 ----dbms_output.put_line('processing window events');
1095 IF(p_x_MEvent_Header_Rec.EVENT_COUNT > 0)THEN
1096 ----dbms_output.put_line('p_x_MEvent_Header_Rec.EVENT_COUNT : ' || p_x_MEvent_Header_Rec.EVENT_COUNT);
1097 FOR i IN l_event_schedule_tbl.FIRST..l_event_schedule_tbl.LAST LOOP
1098 IF(l_event_schedule_tbl(i).EVENT_TYPE = G_EVENT_TYPE_FLIGHT)THEN
1099 l_curr_event_type := G_EVENT_TYPE_FLIGHT;
1100 l_flight_search_rec.UNIT_SCHEDULE_ID := l_event_schedule_tbl(i).EVENT_ID;
1101 ----dbms_output.put_line('calling AHL_UA_FLIGHT_SCHEDULES_PUB.Get_Flight_Schedule_Details for UNIT_SCHEDULE_ID : ' || l_flight_search_rec.UNIT_SCHEDULE_ID);
1102 AHL_UA_FLIGHT_SCHEDULES_PUB.Get_Flight_Schedule_Details(
1103 p_api_version => 1.0,
1104 p_init_msg_list => FND_API.G_TRUE,
1105 p_commit => FND_API.G_FALSE,
1106 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1107 p_default => FND_API.G_FALSE,
1108 p_module_type => 'US',
1109 x_return_status => x_return_status,
1110 x_msg_count => x_msg_count,
1111 x_msg_data => x_msg_data,
1112 p_flight_search_rec => l_flight_search_rec,
1113 x_flight_schedules_tbl => l_flight_schedules_tbl
1114 );
1115 -- Check for API errors
1116 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1117 ----dbms_output.put_line('Unexpected error : AHL_UA_FLIGHT_SCHEDULES_PUB.Get_Flight_Schedule_Details returned error');
1118 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
1119 fnd_log.string
1120 (
1121 fnd_log.level_error,
1122 L_DEBUG_MODULE,
1123 'Unexpected error : AHL_UA_FLIGHT_SCHEDULES_PUB.Get_Flight_Schedule_Details returned error'
1124 );
1125 END IF;
1126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1127 END IF;
1128 l_flight_schedule_rec := l_flight_schedules_tbl(l_flight_schedules_tbl.FIRST);
1129 ----dbms_output.put_line('calling populate_unit_schedule_rec for window event');
1130 populate_unit_schedule_rec(
1131 p_unit_flight_schedule_rec => l_flight_schedule_rec,
1132 p_prev_event_type => l_prev_event_type,
1133 p_window_event => TRUE,
1134 p_prev_unit_schedule_rec => l_prev_unit_schedule_rec,
1135 p_prev_visit_schedule_rec => l_prev_visit_schedule_rec,
1136 p_x_MEvent_Header_Rec => p_x_MEvent_Header_Rec,
1137 p_x_Unit_Schedule_tbl => x_Unit_Schedule_tbl,
1138 p_x_Visit_Schedule_tbl => x_Visit_Schedule_tbl,
1139 x_Unit_Schedule_Rec => l_curr_unit_schedule_rec
1140 );
1141 l_prev_event_type := l_curr_event_type;
1142 l_prev_unit_schedule_rec := l_curr_unit_schedule_rec;
1143 ELSIF(l_event_schedule_tbl(i).EVENT_TYPE = G_EVENT_TYPE_VISIT)THEN
1144 l_curr_event_type := G_EVENT_TYPE_VISIT;
1145 ----dbms_output.put_line('Getting visit information for visit_id : ' || l_event_schedule_tbl(i).EVENT_ID);
1146 OPEN visit_schedule_info_csr(l_event_schedule_tbl(i).EVENT_ID);
1147 FETCH visit_schedule_info_csr INTO l_visit_rec.visit_number,
1148 l_visit_rec.visit_name,
1149 l_visit_rec.organization_id,
1150 l_visit_rec.org_name,
1151 l_visit_rec.department_id,
1152 l_visit_rec.dept_name,
1153 l_visit_rec.visit_type_name,
1154 l_visit_rec.status_code,
1155 l_visit_rec.status_name;--,unit_schedule_id
1156 l_visit_rec.visit_id := l_event_schedule_tbl(i).EVENT_ID;
1157 l_visit_rec.start_date := l_event_schedule_tbl(i).EVENT_START_TIME;
1158 l_visit_rec.end_date := l_event_schedule_tbl(i).EVENT_END_TIME;
1159 ----dbms_output.put_line('calling populate_visit_schedule_rec for window event');
1160 populate_visit_schedule_rec(
1161 p_visit_rec => l_visit_rec,
1162 p_prev_event_type => l_prev_event_type,
1163 p_window_event => TRUE,
1164 p_prev_unit_schedule_rec => l_prev_unit_schedule_rec,
1165 p_prev_visit_schedule_rec => l_prev_visit_schedule_rec,
1166 p_x_MEvent_Header_Rec => p_x_MEvent_Header_Rec,
1167 p_x_Unit_Schedule_tbl => x_Unit_Schedule_tbl,
1168 p_x_Visit_Schedule_tbl => x_Visit_Schedule_tbl,
1169 x_Visit_Schedule_Rec => l_curr_visit_schedule_rec
1170 );
1171 CLOSE visit_schedule_info_csr;
1172 l_prev_event_type := l_curr_event_type;
1173 l_prev_visit_schedule_rec := l_curr_visit_schedule_rec;
1174 END IF;
1175 -- if module type is US then if there is MO and Conflcit.. return without further processing to save resources.
1176 IF(p_module_type = 'US' AND p_x_MEvent_Header_Rec.HAS_CONFLICT = FND_API.G_TRUE AND p_x_MEvent_Header_Rec.HAS_MOPPORTUNITY = FND_API.G_TRUE)THEN
1177 l_early_exit_flag := TRUE;
1178 EXIT;
1179 END IF;
1180 END LOOP;
1181 END IF;
1182 -- For search unit schedule when already determined that there are conflcits and maintenance opportunities
1183 IF(l_early_exit_flag)THEN
1184 ----dbms_output.put_line('exiting early');
1185 RETURN;
1186 END IF;
1187
1188 -- For last event(if it ends before end time of window) or if the event count is zero
1189 IF(p_x_MEvent_Header_Rec.EVENT_COUNT = 0 OR
1190 NVL(l_event_schedule_tbl(l_event_schedule_tbl.LAST).EVENT_END_TIME,
1191 l_event_schedule_tbl(l_event_schedule_tbl.LAST).EVENT_START_TIME + 1/1440 )< p_x_MEvent_Header_Rec.END_TIME) THEN
1192 -- populate previous event information
1193 -- get previous flight information
1194 AHL_UA_COMMON_PVT.Get_Succ_Event_Info
1195 (
1196 p_api_version => 1.0,
1197 x_return_status => x_return_status,
1198 x_msg_count => x_msg_count,
1199 x_msg_data => x_msg_data,
1200 p_unit_config_id => p_x_MEvent_Header_Rec.UNIT_CONFIG_HEADER_ID,
1201 p_end_date_time => p_x_MEvent_Header_Rec.END_TIME,
1202 p_use_actuals => G_USE_ACTUALS,
1203 x_succ_flight_schedule => l_flight_schedule_rec,
1204 x_succ_visit => l_visit_rec,
1205 x_is_conflict => l_is_conflict,
1206 x_is_org_in_user_ou => l_is_org_in_user_ou
1207
1208 );
1209 -- Check for API errors
1210 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1211 ----dbms_output.put_line('Unexpected error : AHL_UA_COMMON_PVT.Get_Succ_Event_Info returned errors');
1212 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
1213 fnd_log.string
1214 (
1215 fnd_log.level_error,
1216 L_DEBUG_MODULE,
1217 'Unexpected error : AHL_UA_COMMON_PVT.Get_Succ_Event_Info returned errors'
1218 );
1219 END IF;
1220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1221 END IF;
1222 -- determine type of last event
1223 IF(l_flight_schedule_rec.unit_schedule_id IS NOT NULL)THEN
1224 l_curr_event_type := G_EVENT_TYPE_FLIGHT;
1225 ELSIF(l_visit_rec.visit_id IS NOT NULL)THEN
1226 l_curr_event_type := G_EVENT_TYPE_VISIT;
1227 ELSE
1228 l_curr_event_type := NULL;--no last event found
1229 END IF;
1230 -- populate prior event info into local variable for previous event
1231 IF(l_curr_event_type = G_EVENT_TYPE_FLIGHT)THEN
1232 ----dbms_output.put_line('calling populate_unit_schedule_rec for post window event');
1233 populate_unit_schedule_rec(
1234 p_unit_flight_schedule_rec => l_flight_schedule_rec,
1235 p_prev_event_type => l_prev_event_type,
1236 p_window_event => FALSE,
1237 p_prev_unit_schedule_rec => l_prev_unit_schedule_rec,
1238 p_prev_visit_schedule_rec => l_prev_visit_schedule_rec,
1239 p_x_MEvent_Header_Rec => p_x_MEvent_Header_Rec,
1240 p_x_Unit_Schedule_tbl => x_Unit_Schedule_tbl,
1241 p_x_Visit_Schedule_tbl => x_Visit_Schedule_tbl,
1242 x_Unit_Schedule_Rec => l_curr_unit_schedule_rec
1243 );
1244 ELSIF(l_curr_event_type = G_EVENT_TYPE_VISIT)THEN
1245 ----dbms_output.put_line('calling populate_visit_schedule_rec for post window event');
1246 OPEN visit_schedule_info_csr(l_visit_rec.visit_id);
1247 FETCH visit_schedule_info_csr INTO l_visit_rec.visit_number,
1248 l_visit_rec.visit_name,
1249 l_visit_rec.organization_id,
1250 l_visit_rec.org_name,
1251 l_visit_rec.department_id,
1252 l_visit_rec.dept_name,
1253 l_visit_rec.visit_type_name,
1254 l_visit_rec.status_code,
1255 l_visit_rec.status_name;--,unit_schedule_id
1256 CLOSE visit_schedule_info_csr;
1257 populate_visit_schedule_rec(
1258 p_visit_rec => l_visit_rec,
1259 p_prev_event_type => l_prev_event_type,
1260 p_window_event => FALSE,
1261 p_prev_unit_schedule_rec => l_prev_unit_schedule_rec,
1262 p_prev_visit_schedule_rec => l_prev_visit_schedule_rec,
1263 p_x_MEvent_Header_Rec => p_x_MEvent_Header_Rec,
1264 p_x_Unit_Schedule_tbl => x_Unit_Schedule_tbl,
1265 p_x_Visit_Schedule_tbl => x_Visit_Schedule_tbl,
1266 x_Visit_Schedule_Rec => l_curr_visit_schedule_rec
1267 );
1268 ELSE -- when no post window event is found
1269 -- a dummy record needs to be added with MO flag as FND_API.G_TRUE if there is flight for unit
1270 -- either can add it to flight or visit, lets add where perious event lies
1271 --Adithya added the end_date > sysdate condition for FP bug# 6447447.
1272 IF(l_prev_event_type IS NOT NULL AND p_x_MEvent_Header_Rec.END_TIME > SYSDATE)THEN
1273 IF(l_prev_event_type = G_EVENT_TYPE_FLIGHT)THEN
1274 l_curr_unit_schedule_rec := NULL;--initilaize it
1275 l_curr_unit_schedule_rec.PREV_EVENT_ID := l_prev_unit_schedule_rec.UNIT_SCHEDULE_ID;
1276 l_curr_unit_schedule_rec.PREV_EVENT_TYPE := l_prev_event_type;
1277 l_curr_unit_schedule_rec.PREV_EVENT_ORG_ID := l_prev_unit_schedule_rec.ARRIVAL_ORG_ID;
1278 l_curr_Unit_Schedule_Rec.IS_PREV_ORG_VALID := l_prev_unit_schedule_rec.IS_ORG_VALID;
1279 l_curr_unit_schedule_rec.PREV_EVENT_ORG_NAME := l_prev_unit_schedule_rec.ARRIVAL_ORG_NAME;
1280 l_curr_unit_schedule_rec.PREV_EVENT_DEP_ID := l_prev_unit_schedule_rec.ARRIVAL_DEP_ID;
1281 l_curr_unit_schedule_rec.PRVE_EVENT_DEP_NAME := l_prev_unit_schedule_rec.ARRIVAL_DEP_NAME;
1282 l_curr_unit_schedule_rec.PREV_EVENT_END_TIME := l_prev_unit_schedule_rec.ARRIVAL_TIME;
1283 l_curr_unit_schedule_rec.PREV_UNIT_SCHEDULE_ID := l_prev_unit_schedule_rec.UNIT_SCHEDULE_ID;
1284 l_curr_unit_schedule_rec.PREV_FLIGHT_NUMBER := l_prev_unit_schedule_rec.FLIGHT_NUMBER;
1285 l_curr_unit_schedule_rec.HAS_MOPPORTUNITY := FND_API.G_TRUE;
1286 l_curr_unit_schedule_rec.HAS_CONFLICT := FND_API.G_FALSE;
1287 l_prev_unit_schedule_rec.EVENT_SEQ := NVL(l_prev_unit_schedule_rec.EVENT_SEQ,1);
1288 l_curr_unit_schedule_rec.EVENT_SEQ := l_prev_unit_schedule_rec.EVENT_SEQ + 1;
1289 -- add pre-window event if event count = 0
1290 IF(p_x_MEvent_Header_Rec.EVENT_COUNT = 0)THEN
1291 x_Unit_Schedule_tbl(l_prev_unit_schedule_rec.EVENT_SEQ -1 ) := l_prev_unit_schedule_rec;
1292 END IF;
1293 -- add current dummy event
1294 x_Unit_Schedule_tbl(l_curr_unit_schedule_rec.EVENT_SEQ -1) := l_curr_unit_schedule_rec;
1295 -- update header to tell that there is a MO
1296 p_x_MEvent_Header_Rec.HAS_MOPPORTUNITY := FND_API.G_TRUE;
1297
1298 --Adithya modified the condition below as part of fix for FP bug# 6447447
1299 -- Even if there are no flights but visits exist in the past, MO should be flagged
1300 ELSIF(l_prev_event_type = G_EVENT_TYPE_VISIT) --AND l_prev_visit_schedule_rec.PREV_UNIT_SCHEDULE_ID IS NOT NULL)
1301 THEN
1302 l_curr_visit_schedule_rec := NULL;--initilaize it
1303 l_curr_visit_schedule_rec.PREV_EVENT_ID := l_prev_visit_schedule_rec.VISIT_ID;
1304 l_curr_visit_schedule_rec.PREV_EVENT_TYPE := l_prev_event_type;
1305 l_curr_visit_schedule_rec.PREV_EVENT_ORG_ID := l_prev_visit_schedule_rec.VISIT_ORG_ID;
1306 l_curr_visit_Schedule_Rec.IS_PREV_ORG_VALID := l_prev_visit_schedule_rec.IS_ORG_VALID;
1307 l_curr_visit_schedule_rec.PREV_EVENT_ORG_NAME := l_prev_visit_schedule_rec.VISIT_ORG_NAME;
1308 l_curr_visit_schedule_rec.PREV_EVENT_DEP_ID := l_prev_visit_schedule_rec.VISIT_DEP_ID;
1309 l_curr_visit_schedule_rec.PRVE_EVENT_DEP_NAME := l_prev_visit_schedule_rec.VISIT_DEP_NAME;
1310 l_curr_visit_schedule_rec.PREV_EVENT_END_TIME :=
1311 NVL(l_prev_visit_schedule_rec.END_TIME, l_prev_visit_schedule_rec.START_TIME + 1/1440);
1312 l_curr_visit_schedule_rec.PREV_UNIT_SCHEDULE_ID := l_prev_visit_schedule_rec.PREV_UNIT_SCHEDULE_ID;
1313 l_curr_visit_schedule_rec.PREV_FLIGHT_NUMBER := l_prev_visit_schedule_rec.PREV_FLIGHT_NUMBER;
1314 l_curr_visit_schedule_rec.HAS_MOPPORTUNITY := FND_API.G_TRUE;
1315 l_curr_visit_schedule_rec.HAS_CONFLICT := FND_API.G_FALSE;
1316 l_prev_visit_schedule_rec.EVENT_SEQ := NVL(l_prev_visit_schedule_rec.EVENT_SEQ,1);
1317 l_curr_visit_schedule_rec.EVENT_SEQ := l_prev_visit_schedule_rec.EVENT_SEQ + 1;
1318 -- add pre-window event if event count = 0
1319 IF(p_x_MEvent_Header_Rec.EVENT_COUNT = 0)THEN
1320 x_Visit_Schedule_tbl(l_prev_visit_schedule_rec.EVENT_SEQ - 1) := l_prev_visit_schedule_rec;
1321 END IF;
1322 -- add current event
1323 x_Visit_Schedule_tbl(l_curr_visit_schedule_rec.EVENT_SEQ -1) := l_curr_visit_schedule_rec;
1324 -- update header to tell that there is a MO
1325 p_x_MEvent_Header_Rec.HAS_MOPPORTUNITY := FND_API.G_TRUE;
1326 END IF;
1327
1328 --Adithya modified code for fixing FP bug# 6447447 -- Start --
1329 ELSIF ( p_x_MEvent_Header_Rec.END_TIME > SYSDATE )
1330 --if no events are found ( i.e. previous event id is null)
1331 THEN
1332 l_curr_visit_schedule_rec := NULL;--initilaize it
1333 l_curr_visit_schedule_rec.EVENT_SEQ := 1;
1334 l_curr_visit_schedule_rec.PREV_EVENT_END_TIME := sysdate;
1335 l_curr_visit_schedule_rec.HAS_MOPPORTUNITY := FND_API.G_TRUE;
1336 l_curr_visit_schedule_rec.HAS_CONFLICT := FND_API.G_FALSE;
1337 -- add current dummy event
1338 x_Visit_Schedule_tbl(0) := l_curr_visit_schedule_rec;
1339 -- update header to tell that there is a MO
1340 p_x_MEvent_Header_Rec.HAS_MOPPORTUNITY := FND_API.G_TRUE;
1341 --Adithya modified code for fixing FP bug# 6447447 -- END --
1342
1343 END IF;
1344 END IF;
1345 END IF;
1346
1347 ----dbms_output.put_line('returning after success');
1348 -- Log API exit point
1349 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1350 THEN
1351 fnd_log.string
1352 (
1353 fnd_log.level_procedure,
1354 L_DEBUG_MODULE||'.end',
1355 'At the end of PLSQL procedure'
1356 );
1357 END IF;
1358
1359 -- Check Error Message stack.
1360 x_msg_count := FND_MSG_PUB.count_msg;
1361 IF x_msg_count > 0
1362 THEN
1363 RAISE FND_API.G_EXC_ERROR;
1364 END IF;
1365
1366 -- Standard call to get message count and if count is 1, get message info
1367 FND_MSG_PUB.count_and_get
1368 (
1369 p_count => x_msg_count,
1370 p_data => x_msg_data,
1371 p_encoded => FND_API.G_FALSE
1372 );
1373
1374 EXCEPTION
1375 WHEN FND_API.G_EXC_ERROR THEN
1376 x_return_status := FND_API.G_RET_STS_ERROR;
1377 FND_MSG_PUB.count_and_get
1378 (
1379 p_count => x_msg_count,
1380 p_data => x_msg_data,
1381 p_encoded => FND_API.G_FALSE
1382 );
1383
1384 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1385 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1386 FND_MSG_PUB.count_and_get
1387 (
1388 p_count => x_msg_count,
1389 p_data => x_msg_data,
1390 p_encoded => FND_API.G_FALSE
1391 );
1392
1393 WHEN OTHERS THEN
1394 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1395 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1396 THEN
1397 FND_MSG_PUB.add_exc_msg
1398 (
1399 p_pkg_name => G_PKG_NAME,
1400 p_procedure_name => 'Get_MEvent_Details',
1401 p_error_text => SUBSTR(SQLERRM,1,240)
1402 );
1403 END IF;
1404 FND_MSG_PUB.count_and_get
1405 (
1406 p_count => x_msg_count,
1407 p_data => x_msg_data,
1408 p_encoded => FND_API.G_FALSE
1409 );
1410 END Get_MEvent_Details;
1411 -------------------------------------------------------------------------------------------
1412 -- Non-spec Procedure populate_unit_schedule_rec --
1413 -- Determines Maintenance Opportunity and Conflcits for the current flight record
1414 -- Prepare it with transient variable calculations
1415 -------------------------------------------------------------------------------------------
1416 PROCEDURE populate_unit_schedule_rec(
1417 p_unit_flight_schedule_rec IN AHL_UA_FLIGHT_SCHEDULES_PVT.Flight_Schedule_Rec_Type,
1418 p_prev_event_type IN VARCHAR2,
1419 p_window_event IN BOOLEAN,
1420 p_prev_unit_schedule_rec IN AHL_UA_UNIT_SCHEDULES_PVT.Unit_Schedule_rec_type,
1421 p_prev_visit_schedule_rec IN AHL_UA_UNIT_SCHEDULES_PVT.Visit_Schedule_rec_type,
1422 p_x_MEvent_Header_Rec IN OUT NOCOPY AHL_UA_UNIT_SCHEDULES_PVT.MEvent_Header_Rec_Type,
1423 p_x_Unit_Schedule_tbl IN OUT NOCOPY AHL_UA_UNIT_SCHEDULES_PVT.Unit_Schedule_Tbl_Type,
1424 p_x_Visit_Schedule_tbl IN OUT NOCOPY AHL_UA_UNIT_SCHEDULES_PVT.Visit_Schedule_Tbl_Type,
1425 x_Unit_Schedule_Rec OUT NOCOPY AHL_UA_UNIT_SCHEDULES_PVT.Unit_Schedule_rec_type
1426 ) IS
1427
1428 -- Declare local variables
1429 l_api_name CONSTANT VARCHAR2(30) := 'populate_unit_schedule_rec';
1430 L_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1431
1432 add_pre_window_event BOOLEAN;
1433 l_previous_event_seq NUMBER;
1434 l_index NUMBER;
1435
1436 org_conflict BOOLEAN;
1437 dept_conflict BOOLEAN;
1438 time_conflict BOOLEAN;
1439
1440 l_org_valid VARCHAR2(1);
1441 l_return_status VARCHAR2(1);
1442 l_msg_data VARCHAR2(2000);
1443
1444 -- added this variable because visit_end_date might be null and we have to add 1 min to the start time to
1445 -- compare for conflcits.
1446 l_prev_event_end_time DATE;
1447
1448 BEGIN
1449 -- Log API entry point
1450 ----dbms_output.put_line('adding flight record for unit_schedule_id : ' || p_unit_flight_schedule_rec.UNIT_SCHEDULE_ID);
1451 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1452 THEN
1453 fnd_log.string
1454 (
1455 fnd_log.level_procedure,
1456 L_DEBUG_MODULE||'.begin',
1457 'At the start of PLSQL procedure'
1458 );
1459 END IF;
1460
1461
1462 x_Unit_Schedule_Rec.UNIT_SCHEDULE_ID := p_unit_flight_schedule_rec.UNIT_SCHEDULE_ID;
1463 x_Unit_Schedule_Rec.FLIGHT_NUMBER := p_unit_flight_schedule_rec.FLIGHT_NUMBER;
1464 x_Unit_Schedule_Rec.SEGMENT := p_unit_flight_schedule_rec.SEGMENT;
1465 x_Unit_Schedule_Rec.DEPARTURE_ORG_ID := p_unit_flight_schedule_rec.DEPARTURE_ORG_ID;
1466 x_Unit_Schedule_Rec.DEPARTURE_ORG_NAME := p_unit_flight_schedule_rec.DEPARTURE_ORG_CODE;
1467 x_Unit_Schedule_Rec.DEPARTURE_DEP_ID := p_unit_flight_schedule_rec.DEPARTURE_DEPT_ID;
1468 x_Unit_Schedule_Rec.DEPARTURE_DEP_NAME := p_unit_flight_schedule_rec.DEPARTURE_DEPT_CODE;
1469 x_Unit_Schedule_Rec.ARRIVAL_ORG_ID := p_unit_flight_schedule_rec.ARRIVAL_ORG_ID;
1470 x_Unit_Schedule_Rec.ARRIVAL_ORG_NAME := p_unit_flight_schedule_rec.ARRIVAL_ORG_CODE;
1471 x_Unit_Schedule_Rec.ARRIVAL_DEP_ID := p_unit_flight_schedule_rec.ARRIVAL_DEPT_ID;
1472 x_Unit_Schedule_Rec.ARRIVAL_DEP_NAME := p_unit_flight_schedule_rec.ARRIVAL_DEPT_CODE;
1473
1474
1475
1476 -- whether the org is valid for the user
1477 x_Unit_Schedule_Rec.IS_ORG_VALID := FND_API.G_TRUE;
1478 IF(p_unit_flight_schedule_rec.ARRIVAL_ORG_ID IS NOT NULL)THEN
1479 x_Unit_Schedule_Rec.IS_ORG_VALID := AHL_UTILITY_PVT.IS_ORG_IN_USER_OU(
1480 p_org_id => p_unit_flight_schedule_rec.ARRIVAL_ORG_ID,
1481 p_org_name => NULL,
1482 x_return_status => l_return_status,
1483 x_msg_data => l_msg_data
1484 );
1485 -- Check for API errors
1486 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1487 ----dbms_output.put_line('Unexpected error : AHL_UTILITY_PVT.IS_ORG_IN_USER_OU returned errors');
1488 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
1489 fnd_log.string
1490 (
1491 fnd_log.level_error,
1492 L_DEBUG_MODULE,
1493 'Unexpected error : AHL_UTILITY_PVT.IS_ORG_IN_USER_OU returned errors'
1494 );
1495 END IF;
1496 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1497 END IF;
1498 END IF;
1499
1500 IF(G_USE_ACTUALS = FND_API.G_FALSE)THEN
1501 x_Unit_Schedule_Rec.DEPARTURE_TIME := p_unit_flight_schedule_rec.EST_DEPARTURE_TIME;
1502 x_Unit_Schedule_Rec.ARRIVAL_TIME := p_unit_flight_schedule_rec.EST_ARRIVAL_TIME;
1503 ELSE
1504 x_Unit_Schedule_Rec.DEPARTURE_TIME := NVL(p_unit_flight_schedule_rec.ACTUAL_DEPARTURE_TIME,
1505 p_unit_flight_schedule_rec.EST_DEPARTURE_TIME);
1506 x_Unit_Schedule_Rec.ARRIVAL_TIME := NVL(p_unit_flight_schedule_rec.ACTUAL_ARRIVAL_TIME,
1507 p_unit_flight_schedule_rec.EST_ARRIVAL_TIME);
1508 END IF;
1509
1510 x_Unit_Schedule_Rec.PREV_EVENT_TYPE := p_prev_event_type;
1511 x_Unit_Schedule_Rec.HAS_MOPPORTUNITY := FND_API.G_FALSE;
1512 x_Unit_Schedule_Rec.HAS_CONFLICT := FND_API.G_FALSE;
1513
1514 IF(p_prev_event_type = G_EVENT_TYPE_FLIGHT)THEN
1515 ----dbms_output.put_line('prev event type : ' || G_EVENT_TYPE_FLIGHT);
1516 x_Unit_Schedule_Rec.PREV_EVENT_ID := p_prev_unit_schedule_rec.UNIT_SCHEDULE_ID;
1517 x_Unit_Schedule_Rec.PREV_EVENT_ORG_ID := p_prev_unit_schedule_rec.ARRIVAL_ORG_ID;
1518 x_Unit_Schedule_Rec.IS_PREV_ORG_VALID := p_prev_unit_schedule_rec.IS_ORG_VALID;
1519 x_Unit_Schedule_Rec.PREV_EVENT_ORG_NAME := p_prev_unit_schedule_rec.ARRIVAL_ORG_NAME;
1520 x_Unit_Schedule_Rec.PREV_EVENT_DEP_ID := p_prev_unit_schedule_rec.ARRIVAL_DEP_ID;
1521 x_Unit_Schedule_Rec.PRVE_EVENT_DEP_NAME := p_prev_unit_schedule_rec.ARRIVAL_DEP_NAME;
1522 l_prev_event_end_time := p_prev_unit_schedule_rec.ARRIVAL_TIME;
1523 x_Unit_Schedule_Rec.PREV_EVENT_END_TIME := l_prev_event_end_time;
1524 x_Unit_Schedule_Rec.PREV_UNIT_SCHEDULE_ID := p_prev_unit_schedule_rec.UNIT_SCHEDULE_ID;
1525 x_Unit_Schedule_Rec.PREV_FLIGHT_NUMBER := p_prev_unit_schedule_rec.FLIGHT_NUMBER;
1526 l_previous_event_seq := NVL(p_prev_unit_schedule_rec.EVENT_SEQ,0);
1527 ELSIF(p_prev_event_type = G_EVENT_TYPE_VISIT)THEN
1528 ----dbms_output.put_line('prev event type : ' || G_EVENT_TYPE_VISIT);
1529 x_Unit_Schedule_Rec.PREV_EVENT_ID := p_prev_visit_schedule_rec.VISIT_ID;
1530 x_Unit_Schedule_Rec.PREV_EVENT_ORG_ID := p_prev_visit_schedule_rec.VISIT_ORG_ID;
1531 x_Unit_Schedule_Rec.IS_PREV_ORG_VALID := p_prev_visit_schedule_rec.IS_ORG_VALID;
1532 x_Unit_Schedule_Rec.PREV_EVENT_ORG_NAME := p_prev_visit_schedule_rec.VISIT_ORG_NAME;
1533 x_Unit_Schedule_Rec.PREV_EVENT_DEP_ID := p_prev_visit_schedule_rec.VISIT_DEP_ID;
1534 x_Unit_Schedule_Rec.PRVE_EVENT_DEP_NAME := p_prev_visit_schedule_rec.VISIT_DEP_NAME;
1535 l_prev_event_end_time := NVL(p_prev_visit_schedule_rec.END_TIME,
1536 p_prev_visit_schedule_rec.START_TIME + 1/1440) ;
1537 x_Unit_Schedule_Rec.PREV_EVENT_END_TIME := l_prev_event_end_time;
1538 x_Unit_Schedule_Rec.PREV_UNIT_SCHEDULE_ID := p_prev_visit_schedule_rec.PREV_UNIT_SCHEDULE_ID;
1539 x_Unit_Schedule_Rec.PREV_FLIGHT_NUMBER := p_prev_visit_schedule_rec.PREV_FLIGHT_NUMBER;
1540 l_previous_event_seq := NVL(p_prev_visit_schedule_rec.EVENT_SEQ,0);
1541 ELSE
1542 x_Unit_Schedule_Rec.PREV_UNIT_SCHEDULE_ID := p_prev_unit_schedule_rec.UNIT_SCHEDULE_ID;
1543 x_Unit_Schedule_Rec.PREV_FLIGHT_NUMBER := p_prev_unit_schedule_rec.FLIGHT_NUMBER;
1544 END IF;
1545
1546 -- Determine Conflcits
1547 IF(x_Unit_Schedule_Rec.PREV_EVENT_ID IS NOT NULL)THEN
1548 IF(x_Unit_Schedule_Rec.DEPARTURE_ORG_ID <> x_Unit_Schedule_Rec.PREV_EVENT_ORG_ID)THEN
1549 x_Unit_Schedule_Rec.HAS_CONFLICT := FND_API.G_TRUE;
1550 org_conflict := TRUE;
1551 END IF;
1552 IF(G_DEPT_CONFLICT AND x_Unit_Schedule_Rec.DEPARTURE_DEP_ID <> x_Unit_Schedule_Rec.PREV_EVENT_DEP_ID)THEN
1553 x_Unit_Schedule_Rec.HAS_CONFLICT := FND_API.G_TRUE;
1554 dept_conflict := TRUE;
1555 END IF;
1556 IF(x_Unit_Schedule_Rec.DEPARTURE_TIME < l_prev_event_end_time)THEN
1557 x_Unit_Schedule_Rec.HAS_CONFLICT := FND_API.G_TRUE;
1558 time_conflict := TRUE;
1559 END IF;
1560 END IF;
1561
1562 -- Determine Maintenance Opportunity
1563 -- MO should be in future
1564 /*
1565 * Commenting out the following lines of code - bug #4071097 has been converted to ER...
1566 * Retaining code since this will later be needed anyway, when the ER is worked upon...
1567 */
1568 -- Uncommented the following lines of code as part of fix for bug# 6447447
1569 IF(x_Unit_Schedule_Rec.PREV_EVENT_ID IS NOT NULL AND
1570 --x_Unit_Schedule_Rec.PREV_UNIT_SCHEDULE_ID IS NOT NULL AND
1571 x_Unit_Schedule_Rec.DEPARTURE_TIME > l_prev_event_end_time AND
1572 x_Unit_Schedule_Rec.DEPARTURE_TIME > SYSDATE) THEN
1573 IF(l_prev_event_end_time >= SYSDATE AND
1574 (x_Unit_Schedule_Rec.DEPARTURE_TIME - l_prev_event_end_time)*24*60 >= G_MIN_TIME_MO)THEN
1575 x_Unit_Schedule_Rec.HAS_MOPPORTUNITY := FND_API.G_TRUE;
1576 ELSIF(l_prev_event_end_time < SYSDATE AND
1577 (x_Unit_Schedule_Rec.DEPARTURE_TIME - SYSDATE)*24*60 >= G_MIN_TIME_MO)THEN
1578 x_Unit_Schedule_Rec.HAS_MOPPORTUNITY := FND_API.G_TRUE;
1579 END IF;
1580 END IF;
1581 --Commented the following lines of code as part of fix for bug# 6447447
1582 /*
1583 IF(x_Unit_Schedule_Rec.PREV_EVENT_ID IS NOT NULL AND
1584 x_Unit_Schedule_Rec.PREV_UNIT_SCHEDULE_ID IS NOT NULL AND
1585 x_Unit_Schedule_Rec.DEPARTURE_TIME > l_prev_event_end_time) THEN
1586 IF ((x_Unit_Schedule_Rec.DEPARTURE_TIME - l_prev_event_end_time)*24*60 >= G_MIN_TIME_MO)THEN
1587 x_Unit_Schedule_Rec.HAS_MOPPORTUNITY := FND_API.G_TRUE;
1588 END IF;
1589 END IF;
1590 */
1591
1592 add_pre_window_event := FALSE;
1593 -- Determine event sequence based on window and non window event
1594 -- decide whether to add "out of window" records.
1595 -- out of window events will be added when there is MO or conflcit
1596 IF(p_window_event)THEN
1597 IF(x_Unit_Schedule_Rec.PREV_EVENT_ID IS NULL)THEN
1598 x_Unit_Schedule_Rec.EVENT_SEQ := 1;
1599 ELSE
1600 IF(l_previous_event_seq > 0)THEN
1601 x_Unit_Schedule_Rec.EVENT_SEQ := l_previous_event_seq + 1;
1602 ELSE
1603 IF(x_Unit_Schedule_Rec.HAS_MOPPORTUNITY = FND_API.G_TRUE OR x_Unit_Schedule_Rec.HAS_CONFLICT = FND_API.G_TRUE)THEN
1604 add_pre_window_event := TRUE;
1605 x_Unit_Schedule_Rec.EVENT_SEQ := 2;
1606 ELSE
1607 x_Unit_Schedule_Rec.EVENT_SEQ := 1;
1608 END IF;
1609 END IF;
1610 END IF;
1611 ELSIF( NOT p_window_event AND x_Unit_Schedule_Rec.PREV_EVENT_ID IS NOT NULL)THEN
1612 IF(x_Unit_Schedule_Rec.HAS_MOPPORTUNITY = FND_API.G_TRUE OR x_Unit_Schedule_Rec.HAS_CONFLICT = FND_API.G_TRUE)THEN
1613 IF(l_previous_event_seq > 0)THEN
1614 x_Unit_Schedule_Rec.EVENT_SEQ := l_previous_event_seq + 1;
1615 ELSE
1616 add_pre_window_event := TRUE;
1617 x_Unit_Schedule_Rec.EVENT_SEQ := 2;
1618 END IF;
1619 END IF;
1620 END IF;
1621 -- add pre window event if applicable
1622 IF(add_pre_window_event)THEN
1623 IF(p_prev_event_type = G_EVENT_TYPE_FLIGHT)THEN
1624 p_x_Unit_Schedule_tbl(0) := p_prev_unit_schedule_rec;
1625 p_x_Unit_Schedule_tbl(0).EVENT_SEQ := 1;
1626 ELSIF(p_prev_event_type = G_EVENT_TYPE_VISIT)THEN
1627 p_x_Visit_Schedule_tbl(0) := p_prev_visit_schedule_rec;
1628 p_x_Visit_Schedule_tbl(0).EVENT_SEQ := 1;
1629 END IF;
1630 END IF;
1631
1632 -- Add Conflcit Message
1633 IF(x_Unit_Schedule_Rec.HAS_CONFLICT = FND_API.G_TRUE)THEN
1634 -- Event with sequence () and previous Event() has conflcits.
1635 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UA_FLIGHT_CONFLICT_MSG');
1636 FND_MESSAGE.SET_TOKEN('EVENT_SEQ1',x_Unit_Schedule_Rec.EVENT_SEQ,false);
1637 FND_MESSAGE.SET_TOKEN('EVENT_SEQ2',x_Unit_Schedule_Rec.EVENT_SEQ -1,false);
1638 x_Unit_Schedule_Rec.CONFLICT_MESSAGE := FND_MESSAGE.get;
1639 IF(org_conflict)THEN
1640 IF(p_prev_event_type = G_EVENT_TYPE_FLIGHT)THEN
1641 -- Departure Org() and previous flight Arrival Org() do not match.
1642 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UA_FFO_CONFLICT_MSG');
1643 FND_MESSAGE.SET_TOKEN('DEP_ORG',x_Unit_Schedule_Rec.DEPARTURE_ORG_NAME,false);
1644 FND_MESSAGE.SET_TOKEN('ARR_ORG',p_prev_unit_schedule_rec.ARRIVAL_ORG_NAME,false);
1645 x_Unit_Schedule_Rec.CONFLICT_MESSAGE := x_Unit_Schedule_Rec.CONFLICT_MESSAGE || FND_MESSAGE.get;
1646 ELSIF(p_prev_event_type = G_EVENT_TYPE_VISIT)THEN
1647 -- Daparture Org() and previous visit Org() do not match.
1648 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UA_FVO_CONFLICT_MSG');
1649 FND_MESSAGE.SET_TOKEN('DEP_ORG',x_Unit_Schedule_Rec.DEPARTURE_ORG_NAME,false);
1650 FND_MESSAGE.SET_TOKEN('VST_ORG',p_prev_visit_schedule_rec.VISIT_ORG_NAME,false);
1651 x_Unit_Schedule_Rec.CONFLICT_MESSAGE := x_Unit_Schedule_Rec.CONFLICT_MESSAGE || FND_MESSAGE.get;
1652 END IF;
1653 END IF;
1654 IF(dept_conflict)THEN
1655 IF(p_prev_event_type = G_EVENT_TYPE_FLIGHT)THEN
1656 -- Departure Dept() and previous flight Arrival Dept() do not match.
1657 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UA_FFD_CONFLICT_MSG');
1658 FND_MESSAGE.SET_TOKEN('DEP_DEPT',x_Unit_Schedule_Rec.DEPARTURE_DEP_NAME,false);
1659 FND_MESSAGE.SET_TOKEN('ARR_DEPT',p_prev_unit_schedule_rec.ARRIVAL_DEP_NAME,false);
1660 x_Unit_Schedule_Rec.CONFLICT_MESSAGE := x_Unit_Schedule_Rec.CONFLICT_MESSAGE ||FND_MESSAGE.get;
1661 ELSIF(p_prev_event_type = G_EVENT_TYPE_VISIT)THEN
1662 -- Departure Dept() and previous visit Dept() do not match.
1663 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UA_FVD_CONFLICT_MSG');
1664 FND_MESSAGE.SET_TOKEN('DEP_DEPT',x_Unit_Schedule_Rec.DEPARTURE_DEP_NAME,false);
1665 FND_MESSAGE.SET_TOKEN('VST_DEPT',p_prev_visit_schedule_rec.VISIT_DEP_NAME,false);
1666 x_Unit_Schedule_Rec.CONFLICT_MESSAGE := x_Unit_Schedule_Rec.CONFLICT_MESSAGE ||FND_MESSAGE.get;
1667 END IF;
1668 END IF;
1669 IF(time_conflict)THEN
1670 IF(p_prev_event_type = G_EVENT_TYPE_FLIGHT)THEN
1671 -- Departure Time and previous flight Arrival Time has overlap.
1672 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UA_FFT_CONFLICT_MSG');
1673 x_Unit_Schedule_Rec.CONFLICT_MESSAGE := x_Unit_Schedule_Rec.CONFLICT_MESSAGE ||FND_MESSAGE.get;
1674 ELSIF(p_prev_event_type = G_EVENT_TYPE_VISIT)THEN
1675 -- Departure Time and previous Visit End Time has overlap.
1676 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UA_FVT_CONFLICT_MSG');
1677 x_Unit_Schedule_Rec.CONFLICT_MESSAGE := x_Unit_Schedule_Rec.CONFLICT_MESSAGE ||FND_MESSAGE.get;
1678 END IF;
1679 END IF;
1680 END IF;
1681 -- add current event to table of events.
1682 IF(NVL(x_Unit_Schedule_Rec.EVENT_SEQ,0) > 0)THEN
1683 IF(p_x_Unit_Schedule_tbl IS NULL)THEN
1684 l_index := 0;
1685 ELSE
1686 l_index := p_x_Unit_Schedule_tbl.COUNT;
1687 END IF;
1688 p_x_Unit_Schedule_tbl(l_index) := x_Unit_Schedule_Rec;
1689 END IF;
1690
1691 -- Update conflict or MO at the header level
1692 IF(x_Unit_Schedule_Rec.HAS_CONFLICT = FND_API.G_TRUE)THEN
1693 p_x_MEvent_Header_Rec.HAS_CONFLICT := FND_API.G_TRUE;
1694 END IF;
1695 IF(x_Unit_Schedule_Rec.HAS_MOPPORTUNITY = FND_API.G_TRUE)THEN
1696 p_x_MEvent_Header_Rec.HAS_MOPPORTUNITY := FND_API.G_TRUE;
1697 END IF;
1698
1699 -- Log API exit point
1700 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1701 THEN
1702 fnd_log.string
1703 (
1704 fnd_log.level_procedure,
1705 L_DEBUG_MODULE||'.end',
1706 'At the end of PLSQL procedure'
1707 );
1708 END IF;
1709 ----dbms_output.put_line('added flight record for unit_schedule_id : ' || p_unit_flight_schedule_rec.UNIT_SCHEDULE_ID);
1710
1711
1712 END populate_unit_schedule_rec;
1713
1714 -------------------------------------------------------------------------------------------
1715 -- Non-spec Procedure populate_visit_schedule_rec --
1716 -- Determines Maintenance Opportunity and Conflcits for the current visit record
1717 -- Prepare it with transient variable calculations
1718 -------------------------------------------------------------------------------------------
1719 PROCEDURE populate_visit_schedule_rec(
1720 p_visit_rec IN AHL_VWP_VISITS_PVT.Visit_Rec_Type,
1721 p_prev_event_type IN VARCHAR2,
1722 p_window_event IN BOOLEAN,
1723 p_prev_unit_schedule_rec IN AHL_UA_UNIT_SCHEDULES_PVT.Unit_Schedule_rec_type,
1724 p_prev_visit_schedule_rec IN AHL_UA_UNIT_SCHEDULES_PVT.Visit_Schedule_rec_type,
1725 p_x_MEvent_Header_Rec IN OUT NOCOPY AHL_UA_UNIT_SCHEDULES_PVT.MEvent_Header_Rec_Type,
1726 p_x_Unit_Schedule_tbl IN OUT NOCOPY AHL_UA_UNIT_SCHEDULES_PVT.Unit_Schedule_Tbl_Type,
1727 p_x_Visit_Schedule_tbl IN OUT NOCOPY AHL_UA_UNIT_SCHEDULES_PVT.Visit_Schedule_Tbl_Type,
1728 x_visit_schedule_rec OUT NOCOPY AHL_UA_UNIT_SCHEDULES_PVT.Visit_Schedule_rec_type
1729 )IS
1730
1731 -- Declare local variables
1732 l_api_name CONSTANT VARCHAR2(30) := 'populate_visit_schedule_rec';
1733 L_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1734
1735 add_pre_window_event BOOLEAN;
1736 l_previous_event_seq NUMBER;
1737 l_index NUMBER;
1738
1739 org_conflict BOOLEAN;
1740 dept_conflict BOOLEAN;
1741 time_conflict BOOLEAN;
1742
1743 l_org_valid VARCHAR2(1);
1744 l_return_status VARCHAR2(1);
1745 l_msg_data VARCHAR2(2000);
1746
1747 -- added this variable because visit_end_date might be null and we have to add 1 min to the start time to
1748 -- compare for conflcits.
1749 l_prev_event_end_time DATE;
1750
1751 BEGIN
1752 -- Log API entry point
1753 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1754 THEN
1755 fnd_log.string
1756 (
1757 fnd_log.level_procedure,
1758 L_DEBUG_MODULE||'.begin',
1759 'At the start of PLSQL procedure'
1760 );
1761 END IF;
1762 ----dbms_output.put_line('adding visit record for VISIT_ID : ' || p_Visit_Rec.VISIT_ID);
1763
1764 x_visit_Schedule_rec.VISIT_ID := p_Visit_Rec.VISIT_ID;
1765 x_visit_Schedule_rec.VISIT_NUMBER := p_Visit_Rec.VISIT_NUMBER;
1766 x_visit_Schedule_rec.VISIT_NAME := p_Visit_Rec.VISIT_NAME;
1767 x_visit_Schedule_rec.VISIT_TYPE := p_Visit_Rec.VISIT_TYPE_NAME;
1768 x_visit_Schedule_rec.VISIT_STATUS_CODE := p_Visit_Rec.STATUS_CODE;
1769 x_visit_Schedule_rec.VISIT_STATUS := p_Visit_Rec.STATUS_NAME;
1770 ----dbms_output.put_line('adding org and dept info: ');
1771
1772 x_Visit_Schedule_Rec.VISIT_ORG_ID := p_Visit_Rec.ORGANIZATION_ID;
1773 x_Visit_Schedule_Rec.VISIT_ORG_NAME := p_Visit_Rec.ORG_NAME;
1774 x_Visit_Schedule_Rec.VISIT_DEP_ID := p_Visit_Rec.DEPARTMENT_ID;
1775 x_Visit_Schedule_Rec.VISIT_DEP_NAME := p_Visit_Rec.DEPT_NAME;
1776 x_Visit_Schedule_Rec.START_TIME := p_Visit_Rec.START_DATE;
1777 x_Visit_Schedule_Rec.END_TIME := p_Visit_Rec.END_DATE;
1778 ----dbms_output.put_line('added org and dept info: ');
1779
1780
1781
1782 x_Visit_Schedule_Rec.IS_ORG_VALID := FND_API.G_TRUE;
1783 IF(p_Visit_Rec.ORGANIZATION_ID IS NOT NULL)THEN
1784
1785 -- whether the org is valid for the user
1786 x_Visit_Schedule_Rec.IS_ORG_VALID := AHL_UTILITY_PVT.IS_ORG_IN_USER_OU(
1787 p_org_id => p_Visit_Rec.ORGANIZATION_ID,
1788 p_org_name => NULL,
1789 x_return_status => l_return_status,
1790 x_msg_data => l_msg_data
1791 );
1792 -- Check for API errors
1793 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1794 ----dbms_output.put_line('Unexpected error : AHL_UTILITY_PVT.IS_ORG_IN_USER_OU returned errors');
1795 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
1796 fnd_log.string
1797 (
1798 fnd_log.level_error,
1799 L_DEBUG_MODULE,
1800 'Unexpected error : AHL_UTILITY_PVT.IS_ORG_IN_USER_OU returned errors'
1801 );
1802 END IF;
1803 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1804 END IF;
1805 END IF;
1806
1807 IF(NVL(x_visit_Schedule_rec.VISIT_STATUS_CODE,'x') NOT IN( 'CLOSED','CANCELLED'))THEN
1808 x_Visit_Schedule_Rec.CAN_CANCEL := FND_API.G_TRUE;
1809 ELSE
1810 x_Visit_Schedule_Rec.CAN_CANCEL := FND_API.G_FALSE;
1811 END IF;
1812
1813
1814 x_Visit_Schedule_Rec.PREV_EVENT_TYPE := p_prev_event_type;
1815 x_Visit_Schedule_Rec.HAS_MOPPORTUNITY := FND_API.G_FALSE;
1816 x_Visit_Schedule_Rec.HAS_CONFLICT := FND_API.G_FALSE;
1817
1818 IF(p_prev_event_type = G_EVENT_TYPE_FLIGHT)THEN
1819 ----dbms_output.put_line('Previous event type : ' || G_EVENT_TYPE_FLIGHT);
1820
1821 x_Visit_Schedule_Rec.PREV_EVENT_ID := p_prev_unit_schedule_rec.UNIT_SCHEDULE_ID;
1822 x_Visit_Schedule_Rec.PREV_EVENT_ORG_ID := p_prev_unit_schedule_rec.ARRIVAL_ORG_ID;
1823 x_Visit_Schedule_Rec.IS_PREV_ORG_VALID := p_prev_unit_schedule_rec.IS_ORG_VALID;
1824 x_Visit_Schedule_Rec.PREV_EVENT_ORG_NAME := p_prev_unit_schedule_rec.ARRIVAL_ORG_NAME;
1825 x_Visit_Schedule_Rec.PREV_EVENT_DEP_ID := p_prev_unit_schedule_rec.ARRIVAL_DEP_ID;
1826 x_Visit_Schedule_Rec.PRVE_EVENT_DEP_NAME := p_prev_unit_schedule_rec.ARRIVAL_DEP_NAME;
1827 l_prev_event_end_time := p_prev_unit_schedule_rec.ARRIVAL_TIME;
1828 x_Visit_Schedule_Rec.PREV_EVENT_END_TIME := l_prev_event_end_time;
1829 x_Visit_Schedule_Rec.PREV_UNIT_SCHEDULE_ID := p_prev_unit_schedule_rec.UNIT_SCHEDULE_ID;
1830 x_Visit_Schedule_Rec.PREV_FLIGHT_NUMBER := p_prev_unit_schedule_rec.FLIGHT_NUMBER;
1831 l_previous_event_seq := NVL(p_prev_unit_schedule_rec.EVENT_SEQ,0);
1832 ELSIF(p_prev_event_type = G_EVENT_TYPE_VISIT)THEN
1833 ----dbms_output.put_line('Previous event type : ' || G_EVENT_TYPE_VISIT);
1834 x_Visit_Schedule_Rec.PREV_EVENT_ID := p_prev_visit_schedule_rec.VISIT_ID;
1835 x_Visit_Schedule_Rec.PREV_EVENT_ORG_ID := p_prev_visit_schedule_rec.VISIT_ORG_ID;
1836 x_Visit_Schedule_Rec.IS_PREV_ORG_VALID := p_prev_visit_schedule_rec.IS_ORG_VALID;
1837 x_Visit_Schedule_Rec.PREV_EVENT_ORG_NAME := p_prev_visit_schedule_rec.VISIT_ORG_NAME;
1838 x_Visit_Schedule_Rec.PREV_EVENT_DEP_ID := p_prev_visit_schedule_rec.VISIT_DEP_ID;
1839 x_Visit_Schedule_Rec.PRVE_EVENT_DEP_NAME := p_prev_visit_schedule_rec.VISIT_DEP_NAME;
1840 l_prev_event_end_time := NVL(p_prev_visit_schedule_rec.END_TIME,
1841 p_prev_visit_schedule_rec.START_TIME+ 1/1440) ;
1842 x_Visit_Schedule_Rec.PREV_EVENT_END_TIME := l_prev_event_end_time;
1843 x_Visit_Schedule_Rec.PREV_UNIT_SCHEDULE_ID := p_prev_visit_schedule_rec.PREV_UNIT_SCHEDULE_ID;
1844 x_Visit_Schedule_Rec.PREV_FLIGHT_NUMBER := p_prev_visit_schedule_rec.PREV_FLIGHT_NUMBER;
1845 l_previous_event_seq := NVL(p_prev_visit_schedule_rec.EVENT_SEQ,0);
1846 ELSE
1847 x_Visit_Schedule_Rec.PREV_UNIT_SCHEDULE_ID := p_prev_unit_schedule_rec.UNIT_SCHEDULE_ID;
1848 x_Visit_Schedule_Rec.PREV_FLIGHT_NUMBER := p_prev_unit_schedule_rec.FLIGHT_NUMBER;
1849 END IF;
1850 ----dbms_output.put_line('deciding on conflicts ');
1851
1852 -- Determine Conflcits
1853 IF(x_Visit_Schedule_Rec.PREV_EVENT_ID IS NOT NULL)THEN
1854 IF(x_Visit_Schedule_Rec.VISIT_ORG_ID <> x_Visit_Schedule_Rec.PREV_EVENT_ORG_ID)THEN
1855 org_conflict := TRUE;
1856 x_Visit_Schedule_Rec.HAS_CONFLICT := FND_API.G_TRUE;
1857 END IF;
1858 IF(G_DEPT_CONFLICT AND x_Visit_Schedule_Rec.VISIT_DEP_ID <> x_Visit_Schedule_Rec.PREV_EVENT_DEP_ID)THEN
1859 dept_conflict := TRUE;
1860 x_Visit_Schedule_Rec.HAS_CONFLICT := FND_API.G_TRUE;
1861 END IF;
1862 IF(x_Visit_Schedule_Rec.START_TIME < l_prev_event_end_time)THEN
1863 time_conflict := TRUE;
1864 x_Visit_Schedule_Rec.HAS_CONFLICT := FND_API.G_TRUE;
1865 END IF;
1866 END IF;
1867
1868 -- Determine Maintenance Opportunity
1869 -- MO should be in future
1870 /*
1871 * Commenting out the following lines of code - bug #4071097 has been converted to ER...
1872 * Retaining code since this will later be needed anyway, when the ER is worked upon...
1873 */
1874 -- Uncommented the following lines of code as part of fix for bug# 6447447
1875 IF(x_Visit_Schedule_Rec.PREV_EVENT_ID IS NOT NULL AND
1876 --x_Visit_Schedule_Rec.PREV_UNIT_SCHEDULE_ID IS NOT NULL AND
1877 x_Visit_Schedule_Rec.START_TIME > l_prev_event_end_time AND
1878 x_Visit_Schedule_Rec.START_TIME > SYSDATE) THEN
1879 IF(l_prev_event_end_time >= SYSDATE AND
1880 (x_Visit_Schedule_Rec.START_TIME - l_prev_event_end_time)*24*60 >= G_MIN_TIME_MO)THEN
1881 x_Visit_Schedule_Rec.HAS_MOPPORTUNITY := FND_API.G_TRUE;
1882 ELSIF(l_prev_event_end_time < SYSDATE AND
1883 (x_Visit_Schedule_Rec.START_TIME - SYSDATE)*24*60 >= G_MIN_TIME_MO)THEN
1884 x_Visit_Schedule_Rec.HAS_MOPPORTUNITY := FND_API.G_TRUE;
1885 END IF;
1886 END IF;
1887
1888 -- Commented the following lines of code as part of fix for bug# 6447447
1889 /*
1890 IF(x_Visit_Schedule_Rec.PREV_EVENT_ID IS NOT NULL AND
1891 x_Visit_Schedule_Rec.PREV_UNIT_SCHEDULE_ID IS NOT NULL AND
1892 x_Visit_Schedule_Rec.START_TIME > l_prev_event_end_time) THEN
1893 IF ((x_Visit_Schedule_Rec.START_TIME - l_prev_event_end_time)*24*60 >= G_MIN_TIME_MO)THEN
1894 x_Visit_Schedule_Rec.HAS_MOPPORTUNITY := FND_API.G_TRUE;
1895 END IF;
1896 END IF;
1897 */
1898
1899 add_pre_window_event := FALSE;
1900 -- Determine event sequence based on window and non window event
1901 -- decide whether to add "out of window" records.
1902 IF(p_window_event)THEN
1903 IF(x_Visit_Schedule_Rec.PREV_EVENT_ID IS NULL)THEN
1904 x_Visit_Schedule_Rec.EVENT_SEQ := 1;
1905 ELSE
1906 IF(l_previous_event_seq > 0)THEN
1907 x_Visit_Schedule_Rec.EVENT_SEQ := l_previous_event_seq + 1;
1908 ELSE
1909 IF(x_Visit_Schedule_Rec.HAS_MOPPORTUNITY = FND_API.G_TRUE OR x_Visit_Schedule_Rec.HAS_CONFLICT = FND_API.G_TRUE)THEN
1910 add_pre_window_event := TRUE;
1911 x_Visit_Schedule_Rec.EVENT_SEQ := 2;
1912 ELSE
1913 x_Visit_Schedule_Rec.EVENT_SEQ := 1;
1914 END IF;
1915 END IF;
1916 END IF;
1917 ELSIF( NOT p_window_event AND x_Visit_Schedule_Rec.PREV_EVENT_ID IS NOT NULL)THEN
1918 IF(x_Visit_Schedule_Rec.HAS_MOPPORTUNITY = FND_API.G_TRUE OR x_Visit_Schedule_Rec.HAS_CONFLICT = FND_API.G_TRUE)THEN
1919 IF(l_previous_event_seq > 0)THEN
1920 x_Visit_Schedule_Rec.EVENT_SEQ := l_previous_event_seq + 1;
1921 ELSE
1922 add_pre_window_event := TRUE;
1923 x_Visit_Schedule_Rec.EVENT_SEQ := 2;
1924 END IF;
1925 END IF;
1926 END IF;
1927 -- add pre window event if applicable
1928 IF(add_pre_window_event)THEN
1929 IF(p_prev_event_type = G_EVENT_TYPE_FLIGHT)THEN
1930 p_x_Unit_Schedule_tbl(0) := p_prev_unit_schedule_rec;
1931 p_x_Unit_Schedule_tbl(0).EVENT_SEQ := 1;
1932 ELSIF(p_prev_event_type = G_EVENT_TYPE_VISIT)THEN
1933 p_x_Visit_Schedule_tbl(0) := p_prev_visit_schedule_rec;
1934 p_x_Visit_Schedule_tbl(0).EVENT_SEQ := 1;
1935 END IF;
1936 END IF;
1937
1938 -- Add Conflcit Message
1939 IF(x_Visit_Schedule_Rec.HAS_CONFLICT = FND_API.G_TRUE)THEN
1940 -- Event with sequence () and previous Event() has conflcits.
1941 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UA_FLIGHT_CONFLICT_MSG');
1942 FND_MESSAGE.SET_TOKEN('EVENT_SEQ1',x_Visit_Schedule_Rec.EVENT_SEQ,false);
1943 FND_MESSAGE.SET_TOKEN('EVENT_SEQ2',x_Visit_Schedule_Rec.EVENT_SEQ -1,false);
1944 x_Visit_Schedule_Rec.CONFLICT_MESSAGE := FND_MESSAGE.get;
1945 IF(org_conflict)THEN
1946 IF(p_prev_event_type = G_EVENT_TYPE_FLIGHT)THEN
1947 -- Visit Org() and previous flight Arrival Org() do not match.
1948 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UA_VFO_CONFLICT_MSG');
1949 FND_MESSAGE.SET_TOKEN('VST_ORG',x_Visit_Schedule_Rec.VISIT_ORG_NAME,false);
1950 FND_MESSAGE.SET_TOKEN('ARR_ORG',p_prev_unit_schedule_rec.ARRIVAL_ORG_NAME,false);
1951 x_Visit_Schedule_Rec.CONFLICT_MESSAGE := x_Visit_Schedule_Rec.CONFLICT_MESSAGE || FND_MESSAGE.get;
1952 ELSIF(p_prev_event_type = G_EVENT_TYPE_VISIT)THEN
1953 -- Visit Org() and previous visit Org() do not match.
1954 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UA_VVO_CONFLICT_MSG');
1955 FND_MESSAGE.SET_TOKEN('VST_ORG',x_Visit_Schedule_Rec.VISIT_ORG_NAME,false);
1956 FND_MESSAGE.SET_TOKEN('VST_PORG',p_prev_visit_schedule_rec.VISIT_ORG_NAME,false);
1957 x_Visit_Schedule_Rec.CONFLICT_MESSAGE := x_Visit_Schedule_Rec.CONFLICT_MESSAGE || FND_MESSAGE.get;
1958 END IF;
1959 END IF;
1960 IF(dept_conflict)THEN
1961 IF(p_prev_event_type = G_EVENT_TYPE_FLIGHT)THEN
1962 -- Visit Dept() and previous flight Arrival Dept() do not match.
1963 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UA_VFD_CONFLICT_MSG');
1964 FND_MESSAGE.SET_TOKEN('VST_DEPT',x_Visit_Schedule_Rec.VISIT_DEP_NAME,false);
1965 FND_MESSAGE.SET_TOKEN('ARR_DEPT',p_prev_unit_schedule_rec.ARRIVAL_DEP_NAME,false);
1966 x_Visit_Schedule_Rec.CONFLICT_MESSAGE := x_Visit_Schedule_Rec.CONFLICT_MESSAGE ||FND_MESSAGE.get;
1967 ELSIF(p_prev_event_type = G_EVENT_TYPE_VISIT)THEN
1968 -- Visit Dept() and previous visit Dept() do not match.
1969 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UA_VVD_CONFLICT_MSG');
1970 FND_MESSAGE.SET_TOKEN('VST_DEPT',x_Visit_Schedule_Rec.VISIT_DEP_NAME,false);
1971 FND_MESSAGE.SET_TOKEN('VST_PDEPT',p_prev_visit_schedule_rec.VISIT_DEP_NAME,false);
1972 x_Visit_Schedule_Rec.CONFLICT_MESSAGE := x_Visit_Schedule_Rec.CONFLICT_MESSAGE ||FND_MESSAGE.get;
1973 END IF;
1974 END IF;
1975 IF(time_conflict)THEN
1976 IF(p_prev_event_type = G_EVENT_TYPE_FLIGHT)THEN
1977 -- Visit start Time and previous flight Arrival Time has overlap.
1978 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UA_VFT_CONFLICT_MSG');
1979 x_Visit_Schedule_Rec.CONFLICT_MESSAGE := x_Visit_Schedule_Rec.CONFLICT_MESSAGE ||FND_MESSAGE.get;
1980 ELSIF(p_prev_event_type = G_EVENT_TYPE_VISIT)THEN
1981 -- Visit Start Time and previous Visit End Time has overlap.
1982 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UA_VVT_CONFLICT_MSG');
1983 x_Visit_Schedule_Rec.CONFLICT_MESSAGE := x_Visit_Schedule_Rec.CONFLICT_MESSAGE ||FND_MESSAGE.get;
1984 END IF;
1985 END IF;
1986 END IF;
1987 -- add current event to table of events.
1988 IF(NVL(x_Visit_Schedule_Rec.EVENT_SEQ,0) > 0)THEN
1989 IF(p_x_Visit_Schedule_tbl IS NULL)THEN
1990 l_index := 0;
1991 ELSE
1992 l_index := p_x_Visit_Schedule_tbl.COUNT;
1993 END IF;
1994 p_x_Visit_Schedule_tbl(l_index) := x_Visit_Schedule_Rec;
1995 END IF;
1996
1997 -- Update conflict or MO at the header level
1998 IF(x_Visit_Schedule_Rec.HAS_CONFLICT = FND_API.G_TRUE)THEN
1999 p_x_MEvent_Header_Rec.HAS_CONFLICT := FND_API.G_TRUE;
2000 END IF;
2001 IF(x_Visit_Schedule_Rec.HAS_MOPPORTUNITY = FND_API.G_TRUE)THEN
2002 p_x_MEvent_Header_Rec.HAS_MOPPORTUNITY := FND_API.G_TRUE;
2003 END IF;
2004
2005 -- Log API exit point
2006 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2007 THEN
2008 fnd_log.string
2009 (
2010 fnd_log.level_procedure,
2011 L_DEBUG_MODULE||'.end',
2012 'At the end of PLSQL procedure'
2013 );
2014 END IF;
2015 ----dbms_output.put_line('added visit record for VISIT_ID : ' || p_Visit_Rec.VISIT_ID);
2016
2017 END populate_visit_schedule_rec;
2018 ---------------------------------------------
2019 -- Spec Procedure Get_Prec_Succ_Event_Info --
2020 ---------------------------------------------
2021 PROCEDURE Get_Prec_Succ_Event_Info
2022 (
2023 p_api_version IN NUMBER,
2024 x_return_status OUT NOCOPY VARCHAR2,
2025 x_msg_count OUT NOCOPY NUMBER,
2026 x_msg_data OUT NOCOPY VARCHAR2,
2027 p_unit_config_id IN NUMBER,
2028 p_start_date_time IN DATE,
2029 p_end_date_time IN DATE,
2030 x_prec_visit OUT NOCOPY AHL_VWP_VISITS_PVT.Visit_Rec_Type,
2031 x_prec_flight_schedule OUT NOCOPY AHL_UA_FLIGHT_SCHEDULES_PVT.Flight_Schedule_Rec_Type,
2032 x_is_prec_conflict OUT NOCOPY VARCHAR2,
2033 x_is_prec_org_in_ou OUT NOCOPY VARCHAR2,
2034 x_succ_visit OUT NOCOPY AHL_VWP_VISITS_PVT.Visit_Rec_Type,
2035 x_succ_flight_schedule OUT NOCOPY AHL_UA_FLIGHT_SCHEDULES_PVT.Flight_Schedule_Rec_Type,
2036 x_is_succ_conflict OUT NOCOPY VARCHAR2,
2037 x_is_succ_org_in_ou OUT NOCOPY VARCHAR2
2038 )
2039 IS
2040 -- 1. Declare local variables
2041 l_api_name CONSTANT VARCHAR2(30) := 'Get_Prec_Succ_Event_Info';
2042 l_api_version CONSTANT NUMBER := 1.0;
2043 l_return_status VARCHAR2(1);
2044 l_msg_count NUMBER;
2045 l_msg_data VARCHAR2(2000);
2046 L_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
2047
2048 cursor check_unit_exists
2049 (
2050 p_unit_config_id number
2051 )
2052 is
2053 select 'x'
2054 from ahl_unit_config_headers
2055 where unit_config_header_id = p_unit_config_id
2056 --priyan Bug # 5303188
2057 --and ahl_util_uc_pkg.get_uc_status_code (p_unit_config_id) IN ('COMPLETE', 'INCOMPLETE');
2058 --and unit_config_status_code IN ('COMPLETE', 'INCOMPLETE');
2059 -- fix for bug #5528416 - must include quarantined units
2060 and ahl_util_uc_pkg.get_uc_status_code (p_unit_config_id) NOT IN ('DRAFT', 'EXPIRED');
2061
2062 BEGIN
2063 -- Standard call to check for call compatibility
2064 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
2065 THEN
2066 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2067 END IF;
2068
2069 -- Initialize message list by default
2070 FND_MSG_PUB.Initialize;
2071
2072 -- Initialize API return status to success
2073 x_return_status := FND_API.G_RET_STS_SUCCESS;
2074
2075 -- Log API entry point
2076 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2077 THEN
2078 fnd_log.string
2079 (
2080 fnd_log.level_procedure,
2081 L_DEBUG_MODULE||'.begin',
2082 'At the start of PLSQL procedure'
2083 );
2084 END IF;
2085
2086 -- API body starts here
2087 -- 5. If (p_unit_config_id is null or p_start_date_time is null or p_end_date_time is null), then display error "Unit Configuration Id and Start Time are mandatory parameters"
2088 IF (
2089 p_unit_config_id IS NULL OR p_unit_config_id = FND_API.G_MISS_NUM OR
2090 p_start_date_time IS NULL OR p_start_date_time = FND_API.G_MISS_DATE
2091 -- There could be cases where there are no succeeding events, hence np p_end_date_time
2092 --p_end_date_time IS NULL OR p_end_date_time = FND_API.G_MISS_DATE OR
2093 --p_start_date_time >= nvl(p_end_date_time, p_start_date_time + 1)
2094 )
2095 THEN
2096 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_COM_INVALID_PROCEDURE_CALL');
2097 FND_MESSAGE.SET_TOKEN('PROCEDURE', l_api_name);
2098 FND_MSG_PUB.ADD;
2099 RAISE FND_API.G_EXC_ERROR;
2100 END IF;
2101
2102 -- 6. Validate unit configuration with unit_config_id = p_unit_config_id exists and is complete and active, using cursor check_unit_exists. If no record found, throw error "Unit Configuration does not exist or is not complete"
2103 OPEN check_unit_exists (p_unit_config_id);
2104 FETCH check_unit_exists INTO l_dummy_varchar;
2105 IF (check_unit_exists%NOTFOUND)
2106 THEN
2107 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UA_UNIT_ID_NOT_FOUND');
2108 FND_MSG_PUB.ADD;
2109 CLOSE check_unit_exists;
2110 RAISE FND_API.G_EXC_ERROR;
2111 END IF;
2112 CLOSE check_unit_exists;
2113
2114 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2115 THEN
2116 fnd_log.string
2117 (
2118 fnd_log.level_statement,
2119 L_DEBUG_MODULE,
2120 'Basic validations done'
2121 );
2122 END IF;
2123
2124 -- Call AHL_UA_COMMON_PVT.Get_Prec_Event_Info to retrieve details of the previous event (visit / flight) and conflicts, if any
2125 AHL_UA_COMMON_PVT.Get_Prec_Event_Info
2126 (
2127 p_api_version => 1.0,
2128 x_return_status => l_return_status,
2129 x_msg_count => l_msg_count,
2130 x_msg_data => l_msg_data,
2131 p_unit_config_id => p_unit_config_id,
2132 p_start_date_time => p_start_date_time,
2133 p_use_actuals => G_USE_ACTUALS,
2134 x_prec_visit => x_prec_visit,
2135 x_prec_flight_schedule => x_prec_flight_schedule,
2136 x_is_conflict => x_is_prec_conflict,
2137 x_is_org_in_user_ou => x_is_prec_org_in_ou
2138 );
2139
2140 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2141 THEN
2142 fnd_log.string
2143 (
2144 fnd_log.level_statement,
2145 L_DEBUG_MODULE,
2146 'After calling AHL_UA_COMMON_PVT.Get_Prec_Event_Info [x_is_prec_conflict='||x_is_prec_conflict||']'
2147 );
2148 END IF;
2149
2150 -- Call AHL_UA_COMMON_PVT.Get_Succ_Event_Info to retrieve details of the next event (visit / flight) and conflicts, if any
2151 AHL_UA_COMMON_PVT.Get_Succ_Event_Info
2152 (
2153 p_api_version => 1.0,
2154 x_return_status => l_return_status,
2155 x_msg_count => l_msg_count,
2156 x_msg_data => l_msg_data,
2157 p_unit_config_id => p_unit_config_id,
2158 -- Pass p_end_date_time = p_start_date_time as sequence of all events will be based on start times
2159 -- The same logic is used in Get_All_Events too...
2160 p_end_date_time => p_start_date_time,
2161 p_use_actuals => G_USE_ACTUALS,
2162 x_succ_visit => x_succ_visit,
2163 x_succ_flight_schedule => x_succ_flight_schedule,
2164 x_is_conflict => x_is_succ_conflict,
2165 x_is_org_in_user_ou => x_is_succ_org_in_ou
2166 );
2167
2168 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2169 THEN
2170 fnd_log.string
2171 (
2172 fnd_log.level_statement,
2173 L_DEBUG_MODULE,
2174 'After calling AHL_UA_COMMON_PVT.Get_Succ_Event_Info [x_is_succ_conflict='||x_is_succ_conflict||']'
2175 );
2176 END IF;
2177 -- API body ends here
2178
2179 -- Log API exit point
2180 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2181 THEN
2182 fnd_log.string
2183 (
2184 fnd_log.level_procedure,
2185 L_DEBUG_MODULE||'.end',
2186 'At the end of PLSQL procedure'
2187 );
2188 END IF;
2189
2190 -- Check Error Message stack.
2191 x_msg_count := FND_MSG_PUB.count_msg;
2192 IF x_msg_count > 0
2193 THEN
2194 RAISE FND_API.G_EXC_ERROR;
2195 END IF;
2196
2197 -- Standard call to get message count and if count is 1, get message info
2198 FND_MSG_PUB.count_and_get
2199 (
2200 p_count => x_msg_count,
2201 p_data => x_msg_data,
2202 p_encoded => FND_API.G_FALSE
2203 );
2204
2205 EXCEPTION
2206 WHEN FND_API.G_EXC_ERROR THEN
2207 x_return_status := FND_API.G_RET_STS_ERROR;
2208 FND_MSG_PUB.count_and_get
2209 (
2210 p_count => x_msg_count,
2211 p_data => x_msg_data,
2212 p_encoded => FND_API.G_FALSE
2213 );
2214
2215 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2217 FND_MSG_PUB.count_and_get
2218 (
2219 p_count => x_msg_count,
2220 p_data => x_msg_data,
2221 p_encoded => FND_API.G_FALSE
2222 );
2223
2224 WHEN OTHERS THEN
2225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2226 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2227 THEN
2228 FND_MSG_PUB.add_exc_msg
2229 (
2230 p_pkg_name => G_PKG_NAME,
2231 p_procedure_name => 'Get_Prec_Succ_Event_Info',
2232 p_error_text => SUBSTR(SQLERRM,1,240)
2233 );
2234 END IF;
2235 FND_MSG_PUB.count_and_get
2236 (
2237 p_count => x_msg_count,
2238 p_data => x_msg_data,
2239 p_encoded => FND_API.G_FALSE
2240 );
2241 END Get_Prec_Succ_Event_Info;
2242
2243 -- Function to get the profile value for minimum number of minutes to considered as a
2244 -- Maintenance Opportunity
2245
2246 FUNCTION Get_Min_Time_MO RETURN NUMBER IS
2247
2248 l_min_time_mo NUMBER;
2249 l_min_time_mo_str VARCHAR2(30);
2250
2251 BEGIN
2252
2253 BEGIN
2254 l_min_time_mo_str := FND_PROFILE.VALUE('AHL_UA_MIN_MO_SIZE');
2255
2256 IF (l_min_time_mo_str IS NULL) THEN
2257 l_min_time_mo := 1;
2258 ELSIF (to_number(l_min_time_mo_str) <= 1 ) THEN
2259 l_min_time_mo := 1;
2260 ELSE
2261 l_min_time_mo := to_number(l_min_time_mo_str);
2262 END IF;
2263
2264 EXCEPTION
2265 WHEN VALUE_ERROR THEN
2266 l_min_time_mo := 1;
2267
2268 WHEN INVALID_NUMBER THEN
2269 l_min_time_mo := 1;
2270 END;
2271
2272 -- return date.
2273 RETURN l_min_time_mo;
2274
2275 END Get_Min_Time_MO;
2276
2277 END AHL_UA_UNIT_SCHEDULES_PVT;