1 PACKAGE BODY FTE_CAT_VALIDATE_PKG AS
2 /* $Header: FTECATVB.pls 115.7 2002/11/21 00:22:09 hbhagava noship $ */
3
4 --
5 -- Package
6 -- FTE_CAT_VALIDATE_PKG
7 --
8 -- Purpose
9 -- This package does the following:
10 -- 1. Validate a stop location belongs to origin/destination region
11
12 -- of a lane/schedule
13 -- 2. Validate stop times (departure and arrival dates )
14 -- against lane/schedule
15 -- 3. Validate Service type of a Lane
16 --
17 G_PACKAGE_NAME CONSTANT VARCHAR2(50) := 'FTE_CAT_VALIDATE_PKG';
18
19 --
20 -- Defined Constant Variables in the specs
21 -- 1 - Departure Date is Match with Departure Date - G_MATCH_WITH_DEP_DATE
22 -- 2 - Departure Date is Match with Arrival Date - G_MATCH_WITH_ARR_DATE
23 -- 3 - Departure Date is between Departure and Arrival Dates - G_BETWEEN_DATES
24
25 -- 4 - Departure Date is Outside the Range ( before ). -G_BEFORE_DEP_DATE
26 -- 5 - Departure Date is Outside the Range ( after). - G_AFTER_ARRIVAL_DATE
27 --
28 --========================================================================
29 -- Procedure Name
30 --
31 -- PROCEDURE Validate_Loc_To_Region
32 --========================================================================
33 --
34 -- Purpose
35 -- Validate a stop location belongs to origin/destination region
36
37 -- of a lane/schedule
38 --
39 -- IN Parameters
40 -- 1. Lane Id
41 -- 2. Location Id
42 -- 3. Search Criteria
43 -- Valid Values are 'O'-Origin, 'D'-Destination, and 'A'-Any One
44 -- This input criteria will be used to validate only those region
45 -- 4. p_init_msg_list (optional, default FND_API.G_FALSE)
46 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
47 -- if set to FND_API.G_TRUE
48 -- initialize error message list
49 -- if set to FND_API.G_FALSE - not initialize error
50 -- message list
51
52 -- Out Parameters
53 -- 1.x_return_status
54 -- if the process succeeds, the value is
55 -- fnd_api.g_ret_sts_success;
56 -- if there is an expected error, the value is
57 -- fnd_api.g_ret_sts_error;
58 -- if there is an unexpected error, the value is
59 -- fnd_api.g_ret_sts_unexp_error;
60
61
62 -- 2. x_valid_flag :returns "Y" or "N"
63 -- Y-means entered location is valid, otherwise "N"
64
65 PROCEDURE Validate_Loc_To_Region(
66 p_lane_id IN NUMBER,
67 p_location_id IN NUMBER,
68 p_search_criteria IN VARCHAR2 default 'A',
69 p_init_msg_list IN VARCHAR2 default fnd_api.g_false,
70 x_return_status OUT NOCOPY VARCHAR2,
71 x_valid_flag OUT NOCOPY VARCHAR2
72 ) is
73
74
75 cursor c_origin_exists_cursor is
76 select 'Y'
77 from fte_lanes fl,
78 wsh_regions_tl wr,
79 hz_locations hl,
80 wsh_regions wrb
81 where fl.origin_id = wr.region_id
82 and wr.region_id = wrb.region_id
83 and fl.lane_id = p_lane_id
84 and hl.location_id = p_location_id
85 and nvl(wr.city,'XX') = decode(wr.city, NULL, 'XX', nvl(hl.city,wr.city))
86 and nvl(wrb.country_code,'XX') = decode(wrb.country_code, NULL, 'XX', nvl(hl.country,wrb.country_code))
87 and nvl(wr.postal_code_from,'99') <= decode(wr.postal_code_from, NULL, '99',nvl(hl.postal_code,wr.postal_code_from))
88 and nvl(wr.postal_code_to,'99') <= decode(wr.postal_code_to, NULL, '99',nvl(hl.postal_code,wr.postal_code_to))
89 and nvl(wrb.state_code,'XX') = nvl(hl.state,wrb.state_code)
90 union
91 select 'Y'
92 from fte_lanes fl,
93 wsh_regions_tl wr,
94 hr_locations_all hl, hr_locations_all_tl hlt,
95 wsh_regions wrb
96 where fl.origin_id = wr.region_id
97 and wr.region_id = wrb.region_id
98 and fl.lane_id = p_lane_id
99 and hl.location_id = p_location_id
100 and nvl(wr.city,'XX') = decode(wr.city, NULL, 'XX', nvl(hl.town_or_city,wr.city))
101 and nvl(wrb.country_code,'XX') = decode(wrb.country_code, NULL, 'XX', nvl(hl.country,wrb.country_code))
102 and nvl(wr.postal_code_from,'99') <= decode(wr.postal_code_from, NULL, '99',nvl(hl.postal_code,wr.postal_code_from))
103 and nvl(wr.postal_code_to,'99') <= decode(wr.postal_code_to, NULL, '99',nvl(hl.postal_code,wr.postal_code_to))
104 and nvl(wrb.state_code,'XX') = nvl(hl.region_2,wrb.state_code)
105 and nvl (hl.business_group_id,nvl(hr_general.get_business_group_id, -99) )
106 = nvl (hr_general.get_business_group_id, -99)
107 and hl.location_id = hlt.location_id and hlt.language = userenv('LANG')
108 ;
109
110
111
112 cursor c_dest_exists_cursor is
113 select 'Y'
114 from fte_lanes fl,
115 wsh_regions_tl wr,
116 hz_locations hl,
117 wsh_regions wrb
118 where fl.destination_id = wr.region_id
119 and wr.region_id = wrb.region_id
120 and fl.lane_id = p_lane_id
121 and hl.location_id = p_location_id
122 and nvl(wr.city,'XX') = decode(wr.city, NULL, 'XX', nvl(hl.city,wr.city))
123 and nvl(wrb.country_code,'XX') = decode(wrb.country_code, NULL, 'XX', nvl(hl.country,wrb.country_code))
124 and nvl(wr.postal_code_from,'99') <= decode(wr.postal_code_from, NULL, '99',nvl(hl.postal_code,wr.postal_code_from))
125 and nvl(wr.postal_code_to,'99') <= decode(wr.postal_code_to, NULL, '99',nvl(hl.postal_code,wr.postal_code_to))
126 and nvl(wrb.state_code,'XX') = nvl(hl.state,wrb.state_code)
127 union
128 select 'Y'
129 from fte_lanes fl,
130 wsh_regions_tl wr,
131 hr_locations_all hl, hr_locations_all_tl hlt,
132 wsh_regions wrb
133 where fl.destination_id = wr.region_id
134 and wr.region_id = wrb.region_id
135 and fl.lane_id = p_lane_id
136 and hl.location_id = p_location_id
137 and nvl(wr.city,'XX') = decode(wr.city, NULL, 'XX', nvl(hl.town_or_city,wr.city))
138 and nvl(wrb.country_code,'XX') = decode(wrb.country_code, NULL, 'XX', nvl(hl.country,wrb.country_code))
139 and nvl(wr.postal_code_from,'99') <= decode(wr.postal_code_from, NULL, '99',nvl(hl.postal_code,wr.postal_code_from))
140 and nvl(wr.postal_code_to,'99') <= decode(wr.postal_code_to, NULL, '99',nvl(hl.postal_code,wr.postal_code_to))
141 and nvl(wrb.state_code,'XX') = nvl(hl.region_2,wrb.state_code)
142 and nvl (hl.business_group_id,nvl(hr_general.get_business_group_id, -99) )
143 = nvl (hr_general.get_business_group_id, -99)
144 and hl.location_id = hlt.location_id and hlt.language = userenv('LANG')
145 ;
146
147
148 cursor c_any_exists_cursor is
149 select 'Y'
150 from fte_lanes fl,
151 wsh_regions_tl wr,
152 hz_locations hl,
153 wsh_regions wrb
154 where fl.destination_id = wr.region_id
155 and wr.region_id = wrb.region_id
156 and fl.lane_id = p_lane_id
157 and hl.location_id = p_location_id
158 and nvl(wr.city,'XX') = decode(wr.city, NULL, 'XX', nvl(hl.city,wr.city))
159 and nvl(wrb.country_code,'XX') = decode(wrb.country_code, NULL, 'XX', nvl(hl.country,wrb.country_code))
160 and nvl(wr.postal_code_from,'99') <= decode(wr.postal_code_from, NULL, '99',nvl(hl.postal_code,wr.postal_code_from))
161 and nvl(wr.postal_code_to,'99') <= decode(wr.postal_code_to, NULL, '99',nvl(hl.postal_code,wr.postal_code_to))
162 and nvl(wrb.state_code,'XX') = nvl(hl.state,wrb.state_code)
163 union
164 select 'Y'
165 from fte_lanes fl,
166 wsh_regions_tl wr,
167 hr_locations_all hl, hr_locations_all_tl hlt,
168 wsh_regions wrb
169 where (fl.origin_id = wr.region_id or fl.DESTINATION_ID = wr.region_id)
170 and wr.region_id = wrb.region_id
171 and fl.lane_id = p_lane_id
172 and hl.location_id = p_location_id
173 and nvl(wr.city,'XX') = decode(wr.city, NULL, 'XX', nvl(hl.town_or_city,wr.city))
174 and nvl(wrb.country_code,'XX') = decode(wrb.country_code, NULL, 'XX', nvl(hl.country,wrb.country_code))
175 and nvl(wr.postal_code_from,'99') <= decode(wr.postal_code_from, NULL, '99',nvl(hl.postal_code,wr.postal_code_from))
176 and nvl(wr.postal_code_to,'99') <= decode(wr.postal_code_to, NULL, '99',nvl(hl.postal_code,wr.postal_code_to))
177 and nvl(wrb.state_code,'XX') = nvl(hl.region_2,wrb.state_code)
178 and nvl (hl.business_group_id,nvl(hr_general.get_business_group_id, -99) )
179 = nvl (hr_general.get_business_group_id, -99)
180 and hl.location_id = hlt.location_id and hlt.language = userenv('LANG')
181 ;
182
183
184 --
185
186 begin
187 SAVEPOINT Validate_Loc_To_Region;
188 -- Initialize message list if p_init_msg_list is set to TRUE.
189 --
190 IF FND_API.to_Boolean( p_init_msg_list ) THEN
191 FND_MSG_PUB.initialize;
192 END IF;
193 -- Initialize API return status to success
194 x_return_status := FND_API.G_RET_STS_SUCCESS;
195 x_valid_flag := 'Y';
196 -- Validate Region
197 if p_search_criteria = 'O' then
198 open c_origin_exists_cursor;
199 fetch c_origin_exists_cursor into x_valid_flag;
200 if c_origin_exists_cursor%NOTFOUND then
201 close c_origin_exists_cursor;
202 x_valid_flag := 'N';
203 end if;
204 if c_origin_exists_cursor%ISOPEN then
205 close c_origin_exists_cursor;
206 end if;
207 elsif p_search_criteria = 'D' then
208 open c_dest_exists_cursor;
209 fetch c_dest_exists_cursor into x_valid_flag;
210 if c_dest_exists_cursor%NOTFOUND then
211 close c_dest_exists_cursor;
212 x_valid_flag := 'N';
213 end if;
214 if c_dest_exists_cursor%ISOPEN then
215 close c_dest_exists_cursor;
216 end if;
217 elsif p_search_criteria = 'A' then
218 open c_any_exists_cursor;
219 fetch c_any_exists_cursor into x_valid_flag;
220 if c_any_exists_cursor%NOTFOUND then
221 close c_any_exists_cursor;
222 x_valid_flag := 'N';
223 end if;
224 if c_any_exists_cursor%ISOPEN then
225 close c_any_exists_cursor;
226 end if;
227 end if;
228 EXCEPTION
229 WHEN FND_API.G_EXC_ERROR THEN
230 if c_origin_exists_cursor%isopen then
231 close c_origin_exists_cursor;
232 end if;
233 if c_dest_exists_cursor%isopen then
234 close c_dest_exists_cursor;
235 end if;
236 if c_any_exists_cursor%isopen then
237 close c_any_exists_cursor;
238 end if;
239 x_valid_flag := 'N';
240 ROLLBACK TO Validate_Loc_To_Region;
241 x_return_status := FND_API.G_RET_STS_ERROR;
242 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
243 if c_origin_exists_cursor%isopen then
244 close c_origin_exists_cursor;
245 end if;
246 if c_dest_exists_cursor%isopen then
247 close c_dest_exists_cursor;
248 end if;
249 if c_any_exists_cursor%isopen then
250 close c_any_exists_cursor;
251 end if;
252 x_valid_flag := 'N';
253 ROLLBACK TO Validate_Loc_To_Region;
254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
255 WHEN OTHERS THEN
256 if c_origin_exists_cursor%isopen then
257 close c_origin_exists_cursor;
258 end if;
259 if c_dest_exists_cursor%isopen then
260 close c_dest_exists_cursor;
261 end if;
262 if c_any_exists_cursor%isopen then
263 close c_any_exists_cursor;
264 end if;
265 x_valid_flag := 'N';
266 ROLLBACK TO Validate_Loc_To_Region;
267 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
268 END Validate_Loc_To_Region ;
269 --
270 --========================================================================
271 -- Procdure Name
272 --
273 -- PROCEDURE Validate_Schedule_Date
274 --========================================================================
275 --
276 -- Purpose
277
278 -- Validate stop times (departure and arrival dates )
279 -- against lane/schedule
280 --
281 -- IN Parameters
282 -- 1. Lane Id
283 -- 2. Schedule Id
284 -- 3. Departure Date
285 -- 4. Arrival Date
286 -- 5. p_init_msg_list (optional, default FND_API.G_FALSE)
287 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
288 -- if set to FND_API.G_TRUE
289 -- initialize error message list
290 -- if set to FND_API.G_FALSE - not initialize error
291 -- message list
292 -- Out Parameters
293 -- 1.x_return_status
294 -- if the process succeeds, the value is
295 -- fnd_api.g_ret_sts_success;
296 -- if there is an expected error, the value is
297 -- fnd_api.g_ret_sts_error;
298 -- if there is an unexpected error, the value is
299 -- fnd_api.g_ret_sts_unexp_error;
300
301
302 -- 2. x_dep_match_flag : returns the following Values
303 -- 1 - Departure Date is Match with Departure Date - MATCH_WITH_DEP_DATE
304
305 -- 2 - Departure Date is Match with Arrival Date - MATCH_WITH_ARR_DATE
306
307
308 -- 3 - Departure Date is between Departure and Arrival Dates - BETWEEN_DATES
309
310 -- 4 - Departure Date is Outside the Range ( before ). -BEFORE_DEP_DATE
311
312
313 -- 5 - Departure Date is Outside the Range ( after). - AFTER_ARRIVAL_DATE
314
315 -- 3. x_arr_match_flag : returns the following Values
316 -- 1 - Arrival Date is Match with Departure Date
317 -- 2 - Arrival Date is Match with Arrival Date
318 -- 3 - Arrival Date is between Departure and Arrival Dates
319 -- 4 - Arrival Date is Outside the Range ( before ).
320 -- 5 - Arrival Date is Outside the Range ( after ).
321
322 PROCEDURE Validate_Schedule_Date(
323 p_lane_id IN NUMBER,
324 p_schedule_id IN NUMBER,
325
326 p_departure_date IN DATE,
327 p_arrival_date IN DATE,
328 p_init_msg_list IN VARCHAR2 default fnd_api.g_false,
329 x_return_status OUT NOCOPY VARCHAR2,
330 x_dep_match_flag OUT NOCOPY NUMBER,
331 x_arr_match_flag OUT NOCOPY NUMBER
332 ) is
333 l_departure_date date;
334 l_arrival_date date;
335 cursor c_schedule_cursor is
336 select departure_date, arrival_date from fte_schedules
337 where lane_id = p_lane_id and schedules_id = p_schedule_id
338 and departure_date is not null and arrival_date is not null;
339
340 --
341 begin
342 SAVEPOINT Validate_Schedule_Date;
343 -- Initialize message list if p_init_msg_list is set to TRUE.
344 --
345 IF FND_API.to_Boolean( p_init_msg_list ) THEN
346 FND_MSG_PUB.initialize;
347 END IF;
348 -- Initialize API return status to success
349 x_return_status := FND_API.G_RET_STS_SUCCESS;
350
351 open c_schedule_cursor;
352 fetch c_schedule_cursor into l_departure_date, l_arrival_date;
353
354 if c_schedule_cursor%notfound then
355 close c_schedule_cursor;
356 raise no_data_found;
357 end if;
358 --
359 if p_departure_date = l_departure_date then
360 x_dep_match_flag := G_MATCH_WITH_DEP_DATE;
361 end if;
362 if p_departure_date = l_arrival_date then
363 x_dep_match_flag := G_MATCH_WITH_ARR_DATE ;
364 end if;
365 if p_departure_date > l_departure_date and
366 p_departure_date < l_arrival_date then
367
368 x_dep_match_flag := G_BETWEEN_DATES ;
369 end if;
373 if p_departure_date > l_arrival_date then
370 if p_departure_date < l_departure_date then
371 x_dep_match_flag := G_BEFORE_DEP_DATE ;
372 end if;
374 x_dep_match_flag := G_AFTER_ARRIVAL_DATE ;
375 end if;
376 --
377 if p_arrival_date = l_departure_date then
378 x_arr_match_flag := G_MATCH_WITH_DEP_DATE;
379 end if;
380 if p_arrival_date = l_arrival_date then
381
382 x_arr_match_flag := G_MATCH_WITH_ARR_DATE ;
383 end if;
384 if p_arrival_date > l_departure_date and
385 p_arrival_date < l_arrival_date then
386 x_arr_match_flag := G_BETWEEN_DATES ;
387 end if;
388 if p_arrival_date < l_departure_date then
389 x_arr_match_flag := G_BEFORE_DEP_DATE ;
390 end if;
391 if p_arrival_date > l_arrival_date then
392 x_arr_match_flag := G_AFTER_ARRIVAL_DATE ;
393 end if;
394 if c_schedule_cursor%isopen then
395
396 close c_schedule_cursor;
397 end if;
398 --
399 EXCEPTION
400 WHEN FND_API.G_EXC_ERROR THEN
401 if c_schedule_cursor%isopen then
402 close c_schedule_cursor;
403 end if;
404 ROLLBACK TO Validate_Schedule_Date;
405 x_return_status := FND_API.G_RET_STS_ERROR;
406 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
407 if c_schedule_cursor%isopen then
408 close c_schedule_cursor;
409
410 end if;
414 if c_schedule_cursor%isopen then
411 ROLLBACK TO Validate_Schedule_Date;
412 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
413 WHEN OTHERS THEN
415 close c_schedule_cursor;
416 end if;
417 ROLLBACK TO Validate_Schedule_Date;
418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
419 END Validate_Schedule_Date;
420 --
421 --========================================================================
422 -- Procdure Name
423
424 --
425 -- PROCEDURE Validate_Service_Type
426 --========================================================================
427 --
428 -- Purpose
429 -- Validate Service type of a Lane
430 --
431 -- IN Parameters
432 -- 1. Lane Id
433 -- 2. Service Type
434 -- Out Parameters
435 -- 1. x_valid_flag :returns "Y" or "N"
436 -- Y-means entered service type is valid, otherwise "N"
437
438
439 PROCEDURE Validate_Service_Type(
440 p_lane_id IN NUMBER,
441 p_service_type IN VARCHAR2,
442 p_init_msg_list IN VARCHAR2 default fnd_api.g_false,
443 x_return_status OUT NOCOPY VARCHAR2,
444 x_valid_flag OUT NOCOPY VARCHAR2
445 ) is
446 cursor c_service_type is
447 select 'Y' from fte_lane_services where service_code = p_service_type and lane_id = p_lane_id;
448
449 begin
450
451 SAVEPOINT Validate_Service_Type;
452 -- Initialize message list if p_init_msg_list is set to TRUE.
453 --
454 IF FND_API.to_Boolean( p_init_msg_list ) THEN
455 FND_MSG_PUB.initialize;
456 END IF;
457 -- Initialize API return status to success
458 x_return_status := FND_API.G_RET_STS_SUCCESS;
459 x_valid_flag := 'Y';
460 -- Validate service
461 open c_service_type;
462 fetch c_service_type into x_valid_flag;
463 if c_service_type%NOTFOUND then
464
465 close c_service_type;
466 x_valid_flag := 'N';
467 end if;
468 if c_service_type%ISOPEN then
469 close c_service_type;
470 end if;
471 EXCEPTION
472 WHEN FND_API.G_EXC_ERROR THEN
473 if c_service_type%isopen then
474 close c_service_type;
475 x_valid_flag := 'N';
476 end if;
477 ROLLBACK TO Validate_Service_Type;
478
479 x_return_status := FND_API.G_RET_STS_ERROR;
480 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
481 if c_service_type%isopen then
482 close c_service_type;
483 x_valid_flag := 'N';
484 end if;
485 ROLLBACK TO Validate_Service_Type;
486 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
487 WHEN OTHERS THEN
488 if c_service_type%isopen then
489 close c_service_type;
490 x_valid_flag := 'N';
491 end if;
492
493 ROLLBACK TO Validate_Service_Type;
494 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
495 END Validate_Service_Type;
496 --
497 END FTE_CAT_VALIDATE_PKG;
[Home] [Help]