[Home] [Help]
PACKAGE BODY: APPS.PQP_VRE_INS
Source
1 PACKAGE BODY pqp_vre_ins AS
2 /* $Header: pqvrerhi.pkb 120.1 2005/06/30 12:04:54 rpinjala noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqp_vre_ins.'; -- Global package name
9 --
10 -- The following global variables are only to be used by
11 -- the set_base_key_value and pre_insert procedures.
12 --
13 g_vehicle_repository_id_i NUMBER DEFAULT NULL;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 PROCEDURE set_base_key_value
19 (p_vehicle_repository_id IN NUMBER) IS
20 --
21 l_proc VARCHAR2(72) := g_package||'set_base_key_value';
22 --
23 BEGIN
24 hr_utility.set_location('Entering:'||l_proc, 10);
25 --
26 pqp_vre_ins.g_vehicle_repository_id_i := p_vehicle_repository_id;
27 --
28 hr_utility.set_location(' Leaving:'||l_proc, 20);
29 END set_base_key_value;
30 --
31 --
32 -- ----------------------------------------------------------------------------
33 -- |----------------------------< dt_insert_dml >-----------------------------|
34 -- ----------------------------------------------------------------------------
35 -- {Start Of Comments}
36 --
37 -- Description:
38 -- This procedure controls the actual dml insert logic for datetrack. The
39 -- functions of this procedure are as follows:
40 -- 1) Get the object_version_number.
41 -- 2) To set the effective start and end dates to the corresponding
42 -- validation start and end dates. Also, the object version number
43 -- record attribute is set.
44 -- 3) To set and unset the g_api_dml status as required (as we are about to
45 -- perform dml).
46 -- 4) To insert the row into the schema with the derived effective start
47 -- and end dates and the object version number.
48 -- 5) To trap any constraint violations that may have occurred.
49 -- 6) To raise any other errors.
50 --
51 -- Prerequisites:
52 -- This is an internal private procedure which must be called from the
53 -- insert_dml and pre_update (logic permitting) procedure and must have
54 -- all mandatory arguments set.
55 --
56 -- In Parameters:
57 -- A Pl/Sql record structure.
58 --
59 -- Post Success:
60 -- The specified row will be inserted into the schema.
61 --
62 -- Post Failure:
63 -- On the insert dml failure it is important to note that we always reset the
64 -- g_api_dml status to false.
65 -- If a check or unique integrity constraint violation is raised the
66 -- constraint_error procedure will be called.
67 -- If any other error is reported, the error will be raised after the
68 -- g_api_dml status is reset.
69 --
70 -- Developer Implementation Notes:
71 -- This is an internal datetrack maintenance procedure which should
72 -- not be modified in anyway.
73 --
74 -- Access Status:
75 -- Internal Row Handler Use Only.
76 --
77 -- {End Of Comments}
78 -- ----------------------------------------------------------------------------
79 PROCEDURE dt_insert_dml
80 (p_rec IN out nocopy pqp_vre_shd.g_rec_type
81 ,p_effective_date IN DATE
82 ,p_datetrack_mode IN VARCHAR2
83 ,p_validation_start_date IN DATE
84 ,p_validation_end_date IN DATE
85 ) IS
86 -- Cursor to select 'old' created AOL who column values
87 --
88 CURSOR C_Sel1 IS
89 SELECT t.created_by,
90 t.creation_date
91 FROM pqp_vehicle_repository_f t
92 WHERE t.vehicle_repository_id = p_rec.vehicle_repository_id
93 AND t.effective_start_date =
94 pqp_vre_shd.g_old_rec.effective_start_date
95 AND t.effective_end_date = (p_validation_start_date - 1);
96 --
97 l_proc varchar2(72) := g_package||'dt_insert_dml';
98 l_created_by pqp_vehicle_repository_f.created_by%TYPE;
99 l_creation_date pqp_vehicle_repository_f.creation_date%TYPE;
100 l_last_update_date pqp_vehicle_repository_f.last_update_date%TYPE;
101 l_last_updated_by pqp_vehicle_repository_f.last_updated_by%TYPE;
102 l_last_update_login pqp_vehicle_repository_f.last_update_login%TYPE;
103 --
104 Begin
105 hr_utility.set_location('Entering:'||l_proc, 5);
106 --
107 -- Get the object version number for the insert
108 --
109 p_rec.object_version_number :=
110 dt_api.get_object_version_number
111 (p_base_table_name => 'pqp_vehicle_repository_f'
112 ,p_base_key_column => 'vehicle_repository_id'
113 ,p_base_key_value => p_rec.vehicle_repository_id
114 );
115 --
116 -- Set the effective start and end dates to the corresponding
117 -- validation start and end dates
118 --
119 p_rec.effective_start_date := p_validation_start_date;
120 p_rec.effective_end_date := p_validation_end_date;
121 --
122 -- If the datetrack_mode is not INSERT then we must populate the WHO
123 -- columns with the 'old' creation values and 'new' updated values.
124 --
125 IF (p_datetrack_mode <> hr_api.g_insert) THEN
126 hr_utility.set_location(l_proc, 10);
127 --
128 -- Select the 'old' created values
129 --
130 OPEN C_Sel1;
131 FETCH C_Sel1 Into l_created_by, l_creation_date;
132 IF C_Sel1%notfound THEN
133 --
134 -- The previous 'old' created row has not been found. We need
135 -- to error as an internal datetrack problem exists.
136 --
137 CLOSE C_Sel1;
138 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
139 fnd_message.set_token('PROCEDURE', l_proc);
140 fnd_message.set_token('STEP','10');
141 fnd_message.raise_error;
142 END IF;
143 CLOSE C_Sel1;
144 --
145 -- Set the AOL updated WHO values
146 --
147 l_last_update_date := sysdate;
148 l_last_updated_by := fnd_global.user_id;
149 l_last_update_login := fnd_global.login_id;
150 END IF;
151 --
152 --
153 --
154 -- Insert the row into: pqp_vehicle_repository_f
155 --
156 INSERT INTO pqp_vehicle_repository_f
157 (vehicle_repository_id
158 ,effective_start_date
159 ,effective_end_date
160 ,registration_number
161 ,vehicle_type
162 ,vehicle_id_number
163 ,business_group_id
164 ,make
165 ,model
166 ,initial_registration
167 ,last_registration_renew_date
168 ,engine_capacity_in_cc
169 ,fuel_type
170 ,currency_code
171 ,list_price
172 ,accessory_value_at_startdate
173 ,accessory_value_added_later
174 ,market_value_classic_car
175 ,fiscal_ratings
176 ,fiscal_ratings_uom
177 ,vehicle_provider
178 ,vehicle_ownership
179 ,shared_vehicle
180 ,vehicle_status
181 ,vehicle_inactivity_reason
182 ,asset_number
183 ,lease_contract_number
184 ,lease_contract_expiry_date
185 ,taxation_method
186 ,fleet_info
187 ,fleet_transfer_date
188 ,object_version_number
189 ,color
190 ,seating_capacity
191 ,weight
192 ,weight_uom
193 ,model_year
194 ,insurance_number
195 ,insurance_expiry_date
196 ,comments
197 ,vre_attribute_category
198 ,vre_attribute1
199 ,vre_attribute2
200 ,vre_attribute3
201 ,vre_attribute4
202 ,vre_attribute5
203 ,vre_attribute6
204 ,vre_attribute7
205 ,vre_attribute8
206 ,vre_attribute9
207 ,vre_attribute10
208 ,vre_attribute11
209 ,vre_attribute12
210 ,vre_attribute13
211 ,vre_attribute14
212 ,vre_attribute15
213 ,vre_attribute16
214 ,vre_attribute17
215 ,vre_attribute18
216 ,vre_attribute19
217 ,vre_attribute20
218 ,vre_information_category
219 ,vre_information1
220 ,vre_information2
221 ,vre_information3
222 ,vre_information4
223 ,vre_information5
224 ,vre_information6
225 ,vre_information7
226 ,vre_information8
227 ,vre_information9
228 ,vre_information10
229 ,vre_information11
230 ,vre_information12
231 ,vre_information13
232 ,vre_information14
233 ,vre_information15
234 ,vre_information16
235 ,vre_information17
236 ,vre_information18
237 ,vre_information19
238 ,vre_information20
239 ,created_by
240 ,creation_date
241 ,last_update_date
242 ,last_updated_by
243 ,last_update_login
244 )
245 VALUES
246 (p_rec.vehicle_repository_id
247 ,p_rec.effective_start_date
248 ,p_rec.effective_end_date
249 ,p_rec.registration_number
250 ,p_rec.vehicle_type
251 ,p_rec.vehicle_id_number
252 ,p_rec.business_group_id
253 ,p_rec.make
254 ,p_rec.model
255 ,p_rec.initial_registration
256 ,p_rec.last_registration_renew_date
257 ,p_rec.engine_capacity_in_cc
258 ,p_rec.fuel_type
259 ,p_rec.currency_code
260 ,p_rec.list_price
261 ,p_rec.accessory_value_at_startdate
262 ,p_rec.accessory_value_added_later
263 ,p_rec.market_value_classic_car
264 ,p_rec.fiscal_ratings
265 ,p_rec.fiscal_ratings_uom
266 ,p_rec.vehicle_provider
267 ,p_rec.vehicle_ownership
268 ,p_rec.shared_vehicle
269 ,p_rec.vehicle_status
270 ,p_rec.vehicle_inactivity_reason
271 ,p_rec.asset_number
272 ,p_rec.lease_contract_number
273 ,p_rec.lease_contract_expiry_date
274 ,p_rec.taxation_method
275 ,p_rec.fleet_info
276 ,p_rec.fleet_transfer_date
277 ,p_rec.object_version_number
278 ,p_rec.color
279 ,p_rec.seating_capacity
280 ,p_rec.weight
281 ,p_rec.weight_uom
282 ,p_rec.model_year
283 ,p_rec.insurance_number
284 ,p_rec.insurance_expiry_date
285 ,p_rec.comments
286 ,p_rec.vre_attribute_category
287 ,p_rec.vre_attribute1
288 ,p_rec.vre_attribute2
289 ,p_rec.vre_attribute3
290 ,p_rec.vre_attribute4
291 ,p_rec.vre_attribute5
292 ,p_rec.vre_attribute6
293 ,p_rec.vre_attribute7
294 ,p_rec.vre_attribute8
295 ,p_rec.vre_attribute9
296 ,p_rec.vre_attribute10
297 ,p_rec.vre_attribute11
298 ,p_rec.vre_attribute12
299 ,p_rec.vre_attribute13
300 ,p_rec.vre_attribute14
301 ,p_rec.vre_attribute15
302 ,p_rec.vre_attribute16
303 ,p_rec.vre_attribute17
304 ,p_rec.vre_attribute18
305 ,p_rec.vre_attribute19
306 ,p_rec.vre_attribute20
307 ,p_rec.vre_information_category
308 ,p_rec.vre_information1
309 ,p_rec.vre_information2
310 ,p_rec.vre_information3
311 ,p_rec.vre_information4
312 ,p_rec.vre_information5
313 ,p_rec.vre_information6
314 ,p_rec.vre_information7
315 ,p_rec.vre_information8
316 ,p_rec.vre_information9
317 ,p_rec.vre_information10
318 ,p_rec.vre_information11
319 ,p_rec.vre_information12
320 ,p_rec.vre_information13
321 ,p_rec.vre_information14
322 ,p_rec.vre_information15
323 ,p_rec.vre_information16
324 ,p_rec.vre_information17
325 ,p_rec.vre_information18
326 ,p_rec.vre_information19
327 ,p_rec.vre_information20
328 ,l_created_by
329 ,l_creation_date
330 ,l_last_update_date
331 ,l_last_updated_by
332 ,l_last_update_login
333 );
334 --
335 --
336 hr_utility.set_location(' Leaving:'||l_proc, 15);
337 --
338 EXCEPTION
339 WHEN hr_api.check_integrity_violated THEN
340 -- A check constraint has been violated
341 --
342 pqp_vre_shd.constraint_error
343 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
344 WHEN hr_api.unique_integrity_violated THEN
345 -- Unique integrity has been violated
346 --
347 pqp_vre_shd.constraint_error
348 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
349 WHEN OTHERS THEN
350 --
351 RAISE;
352 END dt_insert_dml;
353 --
354 -- ----------------------------------------------------------------------------
355 -- |------------------------------< insert_dml >------------------------------|
356 -- ----------------------------------------------------------------------------
357 PROCEDURE insert_dml
358 (p_rec IN OUT NOCOPY pqp_vre_shd.g_rec_type
359 ,p_effective_date IN DATE
360 ,p_datetrack_mode IN VARCHAR2
361 ,p_validation_start_date IN DATE
362 ,p_validation_end_date IN DATE
363 ) IS
364 --
365 l_proc varchar2(72) := g_package||'insert_dml';
366 --
367 BEGIN
368 hr_utility.set_location('Entering:'||l_proc, 5);
369 --
370 pqp_vre_ins.dt_insert_dml
371 (p_rec => p_rec
372 ,p_effective_date => p_effective_date
373 ,p_datetrack_mode => p_datetrack_mode
374 ,p_validation_start_date => p_validation_start_date
375 ,p_validation_end_date => p_validation_end_date
376 );
377 --
378 hr_utility.set_location(' Leaving:'||l_proc, 10);
379 END insert_dml;
380 --
381 -- ----------------------------------------------------------------------------
382 -- |------------------------------< pre_insert >------------------------------|
383 -- ----------------------------------------------------------------------------
384 -- {Start Of Comments}
385 --
386 -- Description:
387 -- This private procedure contains any processing which is required before
388 -- the insert dml. Presently, if the entity has a corresponding primary
389 -- key which is maintained by an associating sequence, the primary key for
390 -- the entity will be populated with the next sequence value in
391 -- preparation for the insert dml.
392 -- Also, if comments are defined for this entity, the comments insert
393 -- logic will also be called, generating a comment_id if required.
394 --
395 -- Prerequisites:
396 -- This is an internal procedure which is called from the ins procedure.
397 --
398 -- In Parameters:
399 -- A Pl/Sql record structure.
400 --
401 -- Post Success:
402 -- Processing continues.
403 --
404 -- Post Failure:
408 -- Developer Implementation Notes:
405 -- If an error has occurred, an error message and exception will be raised
406 -- but not handled.
407 --
409 -- Any pre-processing required before the insert dml is issued should be
410 -- coded within this procedure. As stated above, a good example is the
411 -- generation of a primary key number via a corresponding sequence.
412 -- It is important to note that any 3rd party maintenance should be reviewed
413 -- before placing in this procedure.
414 --
415 -- Access Status:
416 -- Internal Row Handler Use Only.
417 --
418 -- {End Of Comments}
419 -- ----------------------------------------------------------------------------
420 PROCEDURE pre_insert
421 (p_rec IN OUT NOCOPY pqp_vre_shd.g_rec_type
422 ,p_effective_date IN DATE
423 ,p_datetrack_mode IN VARCHAR2
424 ,p_validation_start_date IN DATE
425 ,p_validation_end_date IN DATE
426 ) IS
427 --
428 Cursor C_Sel1 is select pqp_vehicle_repository_s.nextval from sys.dual;
429 --
430 CURSOR C_Sel2 IS
431 SELECT NULL
432 FROM pqp_vehicle_repository_f
433 WHERE vehicle_repository_id =
434 pqp_vre_ins.g_vehicle_repository_id_i;
435 --
436 l_proc VARCHAR2(72) := g_package||'pre_insert';
437 l_exists VARCHAR2(1);
438 --
439 BEGIN
440 hr_utility.set_location('Entering:'||l_proc, 5);
441 --
442 IF (pqp_vre_ins.g_vehicle_repository_id_i is not null) THEN
443 --
444 -- Verify registered primary key values not already in use
445 --
446 OPEN C_Sel2;
447 FETCH C_Sel2 INTO l_exists;
448 IF C_Sel2%FOUND THEN
449 CLOSE C_Sel2;
450 --
451 -- The primary key values are already in use.
452 --
453 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
454 fnd_message.set_token('TABLE_NAME','pqp_vehicle_repository_f');
455 fnd_message.raise_error;
456 END IF;
457 CLOSE C_Sel2;
458 --
459 -- Use registered key values and clear globals
460 --
461 p_rec.vehicle_repository_id :=
462 pqp_vre_ins.g_vehicle_repository_id_i;
463 pqp_vre_ins.g_vehicle_repository_id_i := null;
464 ELSE
465 --
466 -- No registerd key values, so select the next sequence number
467 --
468 --
469 -- Select the next sequence number
470 --
471 OPEN C_Sel1;
472 FETCH C_Sel1 Into p_rec.vehicle_repository_id;
473 CLOSE C_Sel1;
474 End If;
475 --
476 --
477 hr_utility.set_location(' Leaving:'||l_proc, 10);
478 END pre_insert;
479 --
480 -- ----------------------------------------------------------------------------
481 -- |----------------------------< post_insert >-------------------------------|
482 -- ----------------------------------------------------------------------------
483 -- {Start Of Comments}
484 --
485 -- Description:
486 -- This private procedure contains any processing which is required after
487 -- the insert dml.
488 --
489 -- Prerequisites:
490 -- This is an internal procedure which is called from the ins procedure.
491 --
492 -- In Parameters:
493 -- A Pl/Sql record structure.
494 --
495 -- Post Success:
496 -- Processing continues.
497 --
498 -- Post Failure:
499 -- If an error has occurred, an error message and exception will be raised
500 -- but not handled.
501 --
502 -- Developer Implementation Notes:
503 -- Any post-processing required after the insert dml is issued should be
504 -- coded within this procedure. It is important to note that any 3rd party
505 -- maintenance should be reviewed before placing in this procedure.
506 --
507 -- Access Status:
508 -- Internal Row Handler Use Only.
509 --
510 -- {End Of Comments}
511 -- ----------------------------------------------------------------------------
512 PROCEDURE post_insert
513 (p_rec IN pqp_vre_shd.g_rec_type
514 ,p_effective_date IN DATE
515 ,p_datetrack_mode IN VARCHAR2
516 ,p_validation_start_date IN DATE
517 ,p_validation_end_date IN DATE
518 ) IS
519 --
520 l_proc VARCHAR2(72) := g_package||'post_insert';
521 --
522 BEGIN
523 hr_utility.set_location('Entering:'||l_proc, 5);
524 BEGIN
525 --
526 pqp_vre_rki.after_insert
527 (p_effective_date => p_effective_date
528 ,p_validation_start_date => p_validation_start_date
529 ,p_validation_end_date => p_validation_end_date
530 ,p_vehicle_repository_id => p_rec.vehicle_repository_id
531 ,p_effective_start_date => p_rec.effective_start_date
532 ,p_effective_end_date => p_rec.effective_end_date
533 ,p_registration_number => p_rec.registration_number
534 ,p_vehicle_type => p_rec.vehicle_type
535 ,p_vehicle_id_number => p_rec.vehicle_id_number
536 ,p_business_group_id => p_rec.business_group_id
537 ,p_make => p_rec.make
538 ,p_model => p_rec.model
539 ,p_initial_registration => p_rec.initial_registration
540 ,p_last_registration_renew_date => p_rec.last_registration_renew_date
541 ,p_engine_capacity_in_cc => p_rec.engine_capacity_in_cc
545 ,p_accessory_value_at_startdate => p_rec.accessory_value_at_startdate
542 ,p_fuel_type => p_rec.fuel_type
543 ,p_currency_code => p_rec.currency_code
544 ,p_list_price => p_rec.list_price
546 ,p_accessory_value_added_later => p_rec.accessory_value_added_later
547 ,p_market_value_classic_car => p_rec.market_value_classic_car
548 ,p_fiscal_ratings => p_rec.fiscal_ratings
549 ,p_fiscal_ratings_uom => p_rec.fiscal_ratings_uom
550 ,p_vehicle_provider => p_rec.vehicle_provider
551 ,p_vehicle_ownership => p_rec.vehicle_ownership
552 ,p_shared_vehicle => p_rec.shared_vehicle
553 ,p_vehicle_status => p_rec.vehicle_status
554 ,p_vehicle_inactivity_reason => p_rec.vehicle_inactivity_reason
555 ,p_asset_number => p_rec.asset_number
556 ,p_lease_contract_number => p_rec.lease_contract_number
557 ,p_lease_contract_expiry_date => p_rec.lease_contract_expiry_date
558 ,p_taxation_method => p_rec.taxation_method
559 ,p_fleet_info => p_rec.fleet_info
560 ,p_fleet_transfer_date => p_rec.fleet_transfer_date
561 ,p_object_version_number => p_rec.object_version_number
562 ,p_color => p_rec.color
563 ,p_seating_capacity => p_rec.seating_capacity
564 ,p_weight => p_rec.weight
565 ,p_weight_uom => p_rec.weight_uom
566 ,p_model_year => p_rec.model_year
567 ,p_insurance_number => p_rec.insurance_number
568 ,p_insurance_expiry_date => p_rec.insurance_expiry_date
569 ,p_comments => p_rec.comments
570 ,p_vre_attribute_category => p_rec.vre_attribute_category
571 ,p_vre_attribute1 => p_rec.vre_attribute1
572 ,p_vre_attribute2 => p_rec.vre_attribute2
573 ,p_vre_attribute3 => p_rec.vre_attribute3
574 ,p_vre_attribute4 => p_rec.vre_attribute4
575 ,p_vre_attribute5 => p_rec.vre_attribute5
576 ,p_vre_attribute6 => p_rec.vre_attribute6
577 ,p_vre_attribute7 => p_rec.vre_attribute7
578 ,p_vre_attribute8 => p_rec.vre_attribute8
579 ,p_vre_attribute9 => p_rec.vre_attribute9
580 ,p_vre_attribute10 => p_rec.vre_attribute10
581 ,p_vre_attribute11 => p_rec.vre_attribute11
582 ,p_vre_attribute12 => p_rec.vre_attribute12
583 ,p_vre_attribute13 => p_rec.vre_attribute13
584 ,p_vre_attribute14 => p_rec.vre_attribute14
585 ,p_vre_attribute15 => p_rec.vre_attribute15
586 ,p_vre_attribute16 => p_rec.vre_attribute16
587 ,p_vre_attribute17 => p_rec.vre_attribute17
588 ,p_vre_attribute18 => p_rec.vre_attribute18
589 ,p_vre_attribute19 => p_rec.vre_attribute19
590 ,p_vre_attribute20 => p_rec.vre_attribute20
591 ,p_vre_information_category => p_rec.vre_information_category
592 ,p_vre_information1 => p_rec.vre_information1
593 ,p_vre_information2 => p_rec.vre_information2
594 ,p_vre_information3 => p_rec.vre_information3
595 ,p_vre_information4 => p_rec.vre_information4
596 ,p_vre_information5 => p_rec.vre_information5
597 ,p_vre_information6 => p_rec.vre_information6
598 ,p_vre_information7 => p_rec.vre_information7
599 ,p_vre_information8 => p_rec.vre_information8
600 ,p_vre_information9 => p_rec.vre_information9
601 ,p_vre_information10 => p_rec.vre_information10
602 ,p_vre_information11 => p_rec.vre_information11
603 ,p_vre_information12 => p_rec.vre_information12
604 ,p_vre_information13 => p_rec.vre_information13
605 ,p_vre_information14 => p_rec.vre_information14
606 ,p_vre_information15 => p_rec.vre_information15
607 ,p_vre_information16 => p_rec.vre_information16
608 ,p_vre_information17 => p_rec.vre_information17
609 ,p_vre_information18 => p_rec.vre_information18
610 ,p_vre_information19 => p_rec.vre_information19
611 ,p_vre_information20 => p_rec.vre_information20
612 );
613 --
614 EXCEPTION
615 --
616 WHEN hr_api.cannot_find_prog_unit THEN
617 --
618 hr_api.cannot_find_prog_unit_error
619 (p_module_name => 'PQP_VEHICLE_REPOSITORY_F'
620 ,p_hook_type => 'AI');
621 --
622 END;
623 --
624 hr_utility.set_location(' Leaving:'||l_proc, 10);
625 END post_insert;
626 --
627 -- ----------------------------------------------------------------------------
628 -- |-------------------------------< ins_lck >--------------------------------|
629 -- ----------------------------------------------------------------------------
630 -- {Start Of Comments}
631 --
632 -- Description:
633 -- The ins_lck process has one main function to perform. When inserting
634 -- a datetracked row, we must validate the DT mode.
635 --
636 -- Prerequisites:
637 -- This procedure can only be called for the datetrack mode of INSERT.
638 --
639 -- In Parameters:
640 --
641 -- Post Success:
642 -- On successful completion of the ins_lck process the parental
643 -- datetracked rows will be locked providing the p_enforce_foreign_locking
644 -- argument value is TRUE.
645 -- If the p_enforce_foreign_locking argument value is FALSE then the
649 -- The Lck process can fail for:
646 -- parential rows are not locked.
647 --
648 -- Post Failure:
650 -- 1) When attempting to lock the row the row could already be locked by
651 -- another user. This will raise the HR_Api.Object_Locked exception.
652 -- 2) When attempting to the lock the parent which doesn't exist.
653 -- For the entity to be locked the parent must exist!
654 --
655 -- Developer Implementation Notes:
656 -- None.
657 --
658 -- Access Status:
659 -- Internal Row Handler Use Only.
660 --
661 -- {End Of Comments}
662 -- ----------------------------------------------------------------------------
663 PROCEDURE ins_lck
664 (p_effective_date IN DATE
665 ,p_datetrack_mode IN VARCHAR2
666 ,p_rec IN pqp_vre_shd.g_rec_type
667 ,p_validation_start_date OUT NOCOPY DATE
668 ,p_validation_end_date OUT NOCOPY DATE
669 ) IS
670 --
671 l_proc VARCHAR2(72) := g_package||'ins_lck';
672 l_validation_start_date DATE;
673 l_validation_end_date DATE;
674 --
675 Begin
676 hr_utility.set_location('Entering:'||l_proc, 5);
677 --
678 -- Validate the datetrack mode mode getting the validation start
679 -- and end dates for the specified datetrack operation.
680 --
681 dt_api.validate_dt_mode
682 (p_effective_date => p_effective_date
683 ,p_datetrack_mode => p_datetrack_mode
684 ,p_base_table_name => 'pqp_vehicle_repository_f'
685 ,p_base_key_column => 'vehicle_repository_id'
686 ,p_base_key_value => p_rec.vehicle_repository_id
687 ,p_enforce_foreign_locking => true
688 ,p_validation_start_date => l_validation_start_date
689 ,p_validation_end_date => l_validation_end_date
690 );
691 --
692 -- Set the validation start and end date OUT arguments
693 --
694 p_validation_start_date := l_validation_start_date;
695 p_validation_end_date := l_validation_end_date;
696 --
697 hr_utility.set_location(' Leaving:'||l_proc, 10);
698 --
699 END ins_lck;
700 --
701 -- ----------------------------------------------------------------------------
702 -- |---------------------------------< ins >----------------------------------|
703 -- ----------------------------------------------------------------------------
704 PROCEDURE ins
705 (p_effective_date IN DATE
706 ,p_rec IN OUT NOCOPY pqp_vre_shd.g_rec_type
707 ) IS
708 --
709 l_proc varchar2(72) := g_package||'ins';
710 l_datetrack_mode varchar2(30) := hr_api.g_insert;
711 l_validation_start_date date;
712 l_validation_end_date date;
713 --
714 BEGIN
715 hr_utility.set_location('Entering:'||l_proc, 5);
716 --
717 -- Call the lock operation
718 --
719 pqp_vre_ins.ins_lck
720 (p_effective_date => p_effective_date
721 ,p_datetrack_mode => l_datetrack_mode
722 ,p_rec => p_rec
723 ,p_validation_start_date => l_validation_start_date
724 ,p_validation_end_date => l_validation_end_date
725 );
726 --
727 -- Call the supporting insert validate operations
728 --
729 pqp_vre_bus.insert_validate
730 (p_rec => p_rec
731 ,p_effective_date => p_effective_date
732 ,p_datetrack_mode => l_datetrack_mode
733 ,p_validation_start_date => l_validation_start_date
734 ,p_validation_end_date => l_validation_end_date
735 );
736 --
737 -- Call to raise any errors on multi-message list
738 hr_multi_message.end_validation_set;
739 --
740 -- Call the supporting pre-insert operation
741 --
742 pqp_vre_ins.pre_insert
743 (p_rec => p_rec
744 ,p_effective_date => p_effective_date
745 ,p_datetrack_mode => l_datetrack_mode
746 ,p_validation_start_date => l_validation_start_date
747 ,p_validation_end_date => l_validation_end_date
748 );
749 --
750 -- Insert the row
751 --
752 pqp_vre_ins.insert_dml
753 (p_rec => p_rec
754 ,p_effective_date => p_effective_date
755 ,p_datetrack_mode => l_datetrack_mode
756 ,p_validation_start_date => l_validation_start_date
757 ,p_validation_end_date => l_validation_end_date
758 );
759 --
760 -- Call the supporting post-insert operation
761 --
762 pqp_vre_ins.post_insert
763 (p_rec => p_rec
764 ,p_effective_date => p_effective_date
765 ,p_datetrack_mode => l_datetrack_mode
766 ,p_validation_start_date => l_validation_start_date
767 ,p_validation_end_date => l_validation_end_date
768 );
769 --
770 -- Call to raise any errors on multi-message list
771 hr_multi_message.end_validation_set;
772 --
773 hr_utility.set_location('Leaving:'||l_proc,10);
774 END ins;
775 --
776 -- ----------------------------------------------------------------------------
777 -- |---------------------------------< ins >----------------------------------|
778 -- ----------------------------------------------------------------------------
779 PROCEDURE ins
780 (p_effective_date IN DATE
781 ,p_registration_number IN VARCHAR2
782 ,p_vehicle_type IN VARCHAR2
783 ,p_vehicle_id_number IN VARCHAR2
784 ,p_business_group_id IN NUMBER
785 ,p_make IN VARCHAR2
786 ,p_engine_capacity_in_cc IN NUMBER
790 ,p_vehicle_inactivity_reason IN VARCHAR2
787 ,p_fuel_type IN VARCHAR2
788 ,p_currency_code IN VARCHAR2
789 ,p_vehicle_status IN VARCHAR2
791 ,p_model IN VARCHAR2
792 ,p_initial_registration IN DATE
793 ,p_last_registration_renew_date IN DATE
794 ,p_list_price IN NUMBER
795 ,p_accessory_value_at_startdate IN NUMBER
796 ,p_accessory_value_added_later IN NUMBER
797 ,p_market_value_classic_car IN NUMBER
798 ,p_fiscal_ratings IN NUMBER
799 ,p_fiscal_ratings_uom IN VARCHAR2
800 ,p_vehicle_provider IN VARCHAR2
801 ,p_vehicle_ownership IN VARCHAR2
802 ,p_shared_vehicle IN VARCHAR2
803 ,p_asset_number IN VARCHAR2
804 ,p_lease_contract_number IN VARCHAR2
805 ,p_lease_contract_expiry_date IN DATE
806 ,p_taxation_method IN VARCHAR2
807 ,p_fleet_info IN VARCHAR2
808 ,p_fleet_transfer_date IN DATE
809 ,p_color IN VARCHAR2
810 ,p_seating_capacity IN NUMBER
811 ,p_weight IN NUMBER
812 ,p_weight_uom IN VARCHAR2
813 ,p_model_year IN NUMBER
814 ,p_insurance_number IN VARCHAR2
815 ,p_insurance_expiry_date IN DATE
816 ,p_comments IN VARCHAR2
817 ,p_vre_attribute_category IN VARCHAR2
818 ,p_vre_attribute1 IN VARCHAR2
819 ,p_vre_attribute2 IN VARCHAR2
820 ,p_vre_attribute3 IN VARCHAR2
821 ,p_vre_attribute4 IN VARCHAR2
822 ,p_vre_attribute5 IN VARCHAR2
823 ,p_vre_attribute6 IN VARCHAR2
824 ,p_vre_attribute7 IN VARCHAR2
825 ,p_vre_attribute8 IN VARCHAR2
826 ,p_vre_attribute9 IN VARCHAR2
827 ,p_vre_attribute10 IN VARCHAR2
828 ,p_vre_attribute11 IN VARCHAR2
829 ,p_vre_attribute12 IN VARCHAR2
830 ,p_vre_attribute13 IN VARCHAR2
831 ,p_vre_attribute14 IN VARCHAR2
832 ,p_vre_attribute15 IN VARCHAR2
833 ,p_vre_attribute16 IN VARCHAR2
834 ,p_vre_attribute17 IN VARCHAR2
835 ,p_vre_attribute18 IN VARCHAR2
836 ,p_vre_attribute19 IN VARCHAR2
837 ,p_vre_attribute20 IN VARCHAR2
838 ,p_vre_information_category IN VARCHAR2
839 ,p_vre_information1 IN VARCHAR2
840 ,p_vre_information2 IN VARCHAR2
841 ,p_vre_information3 IN VARCHAR2
842 ,p_vre_information4 IN VARCHAR2
843 ,p_vre_information5 IN VARCHAR2
844 ,p_vre_information6 IN VARCHAR2
845 ,p_vre_information7 IN VARCHAR2
846 ,p_vre_information8 IN VARCHAR2
847 ,p_vre_information9 IN VARCHAR2
848 ,p_vre_information10 IN VARCHAR2
849 ,p_vre_information11 IN VARCHAR2
850 ,p_vre_information12 IN VARCHAR2
851 ,p_vre_information13 IN VARCHAR2
852 ,p_vre_information14 IN VARCHAR2
853 ,p_vre_information15 IN VARCHAR2
854 ,p_vre_information16 IN VARCHAR2
855 ,p_vre_information17 IN VARCHAR2
856 ,p_vre_information18 IN VARCHAR2
857 ,p_vre_information19 IN VARCHAR2
858 ,p_vre_information20 IN VARCHAR2
859 ,p_vehicle_repository_id OUT NOCOPY NUMBER
860 ,p_object_version_number OUT NOCOPY NUMBER
861 ,p_effective_start_date OUT NOCOPY DATE
862 ,p_effective_end_date OUT NOCOPY DATE
863 ) IS
864 --
865 l_rec pqp_vre_shd.g_rec_type;
866 l_proc VARCHAR2(72) := g_package||'ins';
867 --
868 BEGIN
869 hr_utility.set_location('Entering:'||l_proc, 5);
870 --
871 -- Call conversion function to turn arguments into the
872 -- p_rec structure.
873 --
874 l_rec :=
875 pqp_vre_shd.convert_args
876 (null
877 ,null
878 ,null
879 ,p_registration_number
880 ,p_vehicle_type
881 ,p_vehicle_id_number
882 ,p_business_group_id
883 ,p_make
884 ,p_model
885 ,p_initial_registration
886 ,p_last_registration_renew_date
887 ,p_engine_capacity_in_cc
888 ,p_fuel_type
889 ,p_currency_code
890 ,p_list_price
891 ,p_accessory_value_at_startdate
892 ,p_accessory_value_added_later
893 ,p_market_value_classic_car
894 ,p_fiscal_ratings
895 ,p_fiscal_ratings_uom
896 ,p_vehicle_provider
897 ,p_vehicle_ownership
898 ,p_shared_vehicle
899 ,p_vehicle_status
900 ,p_vehicle_inactivity_reason
901 ,p_asset_number
902 ,p_lease_contract_number
903 ,p_lease_contract_expiry_date
904 ,p_taxation_method
905 ,p_fleet_info
906 ,p_fleet_transfer_date
907 ,null
908 ,p_color
909 ,p_seating_capacity
910 ,p_weight
911 ,p_weight_uom
912 ,p_model_year
913 ,p_insurance_number
914 ,p_insurance_expiry_date
915 ,p_comments
916 ,p_vre_attribute_category
917 ,p_vre_attribute1
921 ,p_vre_attribute5
918 ,p_vre_attribute2
919 ,p_vre_attribute3
920 ,p_vre_attribute4
922 ,p_vre_attribute6
923 ,p_vre_attribute7
924 ,p_vre_attribute8
925 ,p_vre_attribute9
926 ,p_vre_attribute10
927 ,p_vre_attribute11
928 ,p_vre_attribute12
929 ,p_vre_attribute13
930 ,p_vre_attribute14
931 ,p_vre_attribute15
932 ,p_vre_attribute16
933 ,p_vre_attribute17
934 ,p_vre_attribute18
935 ,p_vre_attribute19
936 ,p_vre_attribute20
937 ,p_vre_information_category
938 ,p_vre_information1
939 ,p_vre_information2
940 ,p_vre_information3
941 ,p_vre_information4
942 ,p_vre_information5
943 ,p_vre_information6
944 ,p_vre_information7
945 ,p_vre_information8
946 ,p_vre_information9
947 ,p_vre_information10
948 ,p_vre_information11
949 ,p_vre_information12
950 ,p_vre_information13
951 ,p_vre_information14
952 ,p_vre_information15
953 ,p_vre_information16
954 ,p_vre_information17
955 ,p_vre_information18
956 ,p_vre_information19
957 ,p_vre_information20
958 );
959 --
960 -- Having converted the arguments into the pqp_vre_rec
961 -- plsql record structure we call the corresponding record
962 -- business process.
963 --
964 pqp_vre_ins.ins
965 (p_effective_date
966 ,l_rec
967 );
968 --
969 -- Set the OUT arguments.
970 --
971 p_vehicle_repository_id := l_rec.vehicle_repository_id;
972 p_effective_start_date := l_rec.effective_start_date;
973 p_effective_end_date := l_rec.effective_end_date;
974 p_object_version_number := l_rec.object_version_number;
975 --
976 --
977 hr_utility.set_location(' Leaving:'||l_proc, 10);
978 END ins;
979 --
980 END pqp_vre_ins;