1 PACKAGE BODY pqp_pl_vehicle_repository AS
2 /* $Header: pqplvrep.pkb 120.1 2006/09/13 13:18:38 mseshadr noship $ */
3 g_package VARCHAR2(30);
4 --
5 --Start of create_pl_vehicle
6 PROCEDURE create_pl_vehicle(P_EFFECTIVE_DATE in DATE
7 ,P_REGISTRATION_NUMBER in VARCHAR2
8 ,P_VEHICLE_TYPE in VARCHAR2
9 ,P_VEHICLE_ID_NUMBER in VARCHAR2
10 ,P_BUSINESS_GROUP_ID in NUMBER
11 ,P_MAKE in VARCHAR2
12 ,P_ENGINE_CAPACITY_IN_CC in NUMBER
13 ,P_FUEL_TYPE in VARCHAR2
14 ,P_CURRENCY_CODE in VARCHAR2
15 ,P_VEHICLE_STATUS in VARCHAR2
16 ,P_VEHICLE_INACTIVITY_REASON in VARCHAR2
17 ,P_MODEL in VARCHAR2
18 ,P_INITIAL_REGISTRATION in DATE
19 ,P_LAST_REGISTRATION_RENEW_DATE in DATE
20 ,P_LIST_PRICE in NUMBER
21 ,P_ACCESSORY_VALUE_AT_STARTDATE in NUMBER
22 ,P_ACCESSORY_VALUE_ADDED_LATER in NUMBER
23 ,P_MARKET_VALUE_CLASSIC_CAR in NUMBER
24 ,P_FISCAL_RATINGS in NUMBER
25 ,P_FISCAL_RATINGS_UOM in VARCHAR2
26 ,P_VEHICLE_PROVIDER in VARCHAR2
27 ,P_VEHICLE_OWNERSHIP in VARCHAR2
28 ,P_SHARED_VEHICLE in VARCHAR2
29 ,P_ASSET_NUMBER in VARCHAR2
30 ,P_LEASE_CONTRACT_NUMBER in VARCHAR2
31 ,P_LEASE_CONTRACT_EXPIRY_DATE in DATE
32 ,P_TAXATION_METHOD in VARCHAR2
33 ,P_FLEET_INFO in VARCHAR2
34 ,P_FLEET_TRANSFER_DATE in DATE
35 ,P_COLOR in VARCHAR2
36 ,P_SEATING_CAPACITY in NUMBER
37 ,P_WEIGHT in NUMBER
38 ,P_WEIGHT_UOM in VARCHAR2
39 ,P_MODEL_YEAR in NUMBER
40 ,P_INSURANCE_NUMBER in VARCHAR2
41 ,P_INSURANCE_EXPIRY_DATE in DATE
42 ,P_COMMENTS in VARCHAR2
43 ,P_VRE_ATTRIBUTE_CATEGORY in VARCHAR2
44 ,P_VRE_INFORMATION_CATEGORY in VARCHAR2
45 ,P_VRE_INFORMATION1 in VARCHAR2
46 ,P_VRE_INFORMATION2 in VARCHAR2
47 ,P_VRE_INFORMATION3 in VARCHAR2
48 ,P_VRE_INFORMATION4 in VARCHAR2
49 ,P_VRE_INFORMATION5 in VARCHAR2
50 ,P_VRE_INFORMATION6 in VARCHAR2
51 ,P_VRE_INFORMATION7 in VARCHAR2
52 ,P_VRE_INFORMATION8 in VARCHAR2
53 ,P_VRE_INFORMATION9 in VARCHAR2
54 ,P_VRE_INFORMATION10 in VARCHAR2
55 ,P_VRE_INFORMATION11 in VARCHAR2
56 ,P_VRE_INFORMATION12 in VARCHAR2
57 ,P_VRE_INFORMATION13 in VARCHAR2
58 ,P_VRE_INFORMATION14 in VARCHAR2
59 ,P_VRE_INFORMATION15 in VARCHAR2
60 ,P_VRE_INFORMATION16 in VARCHAR2
61 ,P_VRE_INFORMATION17 in VARCHAR2
62 ,P_VRE_INFORMATION18 in VARCHAR2
63 ,P_VRE_INFORMATION19 in VARCHAR2
64 ,P_VRE_INFORMATION20 in VARCHAR2
65 ) is
66 Begin
67
68 /* Added for GSI Bug 5472781 */
69 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
70 hr_utility.trace('PL not installed.Leaving create_pl_vehicle');
71 return;
72 END IF;
73 -- Check for Vehicle Ownership other then private
74
75 If P_VEHICLE_OWNERSHIP <> 'PL_PC' Then
76 -- Check for mandatory nature of fields Official Identification number, Identification number and Engine number
77
78 If P_VRE_INFORMATION1 is null or P_VRE_INFORMATION3 is null or P_VEHICLE_ID_NUMBER is null Then
79
80 hr_utility.set_message(800,'HR_375837_VRE_PL_VEHICLE');
81 --Ensure that you enter a vehicle card identification number, vehicle body number, and an engine number.
82 hr_utility.raise_error;
83
84 End if;
85 -- End of Mandatory check
86
87 End if;
88 EXCEPTION WHEN app_exception.application_exception THEN
89 IF hr_multi_message.exception_add(p_same_associated_columns => 'Y') THEN
90 RAISE;
91 END IF;
92 -- After validating the set of important attributes
93 -- if Multiple Message detection is enabled and at least
94 -- one error has been found then abort further validation.
95 hr_multi_message.end_validation_set;
96
97 --hr_utility.set_location('End of Vehicle Ownership check',30);
98 -- End of Vehicle Ownership check
99 End create_pl_vehicle;
100 -- End of create_pl_vehicle
101
102 -- Start of update_pl_vehicle
103 PROCEDURE update_pl_vehicle(P_EFFECTIVE_DATE in DATE
104 ,P_DATETRACK_MODE in VARCHAR2
105 ,P_VEHICLE_REPOSITORY_ID in NUMBER
106 ,P_OBJECT_VERSION_NUMBER in NUMBER
107 ,P_REGISTRATION_NUMBER in VARCHAR2
108 ,P_VEHICLE_TYPE in VARCHAR2
109 ,P_VEHICLE_ID_NUMBER in VARCHAR2
110 ,P_BUSINESS_GROUP_ID in NUMBER
111 ,P_MAKE in VARCHAR2
112 ,P_ENGINE_CAPACITY_IN_CC in NUMBER
113 ,P_FUEL_TYPE in VARCHAR2
114 ,P_CURRENCY_CODE in VARCHAR2
115 ,P_VEHICLE_STATUS in VARCHAR2
116 ,P_VEHICLE_INACTIVITY_REASON in VARCHAR2
117 ,P_MODEL in VARCHAR2
118 ,P_INITIAL_REGISTRATION in DATE
119 ,P_LAST_REGISTRATION_RENEW_DATE in DATE
120 ,P_LIST_PRICE in NUMBER
121 ,P_ACCESSORY_VALUE_AT_STARTDATE in NUMBER
122 ,P_ACCESSORY_VALUE_ADDED_LATER in NUMBER
123 ,P_MARKET_VALUE_CLASSIC_CAR in NUMBER
124 ,P_FISCAL_RATINGS in NUMBER
125 ,P_FISCAL_RATINGS_UOM in VARCHAR2
126 ,P_VEHICLE_PROVIDER in VARCHAR2
127 ,P_VEHICLE_OWNERSHIP in VARCHAR2
128 ,P_SHARED_VEHICLE in VARCHAR2
129 ,P_ASSET_NUMBER in VARCHAR2
130 ,P_LEASE_CONTRACT_NUMBER in VARCHAR2
131 ,P_LEASE_CONTRACT_EXPIRY_DATE in DATE
132 ,P_TAXATION_METHOD in VARCHAR2
133 ,P_FLEET_INFO in VARCHAR2
134 ,P_FLEET_TRANSFER_DATE in DATE
135 ,P_COLOR in VARCHAR2
136 ,P_SEATING_CAPACITY in NUMBER
137 ,P_WEIGHT in NUMBER
138 ,P_WEIGHT_UOM in VARCHAR2
139 ,P_MODEL_YEAR in NUMBER
140 ,P_INSURANCE_NUMBER in VARCHAR2
141 ,P_INSURANCE_EXPIRY_DATE in DATE
142 ,P_COMMENTS in VARCHAR2
143 ,P_VRE_ATTRIBUTE_CATEGORY in VARCHAR2
144 ,P_VRE_INFORMATION_CATEGORY in VARCHAR2
145 ,P_VRE_INFORMATION1 in VARCHAR2
146 ,P_VRE_INFORMATION2 in VARCHAR2
147 ,P_VRE_INFORMATION3 in VARCHAR2
148 ,P_VRE_INFORMATION4 in VARCHAR2
149 ,P_VRE_INFORMATION5 in VARCHAR2
150 ,P_VRE_INFORMATION6 in VARCHAR2
151 ,P_VRE_INFORMATION7 in VARCHAR2
152 ,P_VRE_INFORMATION8 in VARCHAR2
153 ,P_VRE_INFORMATION9 in VARCHAR2
154 ,P_VRE_INFORMATION10 in VARCHAR2
155 ,P_VRE_INFORMATION11 in VARCHAR2
156 ,P_VRE_INFORMATION12 in VARCHAR2
157 ,P_VRE_INFORMATION13 in VARCHAR2
158 ,P_VRE_INFORMATION14 in VARCHAR2
159 ,P_VRE_INFORMATION15 in VARCHAR2
160 ,P_VRE_INFORMATION16 in VARCHAR2
161 ,P_VRE_INFORMATION17 in VARCHAR2
162 ,P_VRE_INFORMATION18 in VARCHAR2
163 ,P_VRE_INFORMATION19 in VARCHAR2
164 ,P_VRE_INFORMATION20 in VARCHAR2) is
165 Begin
166 /* Added for GSI Bug 5472781 */
167 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
168 hr_utility.trace('PL not installed.Leaving update_pl_vehicle');
169 return;
170 END IF;
171 -- Check for Vehicle Ownership other then private
172
173 If P_VEHICLE_OWNERSHIP <> 'PL_PC' Then
174 -- Check for mandatory nature of fields Date From,Official Identification number, Identification number and Engine number
175
176 If P_VRE_INFORMATION1 is null or P_VRE_INFORMATION3 is null or P_VEHICLE_ID_NUMBER is null Then
177 hr_utility.set_message(800,'HR_375837_VRE_PL_VEHICLE');
178 --Ensure that you enter a vehicle card identification number, vehicle body number, and an engine number.
179 hr_utility.raise_error;
180 End if;
181 -- End of Mandatory check
182
183 End if;
184
185 EXCEPTION WHEN app_exception.application_exception THEN
186 IF hr_multi_message.exception_add(p_same_associated_columns => 'Y') THEN
187 RAISE;
188 END IF;
189 -- After validating the set of important attributes
190 -- if Multiple Message detection is enabled and at least
191 -- one error has been found then abort further validation.
192 hr_multi_message.end_validation_set;
193 -- End of Vehicle Ownership check
194 End update_pl_vehicle;
195 -- End of update_pl_vehicle
196
197 END pqp_pl_vehicle_repository;