1 PACKAGE FTE_VEHICLE_PKG AUTHID CURRENT_USER AS
2 /* $Header: FTEVEHLS.pls 115.3 2004/02/25 02:41:25 ttrichy ship $ */
3
4 -- Procecure : Create_Update_Vehicle_Type
5 -- Purpose : Create or update a Vehicle Type
6 -- 1) Insert/Update FTE_VEHICLE_TYPES
7 -- 2) Insert/Update/Delete FTE_VEHICLE_FEATURES
8 -- 3) Call INV_ITEM_GRP.CREATE_ITEM/UPDATE_ITEM
9 -- to create/update an inventory master item
10
11 PROCEDURE Create_Update_Vehicle_Type
12 (
13 p_inventory_item_id IN NUMBER, /* MAIN PROPERTIES */
14 p_organization_id IN NUMBER,
15 p_organization_name IN VARCHAR2,
16 p_vehicle_type_id IN NUMBER,
17 p_vehicle_type_name IN VARCHAR2,
18 p_vehicle_class_code IN VARCHAR2,
19 p_status IN VARCHAR2,
20 p_description IN VARCHAR2,
21 p_weight_uom IN VARCHAR2, /* LOAD CAPACITIES */
22 p_maximum_load_weight IN NUMBER,
23 p_volume_uom IN VARCHAR2,
24 p_internal_volume IN NUMBER,
25 p_pallet_floor_space IN NUMBER,
26 p_pallet_stacking_height IN NUMBER,
27 p_ef_volume_cap_direct IN NUMBER,
28 p_ef_volume_cap_pool IN NUMBER,
29 p_ef_volume_cap_one_stop IN NUMBER,
30 p_ef_volume_cap_two_stop IN NUMBER,
31 p_ef_volume_cap_two_pool IN NUMBER,
32 p_ef_volume_cap_three_pool IN NUMBER,
33 p_tare_weight IN NUMBER, /* DIMENSIONS */
34 p_dimension_uom IN VARCHAR2,
35 p_exterior_length IN NUMBER,
36 p_exterior_width IN NUMBER,
37 p_exterior_height IN NUMBER,
38 p_usable_length IN NUMBER,
39 p_usable_width IN NUMBER,
40 p_usable_height IN NUMBER,
41 p_suspension_type_code IN VARCHAR2, /* FEATURES */
42 p_temperature_control_code IN VARCHAR2,
43 p_features_table IN STRINGARRAY, -- Databae Type
44 p_number_of_doors IN NUMBER, /* DOORS */
45 p_door_height IN NUMBER,
46 p_door_width IN NUMBER,
47 p_attribute1 IN VARCHAR2,
48 p_attribute2 IN VARCHAR2,
49 p_attribute3 IN VARCHAR2,
50 p_attribute4 IN VARCHAR2,
51 p_attribute5 IN VARCHAR2,
52 p_attribute6 IN VARCHAR2,
53 p_attribute7 IN VARCHAR2,
54 p_attribute8 IN VARCHAR2,
55 p_attribute9 IN VARCHAR2,
56 p_attribute10 IN VARCHAR2,
57 p_attribute11 IN VARCHAR2,
58 p_attribute12 IN VARCHAR2,
59 p_attribute13 IN VARCHAR2,
60 p_attribute14 IN VARCHAR2,
61 p_attribute15 IN VARCHAR2,
62 x_return_status OUT NOCOPY VARCHAR2,
63 x_error_table OUT NOCOPY STRINGARRAY,
64 x_vehicle_type_id OUT NOCOPY NUMBER
65 );
66
67 PROCEDURE VALIDATE_VEHICLE_TYPE
68 (
69 p_organization_name IN VARCHAR2,
70 p_status IN VARCHAR2,
71 p_weight_uom IN VARCHAR2,
72 p_volume_uom IN VARCHAR2,
73 p_dimension_uom IN VARCHAR2,
74 x_organization_id OUT NOCOPY NUMBER,
75 x_weight_uom_code OUT NOCOPY VARCHAR2,
76 x_volume_uom_code OUT NOCOPY VARCHAR2,
77 x_dimension_uom_code OUT NOCOPY VARCHAR2,
78 x_return_status OUT NOCOPY VARCHAR2,
79 x_error_table OUT NOCOPY STRINGARRAY
80 );
81
82 -- Procecure : Upgrade_Items
83 -- Purpose : Upgrade Inventory Master Items of type Vehicle to Vehicle Types
84 -- 1) Select from MTL_SYSTEM_ITEMS
85 -- 2) Insert into FTE_VEHICLE_TYPES
86
87 PROCEDURE Upgrade_Items
88 ( x_return_status OUT NOCOPY VARCHAR2,
89 x_error_message OUT NOCOPY VARCHAR2
90 );
91
92 -- Function : Get_Vehicle_Type_Id
93 -- Purpose : Convert the Inventory Item Id into Vehicle Type Id
94 -- If there's more than one Vehicle Types matching the given
95 -- the Inventory Item Id, the first one will be returned.
96 FUNCTION GET_VEHICLE_TYPE_ID
97 ( p_inventory_item_id IN NUMBER ) RETURN NUMBER;
98
99 -- Function : get_vehicle_org_id
100 -- Purpose : Get the vehicle org id for a given inventory item id
101 -- from fte_vehicle_types
102
103 FUNCTION GET_VEHICLE_ORG_ID
104 ( p_inventory_item_id IN NUMBER ) RETURN NUMBER;
105
106 END FTE_VEHICLE_PKG;