1 Package pqp_vre_shd as
2 /* $Header: pqvrerhi.pkh 120.0.12010000.1 2008/07/28 11:26:09 appldev ship $ */
3
4 --
5 -- ----------------------------------------------------------------------------
6 -- | Global Record Type Specification |
7 -- ----------------------------------------------------------------------------
8 --
9 TYPE g_rec_type IS RECORD
10 (vehicle_repository_id NUMBER(10)
11 ,effective_start_date DATE
12 ,effective_end_date DATE
13 ,registration_number VARCHAR2(30)
14 ,vehicle_type VARCHAR2(30)
15 ,vehicle_id_number VARCHAR2(50)
16 ,business_group_id NUMBER(15)
17 ,make VARCHAR2(30)
18 ,model VARCHAR2(30)
19 ,initial_registration DATE
20 ,last_registration_renew_date DATE
21 ,engine_capacity_in_cc NUMBER
22 ,fuel_type VARCHAR2(30)
23 ,currency_code VARCHAR2(30)
24 ,list_price NUMBER(11,2)
25 ,accessory_value_at_startdate NUMBER(11,2)
26 ,accessory_value_added_later NUMBER(11,2)
27 ,market_value_classic_car NUMBER(11,2)
28 ,fiscal_ratings NUMBER
29 ,fiscal_ratings_uom VARCHAR2(30)
30 ,vehicle_provider VARCHAR2(60)
31 ,vehicle_ownership VARCHAR2(30)
32 ,shared_vehicle VARCHAR2(30)
33 ,vehicle_status VARCHAR2(30)
34 ,vehicle_inactivity_reason VARCHAR2(30)
35 ,asset_number VARCHAR2(80)
36 ,lease_contract_number VARCHAR2(80)
37 ,lease_contract_expiry_date DATE
38 ,taxation_method VARCHAR2(80)
39 ,fleet_info VARCHAR2(120)
40 ,fleet_transfer_date DATE
41 ,object_version_number NUMBER(9)
42 ,color VARCHAR2(30)
43 ,seating_capacity NUMBER
44 ,weight NUMBER(15,2)
45 ,weight_uom VARCHAR2(30)
46 ,model_year NUMBER
47 ,insurance_number VARCHAR2(80)
48 ,insurance_expiry_date DATE
49 ,comments VARCHAR2(180)
50 ,vre_attribute_category VARCHAR2(30)
51 ,vre_attribute1 VARCHAR2(150)
52 ,vre_attribute2 VARCHAR2(150)
53 ,vre_attribute3 VARCHAR2(150)
54 ,vre_attribute4 VARCHAR2(150)
55 ,vre_attribute5 VARCHAR2(150)
56 ,vre_attribute6 VARCHAR2(150)
57 ,vre_attribute7 VARCHAR2(150)
58 ,vre_attribute8 VARCHAR2(150)
59 ,vre_attribute9 VARCHAR2(150)
60 ,vre_attribute10 VARCHAR2(150)
61 ,vre_attribute11 VARCHAR2(150)
62 ,vre_attribute12 VARCHAR2(150)
63 ,vre_attribute13 VARCHAR2(150)
64 ,vre_attribute14 VARCHAR2(150)
65 ,vre_attribute15 VARCHAR2(150)
66 ,vre_attribute16 VARCHAR2(150)
67 ,vre_attribute17 VARCHAR2(150)
68 ,vre_attribute18 VARCHAR2(150)
69 ,vre_attribute19 VARCHAR2(150)
70 ,vre_attribute20 VARCHAR2(150)
71 ,vre_information_category VARCHAR2(30)
72 ,vre_information1 VARCHAR2(150)
73 ,vre_information2 VARCHAR2(150)
74 ,vre_information3 VARCHAR2(150)
75 ,vre_information4 VARCHAR2(150)
76 ,vre_information5 VARCHAR2(150)
77 ,vre_information6 VARCHAR2(150)
78 ,vre_information7 VARCHAR2(150)
79 ,vre_information8 VARCHAR2(150)
80 ,vre_information9 VARCHAR2(150)
81 ,vre_information10 VARCHAR2(150)
82 ,vre_information11 VARCHAR2(150)
83 ,vre_information12 VARCHAR2(150)
84 ,vre_information13 VARCHAR2(150)
85 ,vre_information14 VARCHAR2(150)
86 ,vre_information15 VARCHAR2(150)
87 ,vre_information16 VARCHAR2(150)
88 ,vre_information17 VARCHAR2(150)
89 ,vre_information18 VARCHAR2(150)
90 ,vre_information19 VARCHAR2(150)
91 ,vre_information20 VARCHAR2(150)
92 );
93 --
94 -- ----------------------------------------------------------------------------
95 -- | Global Definitions - Internal Development Use Only |
96 -- ----------------------------------------------------------------------------
97 --
98 g_old_rec g_rec_type; -- Global record definition
99 -- Global table name
100 g_tab_nam constant VARCHAR2(30) := 'PQP_VEHICLE_REPOSITORY_F';
101 --
102 -- ----------------------------------------------------------------------------
103 -- |---------------------------< constraint_error >---------------------------|
104 -- ----------------------------------------------------------------------------
105 -- {Start Of Comments}
106 --
107 -- Description:
108 -- This procedure is called when a constraint has been violated (i.e.
109 -- The exception hr_api.check_integrity_violated,
110 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
111 -- hr_api.unique_integrity_violated has been raised).
112 -- The exceptions can only be raised as follows:
113 -- 1) A check constraint can only be violated during an INSERT or UPDATE
114 -- dml operation.
115 -- 2) A parent integrity constraint can only be violated during an
116 -- INSERT or UPDATE dml operation.
117 -- 3) A child integrity constraint can only be violated during an
118 -- DELETE dml operation.
119 -- 4) A unique integrity constraint can only be violated during INSERT or
120 -- UPDATE dml operation.
121 --
122 -- Prerequisites:
123 -- 1) Either hr_api.check_integrity_violated,
124 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
125 -- hr_api.unique_integrity_violated has been raised with the subsequent
126 -- stripping of the constraint name from the generated error message
127 -- text.
128 -- 2) Standalone validation test which corresponds with a constraint error.
129 --
130 -- In Parameter:
131 -- p_constraint_name is in upper format and is just the constraint name
132 -- (e.g. not prefixed by brackets, schema owner etc).
133 --
134 -- Post Success:
135 -- Development dependant.
136 --
137 -- Post Failure:
138 -- Developement dependant.
139 --
140 -- Developer Implementation Notes:
141 -- For each constraint being checked the hr system package failure message
142 -- has been generated as a template only. These system error messages should
143 -- be modified as required (i.e. change the system failure message to a user
144 -- friendly defined error message).
145 --
146 -- Access Status:
147 -- Internal Development Use Only.
148 --
149 -- {End Of Comments}
150 -- ----------------------------------------------------------------------------
151 PROCEDURE constraint_error
152 (p_constraint_name in all_constraints.constraint_name%TYPE);
153 --
154 -- ----------------------------------------------------------------------------
155 -- |-----------------------------< api_updating >-----------------------------|
156 -- ----------------------------------------------------------------------------
157 -- {Start Of Comments}
158 --
159 -- Description:
160 -- This function is used to populate the g_old_rec record with the
161 -- current row from the database for the specified primary key
162 -- provided that the primary key exists and is valid and does not
163 -- already match the current g_old_rec. The function will always return
164 -- a TRUE value if the g_old_rec is populated with the current row.
165 -- A FALSE value will be returned if all of the primary key arguments
166 -- are null.
167 --
168 -- Prerequisites:
169 -- None.
170 --
171 -- In Parameters:
172 --
173 -- Post Success:
174 -- A value of TRUE will be returned indiciating that the g_old_rec
175 -- is current.
176 -- A value of FALSE will be returned if all of the primary key arguments
177 -- have a null value (this indicates that the row has not be inserted into
178 -- the Schema), and therefore could never have a corresponding row.
179 --
180 -- Post Failure:
181 -- A failure can only occur under two circumstances:
182 -- 1) The primary key is invalid (i.e. a row does not exist for the
183 -- specified primary key values).
184 -- 2) If an object_version_number exists but is NOT the same as the current
185 -- g_old_rec value.
186 --
187 -- Developer Implementation Notes:
188 -- None.
189 --
190 -- Access Status:
191 -- Internal Development Use Only.
192 --
193 -- {End Of Comments}
194 -- ----------------------------------------------------------------------------
195 Function api_updating
196 (p_effective_date IN DATE
197 ,p_vehicle_repository_id IN NUMBER
198 ,p_object_version_number IN NUMBER
199 ) Return Boolean;
200 --
201 -- ----------------------------------------------------------------------------
202 -- |---------------------------< find_dt_upd_modes >--------------------------|
203 -- ----------------------------------------------------------------------------
204 -- {Start Of Comments}
205 --
206 -- Description:
207 -- This procedure is used to determine what datetrack update modes are
208 -- allowed as of the effective date for this entity. The procedure will
209 -- return a corresponding Boolean value for each of the update modes
210 -- available where TRUE indicates that the corresponding update mode
211 -- is available.
212 --
213 -- Prerequisites:
214 -- None.
215 --
216 -- In Parameters:
217 -- p_effective_date
218 -- Specifies the date at which the datetrack modes will be operated on.
219 -- p_base_key_value
220 -- Specifies the primary key value for this datetrack entity.
221 -- (E.g. For this entity the assignment of the argument would be:
222 -- p_base_key_value = :vehicle_repository_id).
223 --
224 -- Post Success:
225 -- Processing continues.
226 --
227 -- Post Failure:
228 -- Failure might occur if for the specified effective date and primary key
229 -- value a row doesn't exist.
230 --
231 -- Developer Implementation Notes:
232 -- This procedure could require changes if this entity has any sepcific
233 -- delete restrictions.
234 -- For example, this entity might disallow the datetrack update mode of
235 -- UPDATE. To implement this you would have to set and return a Boolean
236 -- value of FALSE after the call to the dt_api.find_dt_upd_modes procedure.
237 --
238 -- Access Status:
239 -- Internal Development Use Only.
240 --
241 -- {End Of Comments}
242 -- ----------------------------------------------------------------------------
243 PROCEDURE find_dt_upd_modes
244 (p_effective_date IN DATE
245 ,p_base_key_value IN NUMBER
246 ,p_correction OUT NOCOPY boolean
247 ,p_update OUT NOCOPY boolean
248 ,p_update_override OUT NOCOPY boolean
249 ,p_update_change_insert OUT NOCOPY boolean
250 );
251 --
252 -- ----------------------------------------------------------------------------
253 -- |---------------------------< find_dt_del_modes >--------------------------|
254 -- ----------------------------------------------------------------------------
255 -- {Start Of Comments}
256 --
257 -- Description:
258 -- This procedure is used to determine what datetrack delete modes are
259 -- allowed as of the effective date for this entity. The procedure will
260 -- return a corresponding Boolean value for each of the delete modes
261 -- available where TRUE indicates that the corresponding delete mode is
262 -- available.
263 --
264 -- Prerequisites:
265 -- None.
266 --
267 -- In Parameters:
268 -- p_effective_date
269 -- Specifies the date at which the datetrack modes will be operated on.
270 -- p_base_key_value
271 -- Specifies the primary key value for this datetrack entity.
272 -- (E.g. For this entity the assignment of the argument would be:
273 -- p_base_key_value = :vehicle_repository_id).
274 --
275 -- Post Success:
276 -- Processing continues.
277 --
278 -- Post Failure:
279 -- Failure might occur if for the specified effective date and primary key
280 -- value a row doesn't exist.
281 --
282 -- Developer Implementation Notes:
283 -- This procedure could require changes if this entity has any sepcific
284 -- delete restrictions.
285 -- For example, this entity might disallow the datetrack delete mode of
286 -- ZAP. To implement this you would have to set and return a Boolean value
287 -- of FALSE after the call to the dt_api.find_dt_del_modes procedure.
288 --
289 -- Access Status:
290 -- Internal Development Use Only.
291 --
292 -- {End Of Comments}
293 -- ----------------------------------------------------------------------------
294 PROCEDURE find_dt_del_modes
295 (p_effective_date IN DATE
296 ,p_base_key_value IN NUMBER
297 ,p_zap OUT NOCOPY boolean
298 ,p_delete OUT NOCOPY boolean
299 ,p_future_change OUT NOCOPY boolean
300 ,p_delete_next_change OUT NOCOPY boolean
301 );
302 --
303 -- ----------------------------------------------------------------------------
304 -- |-----------------------< upd_effective_end_date >-------------------------|
305 -- ----------------------------------------------------------------------------
306 -- {Start Of Comments}
307 --
308 -- Description:
309 -- This procedure will update the specified datetrack row with the
310 -- specified new effective end date. The object version number is also
311 -- set to the next object version number. DateTrack modes which call
312 -- this procedure are: UPDATE, UPDATE_CHANGE_INSERT,
313 -- UPDATE_OVERRIDE, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE.
314 -- This is an internal datetrack maintenance procedure which should
315 -- not be modified in anyway.
316 --
317 -- Prerequisites:
318 -- None.
319 --
320 -- In Parameters:
321 -- p_new_effective_end_date
322 -- Specifies the new effective end date which will be set for the
323 -- row as of the effective date.
324 -- p_base_key_value
325 -- Specifies the primary key value for this datetrack entity.
326 -- (E.g. For this entity the assignment of the argument would be:
327 -- p_base_key_value = :vehicle_repository_id).
328 --
329 -- Post Success:
330 -- The specified row will be updated with the new effective end date and
331 -- object_version_number.
332 --
333 -- Post Failure:
334 -- Failure might occur if for the specified effective date and primary key
335 -- value a row doesn't exist.
336 --
337 -- Developer Implementation Notes:
338 -- This is an internal datetrack maintenance procedure which should
339 -- not be modified in anyway.
340 --
341 -- Access Status:
342 -- Internal Row Handler Use Only.
343 --
344 -- {End Of Comments}
345 -- ----------------------------------------------------------------------------
346 PROCEDURE upd_effective_end_date
347 (p_effective_date IN DATE
348 ,p_base_key_value IN NUMBER
349 ,p_new_effective_end_date IN DATE
350 ,p_validation_start_date IN DATE
351 ,p_validation_end_date IN DATE
352 ,p_object_version_number OUT NOCOPY NUMBER
353 );
354 --
355 -- ----------------------------------------------------------------------------
356 -- |---------------------------------< lck >----------------------------------|
357 -- ----------------------------------------------------------------------------
358 -- {Start Of Comments}
359 --
360 -- Description:
361 -- The Lck process for datetrack is complicated and comprises of the
362 -- following processing
363 -- The processing steps are as follows:
367 -- 3) The datetrack mode is then validated to ensure the operation is
364 -- 1) The row to be updated or deleted must be locked.
365 -- By locking this row, the g_old_rec record data type is populated.
366 -- 2) If a comment exists the text is selected from hr_comments.
368 -- valid. If the mode is valid the validation start and end dates for
369 -- the mode will be derived and returned. Any required locking is
370 -- completed when the datetrack mode is validated.
371 --
372 -- Prerequisites:
373 -- When attempting to call the lck procedure the object version number,
374 -- primary key, effective date and datetrack mode must be specified.
375 --
376 -- In Parameters:
377 -- p_effective_date
378 -- Specifies the date of the datetrack update operation.
379 -- p_datetrack_mode
380 -- Determines the datetrack update or delete mode.
381 --
382 -- Post Success:
383 -- On successful completion of the Lck process the row to be updated or
384 -- deleted will be locked and selected into the global data structure
385 -- g_old_rec.
386 --
387 -- Post Failure:
388 -- The Lck process can fail for three reasons:
389 -- 1) When attempting to lock the row the row could already be locked by
390 -- another user. This will raise the HR_Api.Object_Locked exception.
391 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
392 -- This error is trapped and reported using the message name
393 -- 'HR_7220_INVALID_PRIMARY_KEY'.
394 -- 3) The row although existing in the HR Schema has a different object
395 -- version number than the object version number specified.
396 -- This error is trapped and reported using the message name
397 -- 'HR_7155_OBJECT_INVALID'.
398 --
399 -- Developer Implementation Notes:
400 -- None.
401 --
402 -- Access Status:
403 -- Internal Development Use Only.
404 --
405 -- {End Of Comments}
406 -- ----------------------------------------------------------------------------
407 PROCEDURE lck
408 (p_effective_date IN DATE
409 ,p_datetrack_mode IN VARCHAR2
410 ,p_vehicle_repository_id IN NUMBER
411 ,p_object_version_number IN NUMBER
412 ,p_validation_start_date OUT NOCOPY DATE
413 ,p_validation_end_date OUT NOCOPY DATE
414 );
415 --
416 -- ----------------------------------------------------------------------------
417 -- |-----------------------------< convert_args >-----------------------------|
418 -- ----------------------------------------------------------------------------
419 -- {Start Of Comments}
420 --
421 -- Description:
422 -- This function is used to turn attribute parameters into the record
423 -- structure parameter g_rec_type.
424 --
425 -- Prerequisites:
426 -- This is a private function and can only be called from the ins or upd
427 -- attribute processes.
428 --
429 -- In Parameters:
430 --
431 -- Post Success:
432 -- A returning record structure will be returned.
433 --
434 -- Post Failure:
435 -- No direct error handling is required within this function. Any possible
436 -- errors within this function will be a PL/SQL value error due to
437 -- conversion of datatypes or data lengths.
438 --
439 -- Developer Implementation Notes:
440 -- None.
441 --
442 -- Access Status:
443 -- Internal Row Handler Use Only.
444 --
445 -- {End Of Comments}
446 -- ----------------------------------------------------------------------------
447 Function convert_args
448 (p_vehicle_repository_id IN NUMBER
449 ,p_effective_start_date IN DATE
450 ,p_effective_end_date IN DATE
451 ,p_registration_number IN VARCHAR2
452 ,p_vehicle_type IN VARCHAR2
453 ,p_vehicle_id_number IN VARCHAR2
454 ,p_business_group_id IN NUMBER
455 ,p_make IN VARCHAR2
456 ,p_model IN VARCHAR2
457 ,p_initial_registration IN DATE
458 ,p_last_registration_renew_date IN DATE
459 ,p_engine_capacity_in_cc IN NUMBER
460 ,p_fuel_type IN VARCHAR2
461 ,p_currency_code IN VARCHAR2
462 ,p_list_price IN NUMBER
463 ,p_accessory_value_at_startdate IN NUMBER
464 ,p_accessory_value_added_later IN NUMBER
465 ,p_market_value_classic_car IN NUMBER
466 ,p_fiscal_ratings IN NUMBER
467 ,p_fiscal_ratings_uom IN VARCHAR2
468 ,p_vehicle_provider IN VARCHAR2
469 ,p_vehicle_ownership IN VARCHAR2
470 ,p_shared_vehicle IN VARCHAR2
471 ,p_vehicle_status IN VARCHAR2
472 ,p_vehicle_inactivity_reason IN VARCHAR2
473 ,p_asset_number IN VARCHAR2
474 ,p_lease_contract_number IN VARCHAR2
475 ,p_lease_contract_expiry_date IN DATE
476 ,p_taxation_method IN VARCHAR2
477 ,p_fleet_info IN VARCHAR2
478 ,p_fleet_transfer_date IN DATE
479 ,p_object_version_number IN NUMBER
480 ,p_color IN VARCHAR2
481 ,p_seating_capacity IN NUMBER
482 ,p_weight IN NUMBER
483 ,p_weight_uom IN VARCHAR2
484 ,p_model_year IN NUMBER
485 ,p_insurance_number IN VARCHAR2
486 ,p_insurance_expiry_date IN DATE
487 ,p_comments IN VARCHAR2
488 ,p_vre_attribute_category IN VARCHAR2
489 ,p_vre_attribute1 IN VARCHAR2
490 ,p_vre_attribute2 IN VARCHAR2
491 ,p_vre_attribute3 IN VARCHAR2
492 ,p_vre_attribute4 IN VARCHAR2
493 ,p_vre_attribute5 IN VARCHAR2
494 ,p_vre_attribute6 IN VARCHAR2
495 ,p_vre_attribute7 IN VARCHAR2
496 ,p_vre_attribute8 IN VARCHAR2
497 ,p_vre_attribute9 IN VARCHAR2
498 ,p_vre_attribute10 IN VARCHAR2
499 ,p_vre_attribute11 IN VARCHAR2
500 ,p_vre_attribute12 IN VARCHAR2
501 ,p_vre_attribute13 IN VARCHAR2
502 ,p_vre_attribute14 IN VARCHAR2
503 ,p_vre_attribute15 IN VARCHAR2
504 ,p_vre_attribute16 IN VARCHAR2
505 ,p_vre_attribute17 IN VARCHAR2
506 ,p_vre_attribute18 IN VARCHAR2
507 ,p_vre_attribute19 IN VARCHAR2
508 ,p_vre_attribute20 IN VARCHAR2
509 ,p_vre_information_category IN VARCHAR2
510 ,p_vre_information1 IN VARCHAR2
511 ,p_vre_information2 IN VARCHAR2
512 ,p_vre_information3 IN VARCHAR2
513 ,p_vre_information4 IN VARCHAR2
514 ,p_vre_information5 IN VARCHAR2
515 ,p_vre_information6 IN VARCHAR2
516 ,p_vre_information7 IN VARCHAR2
517 ,p_vre_information8 IN VARCHAR2
518 ,p_vre_information9 IN VARCHAR2
519 ,p_vre_information10 IN VARCHAR2
520 ,p_vre_information11 IN VARCHAR2
521 ,p_vre_information12 IN VARCHAR2
522 ,p_vre_information13 IN VARCHAR2
523 ,p_vre_information14 IN VARCHAR2
524 ,p_vre_information15 IN VARCHAR2
525 ,p_vre_information16 IN VARCHAR2
526 ,p_vre_information17 IN VARCHAR2
527 ,p_vre_information18 IN VARCHAR2
528 ,p_vre_information19 IN VARCHAR2
529 ,p_vre_information20 IN VARCHAR2
530 )
531 Return g_rec_type;
532 --
533 end pqp_vre_shd;
[Home] [Help]