[Home] [Help]
PACKAGE BODY: APPS.FTE_VEHICLE_PKG
Source
1 PACKAGE BODY FTE_VEHICLE_PKG AS
2 /* $Header: FTEVEHLB.pls 120.0 2005/05/26 18:07:02 appldev noship $ */
3
4
5 PROCEDURE VALIDATE_VEHICLE_TYPE
6 (
7 p_organization_name IN VARCHAR2,
8 p_status IN VARCHAR2,
9 p_weight_uom IN VARCHAR2,
10 p_volume_uom IN VARCHAR2,
11 p_dimension_uom IN VARCHAR2,
12 x_organization_id OUT NOCOPY NUMBER,
13 x_weight_uom_code OUT NOCOPY VARCHAR2,
14 x_volume_uom_code OUT NOCOPY VARCHAR2,
15 x_dimension_uom_code OUT NOCOPY VARCHAR2,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_error_table OUT NOCOPY STRINGARRAY
18 )
19 IS
20 i NUMBER := 0;
21 t_status VARCHAR2(10);
22 BEGIN
23
24 x_error_table := STRINGARRAY(NULL,NULL,NULL,NULL,NULL);
25 if (p_organization_name is not null)
26 then
27 BEGIN
28 SELECT ORGANIZATION_ID INTO x_organization_id
29 FROM HR_ORGANIZATION_UNITS
30 WHERE NAME = p_organization_name;
31 EXCEPTION
32 WHEN OTHERS THEN
33 i := i+1;
34 x_error_table(i) := fnd_message.get_string('FTE','FTE_VEH_ORG_INVALID');
35 END;
36 end if;
37
38 if (p_status is not null)
39 then
40 BEGIN
41 SELECT INVENTORY_ITEM_STATUS_CODE INTO t_status
42 FROM MTL_ITEM_STATUS
43 WHERE INVENTORY_ITEM_STATUS_CODE = p_status;
44 EXCEPTION
45 WHEN OTHERS THEN
46 i := i+1;
47 x_error_table(i) := fnd_message.get_string('FTE','FTE_VEH_STATUS_INVALID');
48 END;
49 end if;
50
51 -- Bug 4127713
52 -- Validate against the Weight UOM Class defined in Global Parameters
53 -- 1) If it's not defined, no UOM is acceptable
54 -- : current implementation to be consistent with LOV behavior
55 -- (java/util/webui/LovUOMCO.java)
56 -- 2) The alternative would be validating against all UOM Classes.
57 -- AND UOM_CLASS like (select gu_weight_class
58 -- from wsh_global_parameters
59 -- where rownum=1)||'%';
60 -- :The defect with this approach is if 'Weight' is set as the default
61 -- class and a non-used 'Weight***' class exists, any UOM belonging to
62 -- 'Weight***' can also be accepted.
63
64 if (p_weight_uom is not null)
65 then
66 BEGIN
67 SELECT UOM_CODE INTO x_weight_uom_code
68 FROM MTL_UNITS_OF_MEASURE_TL
69 WHERE UNIT_OF_MEASURE_TL = p_weight_uom
70 AND LANGUAGE = USERENV('LANG')
71 AND NVL(DISABLE_DATE, SYSDATE+1) > SYSDATE
72 AND UOM_CLASS = (select gu_weight_class
73 from wsh_global_parameters where rownum=1);
74
75 EXCEPTION
76 WHEN OTHERS THEN
77 i := i+1;
78 x_error_table(i) := fnd_message.get_string('FTE','FTE_VEH_WEIGHT_UOM_INVALID');
79 END;
80 end if;
81
82 if (p_volume_uom is not null)
83 then
84 BEGIN
85 SELECT UOM_CODE INTO x_volume_uom_code
86 FROM MTL_UNITS_OF_MEASURE_TL
87 WHERE UNIT_OF_MEASURE_TL = p_volume_uom
88 AND LANGUAGE = USERENV('LANG')
89 AND NVL(DISABLE_DATE, SYSDATE+1) > SYSDATE
90 AND UOM_CLASS = (select gu_volume_class
91 from wsh_global_parameters where rownum=1);
92 EXCEPTION
93 WHEN OTHERS THEN
94 i := i+1;
95 x_error_table(i) := fnd_message.get_string('FTE','FTE_VEH_VOL_UOM_INVALID');
96 END;
97 end if;
98
99 if (p_dimension_uom is not null)
100 then
101 BEGIN
102 SELECT UOM_CODE INTO x_dimension_uom_code
103 FROM MTL_UNITS_OF_MEASURE_TL
104 WHERE UNIT_OF_MEASURE_TL = p_dimension_uom
105 AND LANGUAGE = USERENV('LANG')
106 AND NVL(DISABLE_DATE, SYSDATE+1) > SYSDATE
107 AND UOM_CLASS = (select gu_dimension_class
108 from wsh_global_parameters where rownum=1);
109 EXCEPTION
110 WHEN OTHERS THEN
111 i := i+1;
112 x_error_table(i) := fnd_message.get_string('FTE','FTE_VEH_DIM_UOM_INVALID');
113 END;
114 end if;
115
116 if (i = 0)
117 then
118 x_return_status := 'S';
119 else
120 x_return_status := 'E';
121 end if;
122
123 END VALIDATE_VEHICLE_TYPE;
124
125 -- Procecure : CREATE_UPDATE_VEHICLE_TYPE (public API)
126 -- Purpose : Create or update a Vehicle Type
127 -- 1) Insert/Update FTE_VEHICLE_TYPES
128 -- 2) Insert/Update/Delete FTE_VEHICLE_FEATURES
129 -- 3) Call INV_ITEM_GRP.CREATE_ITEM/UPDATE_ITEM
130 -- to create/update an inventory master item
131
132 PROCEDURE CREATE_UPDATE_VEHICLE_TYPE
133 (
134 p_inventory_item_id IN NUMBER, /* MAIN PROPERTIES */
135 p_organization_id IN NUMBER,
136 p_organization_name IN VARCHAR2,
137 p_vehicle_type_id IN NUMBER,
138 p_vehicle_type_name IN VARCHAR2,
139 p_vehicle_class_code IN VARCHAR2,
140 p_status IN VARCHAR2,
141 p_description IN VARCHAR2,
142 p_weight_uom IN VARCHAR2, /* LOAD CAPACITIES */
143 p_maximum_load_weight IN NUMBER,
144 p_volume_uom IN VARCHAR2,
145 p_internal_volume IN NUMBER,
146 p_pallet_floor_space IN NUMBER,
147 p_pallet_stacking_height IN NUMBER,
148 p_ef_volume_cap_direct IN NUMBER,
149 p_ef_volume_cap_pool IN NUMBER,
150 p_ef_volume_cap_one_stop IN NUMBER,
151 p_ef_volume_cap_two_stop IN NUMBER,
152 p_ef_volume_cap_two_pool IN NUMBER,
153 p_ef_volume_cap_three_pool IN NUMBER,
154 p_tare_weight IN NUMBER, /* DIMENSIONS */
155 p_dimension_uom IN VARCHAR2,
156 p_exterior_length IN NUMBER,
157 p_exterior_width IN NUMBER,
158 p_exterior_height IN NUMBER,
159 p_usable_length IN NUMBER,
160 p_usable_width IN NUMBER,
161 p_usable_height IN NUMBER,
162 p_suspension_type_code IN VARCHAR2, /* FEATURES */
163 p_temperature_control_code IN VARCHAR2,
164 p_features_table IN STRINGARRAY, -- Database Type
165 p_number_of_doors IN NUMBER, /* DOORS */
166 p_door_height IN NUMBER,
167 p_door_width IN NUMBER,
168 p_attribute1 IN VARCHAR2,
169 p_attribute2 IN VARCHAR2,
170 p_attribute3 IN VARCHAR2,
171 p_attribute4 IN VARCHAR2,
172 p_attribute5 IN VARCHAR2,
173 p_attribute6 IN VARCHAR2,
174 p_attribute7 IN VARCHAR2,
175 p_attribute8 IN VARCHAR2,
176 p_attribute9 IN VARCHAR2,
177 p_attribute10 IN VARCHAR2,
178 p_attribute11 IN VARCHAR2,
179 p_attribute12 IN VARCHAR2,
180 p_attribute13 IN VARCHAR2,
181 p_attribute14 IN VARCHAR2,
182 p_attribute15 IN VARCHAR2,
183 x_return_status OUT NOCOPY VARCHAR2,
184 x_error_table OUT NOCOPY STRINGARRAY,
185 x_vehicle_type_id OUT NOCOPY NUMBER
186 )
187 IS
188 newItem INV_ITEM_GRP.Item_rec_type;
189 savedItem INV_ITEM_GRP.Item_rec_type;
190 errorTable INV_ITEM_GRP.Error_tbl_type;
191 errorRec INV_ITEM_GRP.Error_rec_type;
192 i NUMBER := 0;
193
194 --l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
195 l_debug_on CONSTANT BOOLEAN := TRUE;
196 l_module_name CONSTANT VARCHAR2(100) := 'FTE_VEHICLE_PKG';
197 l_inventory_item_id NUMBER;
198
199 BEGIN
200
201 x_error_table := STRINGARRAY(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
202
203 newItem.ITEM_NUMBER := p_vehicle_type_name;
204 newItem.DESCRIPTION := p_description;
205 newItem.MAXIMUM_LOAD_WEIGHT := p_maximum_load_weight;
206 newItem.INTERNAL_VOLUME := p_internal_volume;
207 newItem.UNIT_WEIGHT := p_tare_weight;
208 newItem.UNIT_LENGTH := p_exterior_length;
209 newItem.UNIT_WIDTH := p_exterior_width;
210 newItem.UNIT_HEIGHT := p_exterior_height;
211 newItem.ENABLED_FLAG := 'Y';
212 newItem.VEHICLE_ITEM_FLAG := 'Y';
213 newItem.ATTRIBUTE1 := p_attribute1;
214 newItem.ATTRIBUTE2 := p_attribute2;
215 newItem.ATTRIBUTE3 := p_attribute3;
216 newItem.ATTRIBUTE4 := p_attribute4;
217 newItem.ATTRIBUTE5 := p_attribute5;
218 newItem.ATTRIBUTE6 := p_attribute6;
219 newItem.ATTRIBUTE7 := p_attribute7;
220 newItem.ATTRIBUTE8 := p_attribute8;
221 newItem.ATTRIBUTE9 := p_attribute9;
222 newItem.ATTRIBUTE10 := p_attribute10;
223 newItem.ATTRIBUTE11 := p_attribute11;
224 newItem.ATTRIBUTE12 := p_attribute12;
225 newItem.ATTRIBUTE13 := p_attribute13;
226 newItem.ATTRIBUTE14 := p_attribute14;
227 newItem.ATTRIBUTE15 := p_attribute15;
228
229 IF l_debug_on THEN
230 WSH_DEBUG_SV.PUSH(l_module_name);
231 END IF;
232
233 VALIDATE_VEHICLE_TYPE(p_organization_name => p_organization_name,
234 p_status => p_status,
235 p_weight_uom => p_weight_uom,
236 p_volume_uom => p_volume_uom,
237 p_dimension_uom => p_dimension_uom,
238 x_organization_id => newItem.ORGANIZATION_ID,
239 x_weight_uom_code => newItem.WEIGHT_UOM_CODE,
240 x_volume_uom_code => newItem.VOLUME_UOM_CODE,
241 x_dimension_uom_code => newItem.DIMENSION_UOM_CODE,
242 x_return_status => x_return_status,
243 x_error_table => x_error_table);
244
245 IF l_debug_on THEN
246 WSH_DEBUG_SV.LOGMSG(l_module_name, 'ValidateVehicleType-'||x_return_status);
247 END IF;
248
249 if (x_return_status = 'E')
250 then
251 return;
252 end if;
253
254 SAVEPOINT Create_Update_Vehicle_Type;
255
256 newItem.INVENTORY_ITEM_STATUS_CODE := p_status;
257
258 -- CREATE
259 if (p_vehicle_type_id is null)
260 then
261
262 -- Bug 3268520
263 -- Begin : Check whether the given Name already exists
264 BEGIN
265 /*
266 SELECT item.inventory_item_id INTO l_inventory_item_id
267 FROM mtl_system_items_b_kfv item, fte_vehicle_types veh
268 WHERE item.concatenated_segments = p_vehicle_type_name
269 AND item.inventory_item_id = veh.inventory_item_id
270 AND item.organization_id = veh.organization_id;
271
272 -- This query is changed as follows
273 -- to avoid INDEX FULL SCAN on FTE_VEHICLE_TYPES_U1
274 */
275 SELECT veh.inventory_item_id INTO l_inventory_item_id
276 FROM fte_vehicle_types veh
277 WHERE (veh.inventory_item_id, veh.organization_id) =
278 (SELECT item.inventory_item_id, item.organization_id
279 FROM mtl_system_items_b_kfv item
280 WHERE item.concatenated_segments = p_vehicle_type_name
281 AND rownum < 2);
282
283 i := i+1;
284 x_return_status := 'E';
285 x_error_table(i) := fnd_message.get_string('FTE','FTE_VEH_NAME_UNIQUE_ERROR');
286 return;
287
288 EXCEPTION
289 WHEN NO_DATA_FOUND then
290 null;
291 WHEN TOO_MANY_ROWS then
292 i := i+1;
293 x_return_status := 'E';
294 x_error_table(i) := fnd_message.get_string('FTE','FTE_VEH_NAME_UNIQUE_ERROR');
295 return;
296 WHEN OTHERS THEN
297 i := i+1;
298 x_return_status := 'E';
299 x_error_table(i) := fnd_message.get_string('FTE','FTE_VEH_NAME_OTHER_ERROR2');
300 return;
301 END;
302 -- End : Check whether the given Name already exists
303
304 IF l_debug_on THEN
305 WSH_DEBUG_SV.LOGMSG(l_module_name, 'BeforeCreate');
306 END IF;
307 INV_ITEM_GRP.Create_Item(p_Item_rec => newItem,
308 x_Item_rec => savedItem,
309 x_return_status => x_return_status,
310 x_Error_tbl => errorTable);
311 IF l_debug_on THEN
312 WSH_DEBUG_SV.LOGMSG(l_module_name, 'AfterCreate-'||x_return_status);
313 END IF;
314
315 if (x_return_status = 'S')
316 then
317 INSERT INTO FTE_VEHICLE_TYPES
318 (VEHICLE_TYPE_ID, INVENTORY_ITEM_ID,
319 ORGANIZATION_ID, VEHICLE_CLASS_CODE,
320 PALLET_FLOOR_SPACE, PALLET_STACKING_HEIGHT,
321 EF_VOLUME_CAP_DIRECT, EF_VOLUME_CAP_POOL,
322 EF_VOLUME_CAP_ONE_STOP, EF_VOLUME_CAP_TWO_STOP,
323 EF_VOLUME_CAP_TWO_POOL, EF_VOLUME_CAP_THREE_POOL,
324 USABLE_LENGTH, USABLE_WIDTH, USABLE_HEIGHT,
325 SUSPENSION_TYPE_CODE, TEMPERATURE_CONTROL_CODE,
326 NUMBER_OF_DOORS, DOOR_HEIGHT, DOOR_WIDTH,
327 CREATION_DATE, CREATED_BY,
328 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
329 VALUES (FTE_VEHICLE_TYPES_S.NEXTVAL, savedItem.INVENTORY_ITEM_ID,
330 newItem.ORGANIZATION_ID, p_vehicle_class_code,
331 p_pallet_floor_space, p_pallet_stacking_height,
332 p_ef_volume_cap_direct, p_ef_volume_cap_pool,
333 p_ef_volume_cap_one_stop, p_ef_volume_cap_two_stop,
334 p_ef_volume_cap_two_pool, p_ef_volume_cap_three_pool,
335 p_usable_length, p_usable_width, p_usable_height,
336 p_suspension_type_code, p_temperature_control_code,
337 p_number_of_doors, p_door_height, p_door_width,
338 SYSDATE, FND_GLOBAL.USER_ID,
339 SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID)
340 RETURNING vehicle_type_id INTO x_vehicle_type_id;
341
342 IF l_debug_on THEN
343 WSH_DEBUG_SV.LOGMSG(l_module_name, 'AfterInsertType');
344 END IF;
345
346 FOR i IN 1..p_features_table.COUNT LOOP
347 INSERT INTO FTE_VEHICLE_FEATURES
348 (VEHICLE_TYPE_FEATURE_ID, VEHICLE_TYPE_ID,
349 VEHICLE_FEATURE_CODE,
350 CREATION_DATE, CREATED_BY,
351 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
352 VALUES (FTE_VEHICLE_FEATURES_S.NEXTVAL, x_vehicle_type_id,
353 p_features_table(i),
354 SYSDATE, FND_GLOBAL.USER_ID,
355 SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID);
356 END LOOP;
357 IF l_debug_on THEN
358 WSH_DEBUG_SV.LOGMSG(l_module_name, 'AfterInsertFeature');
359 END IF;
360
361 end if;
362 -- UPDATE
363 else
364 IF l_debug_on THEN
365 WSH_DEBUG_SV.LOGMSG(l_module_name, 'BeforeUpdate');
366 END IF;
367 newItem.INVENTORY_ITEM_ID := p_inventory_item_id;
368 INV_ITEM_GRP.Update_Item(p_Item_rec => newItem,
369 x_Item_rec => savedItem,
370 x_return_status => x_return_status,
371 x_Error_tbl => errorTable);
372 IF l_debug_on THEN
373 WSH_DEBUG_SV.LOGMSG(l_module_name, 'AfterUpdate-'||x_return_status);
374 END IF;
375 if (x_return_status = 'S')
376 then
377 UPDATE FTE_VEHICLE_TYPES
378 SET VEHICLE_CLASS_CODE = p_vehicle_class_code,
379 PALLET_FLOOR_SPACE = p_pallet_floor_space,
380 PALLET_STACKING_HEIGHT = p_pallet_stacking_height,
381 EF_VOLUME_CAP_DIRECT = p_ef_volume_cap_direct,
382 EF_VOLUME_CAP_POOL = p_ef_volume_cap_pool,
383 EF_VOLUME_CAP_ONE_STOP = p_ef_volume_cap_one_stop,
384 EF_VOLUME_CAP_TWO_STOP = p_ef_volume_cap_two_stop,
385 EF_VOLUME_CAP_TWO_POOL = p_ef_volume_cap_two_pool,
386 EF_VOLUME_CAP_THREE_POOL = p_ef_volume_cap_three_pool,
387 USABLE_LENGTH = p_usable_length,
388 USABLE_WIDTH = p_usable_width,
389 USABLE_HEIGHT = p_usable_height,
390 SUSPENSION_TYPE_CODE = p_suspension_type_code,
391 TEMPERATURE_CONTROL_CODE = p_temperature_control_code,
392 NUMBER_OF_DOORS = p_number_of_doors,
393 DOOR_HEIGHT = p_door_height,
394 DOOR_WIDTH = p_door_width,
395 LAST_UPDATE_DATE = SYSDATE,
396 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
397 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
398 WHERE VEHICLE_TYPE_ID = p_vehicle_type_id;
399 IF l_debug_on THEN
400 WSH_DEBUG_SV.LOGMSG(l_module_name, 'AfterInsertType');
401 END IF;
402
403 DELETE FTE_VEHICLE_FEATURES
404 WHERE VEHICLE_TYPE_ID = p_vehicle_type_id;
405 IF l_debug_on THEN
406 WSH_DEBUG_SV.LOGMSG(l_module_name, 'AfterDeleteFeature');
407 END IF;
408
409 FOR i IN 1..p_features_table.COUNT LOOP
410 INSERT INTO FTE_VEHICLE_FEATURES
411 (VEHICLE_TYPE_FEATURE_ID, VEHICLE_TYPE_ID,
412 VEHICLE_FEATURE_CODE,
413 CREATION_DATE, CREATED_BY,
414 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
415 VALUES (FTE_VEHICLE_FEATURES_S.NEXTVAL, p_vehicle_type_id,
416 p_features_table(i),
417 SYSDATE, FND_GLOBAL.USER_ID,
418 SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID);
419 END LOOP;
420 IF l_debug_on THEN
421 WSH_DEBUG_SV.LOGMSG(l_module_name, 'AfterInsertFeature-Update');
422 END IF;
423 end if;
424
425 end if;
426
427 FOR i IN 1..errorTable.COUNT LOOP
428 errorRec := errorTable(i);
429 x_error_table(i) := errorRec.message_text;
430 WSH_DEBUG_SV.LOGMSG(l_module_name, errorRec.message_text);
431 END LOOP;
432
433 IF l_debug_on THEN
434 WSH_DEBUG_SV.POP(l_module_name);
435 END IF;
436
437 IF (x_return_status <> 'S') THEN
438 ROLLBACK TO Create_Update_Vehicle_Type;
439 END IF;
440
441 EXCEPTION WHEN OTHERS THEN
442 x_return_status := 'E';
443 x_error_table(1) := SQLERRM;
444 IF l_debug_on THEN
445 WSH_DEBUG_SV.LOGMSG(l_module_name, 'Exception Others'||SQLERRM);
446 WSH_DEBUG_SV.POP(l_module_name);
447 END IF;
448 ROLLBACK TO Create_Update_Vehicle_Type;
449 END CREATE_UPDATE_VEHICLE_TYPE;
450
451 PROCEDURE UPGRADE_ITEMS
452 ( x_return_status OUT NOCOPY VARCHAR2,
453 x_error_message OUT NOCOPY VARCHAR2)
454 IS
455
456 CURSOR item_cur IS
457 SELECT inventory_item_id, organization_id
458 FROM mtl_system_items
459 WHERE organization_id in (SELECT distinct master_organization_id
460 FROM mtl_parameters)
461 AND vehicle_item_flag = 'Y'
462 AND inventory_item_id not in (SELECT inventory_item_id
463 FROM fte_vehicle_types);
464 BEGIN
465
466 FOR item_cur_rec IN item_cur
467 LOOP
468 INSERT INTO FTE_VEHICLE_TYPES
469 (VEHICLE_TYPE_ID, INVENTORY_ITEM_ID,
470 ORGANIZATION_ID, CREATION_DATE, CREATED_BY,
471 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
472 VALUES (FTE_VEHICLE_TYPES_S.NEXTVAL, item_cur_rec.inventory_item_id,
473 item_cur_rec.organization_id, SYSDATE, FND_GLOBAL.USER_ID,
474 SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID);
475 END LOOP;
476
477 EXCEPTION WHEN OTHERS THEN
478 x_return_status := 'E';
479 x_error_message := SQLERRM;
480 rollback;
481 END UPGRADE_ITEMS;
482
483 FUNCTION GET_VEHICLE_TYPE_ID
484 ( p_inventory_item_id IN NUMBER ) RETURN NUMBER
485 IS
486 x_vehicle_type_id NUMBER;
487 BEGIN
488
489 SELECT vehicle_type_id INTO x_vehicle_type_id
490 FROM fte_vehicle_types
491 WHERE inventory_item_id = p_inventory_item_id;
492
493 return x_vehicle_type_id;
494
495 EXCEPTION
496 WHEN TOO_MANY_ROWS THEN
497 return x_vehicle_type_id;
498 WHEN OTHERS THEN
499 return -1;
500 END GET_VEHICLE_TYPE_ID;
501
502 -- Function : get_vehicle_org_id
503 -- Purpose : Get the vehicle org id for a given inventory item id
504 -- from fte_vehicle_types
505
506 FUNCTION GET_VEHICLE_ORG_ID
507 ( p_inventory_item_id IN NUMBER ) RETURN NUMBER
508 IS
509 x_vehicle_org_id NUMBER;
510
511 CURSOR c_get_veh_org IS
512 SELECT organization_id
513 FROM fte_vehicle_types
514 WHERE inventory_item_id = p_inventory_item_id
515 AND rownum=1;
516
517 BEGIN
518
519 OPEN c_get_veh_org;
520 FETCH c_get_veh_org INTO x_vehicle_org_id;
521 IF c_get_veh_org%NOTFOUND THEN
522 x_vehicle_org_id:=-1;
523 END IF;
524 CLOSE c_get_veh_org;
525
526 return x_vehicle_org_id;
527
528 EXCEPTION
529 WHEN OTHERS THEN
530 return -1;
531 END GET_VEHICLE_ORG_ID;
532
533 END FTE_VEHICLE_PKG;